Database design and discussion – Part II

In the previous post database design, we discussed what our database will look like. But that was just half of the database design discussion as we still have to cover the heart and soul of our SocialPie service. In this post, we will cover the other half and that is the APIs we will use from Twitter, Instagram, and Facebook.

Using Instagram APIs

So Instagram which is now part of Facebook offers a marketing API for businesses. You can find more detail on the Instagram API. This API is built on Facebook’s Graph API. An interesting thing to look at this API, we will find what kind of data we are actually looking to get and store in our database.

This API offers something called Insights API, it provides us the data for user metrics for business accounts and stories metrics. Considering Instagram API is linked with Facebook, we will be using the same API for Facebook data.

/media/insights/ –  This API gives us details about engagements, impressions, and reach about stories. A sample response looks like below:

{
  "data": [
    {
      "name": "impressions",
      "period": "lifetime",
      "values": [
        {
          "value": 264
        }
      ],
      "title": "Impressions",
      "description": "Total number of times the media object has been seen",
      "id": "17855590849148465/insights/impressions/lifetime"
    },
    {
      "name": "reach",
      "period": "lifetime",
      "values": [
        {
          "value": 103
        }
      ],
      "title": "Reach",
      "description": "Total number of unique accounts that have seen the media object",
      "id": "17855590849148465/insights/reach/lifetime"
    }
  ]
}

/user/insights/ – This API gives us different metrics data for business accounts. These metrics include impressions, follower counts, website clicks, text message clicks, profile views, online followers. A sample response looks like below:

{
  "data": [
    {
      "name": "impressions",
      "period": "day",
      "values": [
        {
          "value": 4,
          "end_time": "2017-05-04T07:00:00+0000"
        },
        {
          "value": 66,
          "end_time": "2017-05-05T07:00:00+0000"
        }
      ],
      "title": "Impressions",
      "description": "Total number of times this profile has been seen",
      "id": "17841400008460056/insights/impressions/day"
    },
    {
      "name": "reach",
      "period": "day",
      "values": [
        {
          "value": 3,
          "end_time": "2017-05-04T07:00:00+0000"
        },
        {
          "value": 36,
          "end_time": "2017-05-05T07:00:00+0000"
        }
      ],
      "title": "Reach",
      "description": "Total number of unique accounts that have seen this profile",
      "id": "17841400008460056/insights/reach/day"
    },
    {
      "name": "profile_views",
      "period": "day",
      "values": [
        {
          "value": 0,
          "end_time": "2017-05-04T07:00:00+0000"
        },
        {
          "value": 2,
          "end_time": "2017-05-05T07:00:00+0000"
        }
      ],
      "title": "Profile Views",
      "description": "Total number of unique accounts that have viewed this profile within the specified period",
      "id": "17841400008460056/insights/profile_views/day"
    }
  ]
}

What fields we will use and build our database?

So what data from this API we will be using to build our database. We will have a table called InstagramData This table will include the following fields

  • impressions
  • reach
  • profile_views
  • followers
  • audience_gender_age
  • email_contacts
  • video_views

Therefore, we showed how we will be using Facebook and Instagram APIs. In the next post, we will look into Twitter API. Currently, Twitter does offer an enterprise API at a premium price. But if there is no open-source API for developers, we will not be using it in this project.

References

  1. Instagram API documentation – Instagram API

 

500 Miles

This is a non-programming blog post. I just wanted to announce the publication of my first fiction book 500 Miles.

500 Miles

The book contains 14 short stories about characters from train traveling. I wrote more about the book on my other blog 500 Miles at yogsma.

You can buy this book on Amazon India, Flipkart or Pothi.com. The links for the same are as below:

500 Miles at Amazon India

500 Miles at Flipkart

500 Miles at Pothi.com

 

Database design and discussion – Part I

Continuing the series of building a spring-based web application, in this post, we will discuss database design. Based on this database, we will eventually build our REST APIs.

Database Design

We will build database design as we go about discussing the APIs that we will be using from Twitter, Facebook, and Instagram. Since we will have users of a company logging into our application, few basic database tables that we will need

  1. User
  2. Company
  3. Role
  4. UserPassword
  5. Address

Database Model Part 1

An administrator user can add their company and can also add users. An administrator will be allowed to create reports and she can share these reports with other users. These other users will have the role of reporters.

These tables will be the foundation blocks for our application. As referred to user flow, a user with a particular role will log in to the application. He can view/change the social performance data for his company and propose new marketing strategies. Of course, this is not the complete database model for the application. We still have to look into what data we will be fetching from Facebook, Twitter, and Instagram APIs. We will study those APIs in the next post.

Follow the progress of this application here.

Design of REST API for web application

One reason I like to build an application in public is that it keeps me accountable. I can’t run away. If I don’t finish something, it’s ok. At least, I will have something done to show to people. Building in public is not a new idea, a lot of people have used it. In this post, I discuss the design of REST API for Social KPI.

In the previous post here, we discussed the architecture of the application we are building.  This will be an ongoing process as we continue to build our application and evolve.

We will follow the following tips to design REST APIs

  1. We will use Resource to represent object for REST APIs
  2. API endpoint will represent a resource object in the plural. Example – companies, users
  3. We will use HTTP status codes for success or failure of the request
  4. We will use JSON object to represent a response
  5. And we will use versioning to represent a version of APIs

As discussed in the previous post application idea, we will have APIs for companies, users of those companies, customers, clicks, engagements data. While concluding this short post, I want to say that the next post will include database design as well as URL design for REST APIs.

We will be using Spring Boot to build REST API.

In conclusion, I discussed the design of the REST API for the web application Social KPI. If you want to follow the progress, subscribe here.

Architecture of the web application

In my last post design, I discussed the idea that we are going to work on building a web application. I detailed the user flow, but I missed out on some points about security and session management. I will add the details of architecture of social KPI web application.

Name of the application

Before we discuss the application, we still haven’t decided on the name for the application. This web application will indicate the performance of a small business in social media. Basically, this is a free tool for marketing and depending on how small businesses use social media, they will be able to build a campaign for their business. If small businesses are not using social media, they are already at a disadvantage. This is just a pie in the big social world. That brings me to the purpose of the application to provide social key performance indicators (social KPIs) to businesses. So the name of the application will be SocialPie.

Security and Session Management

We will use Spring Boot. We will be using spring security elements to build authentication and authorization aspect of the application. I will definitely include the details of this component when we will start working on building the application. In a previous post spring security, I have discussed how to use spring security for authentication.

For managing a session, we will be using spring provided service based on Redis. We will also be using caching considering we will be connecting to Facebook, Twitter, and Instagram APIs, so we can keep the data in cache for pre-decided time. This will be beneficial from a performance perspective. We will be using Redis caching with our own cache manager to handle caching.

I will try to include all these elements in the architecture diagram that we will be building in this post.

Architecture

Architecture of web application Social KPI

 

Conclusion

In this post, we created an architecture for our web application Social KPI. In the next post, I will detail another user flow with some class diagrams and explain each service in detail. The application will be based on microservice architecture.