comments on blog posts over time

My goal is to keep track of the popular posts on different blog sites based on social network activity at any given time. The goal is not to simply get the most popular now, but instead find posts that are popular compared to other posts on the same blog.

For example, I follow a tech blog, a sports blog, and a gossip blog. The tech blog gets waaay more readership than the other two blogs, so in raw numbers every post on the tech blog will always out number views on the other two. So let’s say the average tech blog post gets 500 fb likes and the other two get an average of 50 likes per post. Then when there is a sports blog post that has 200 Facebook likes and a gossip blog post with 300 while the tech blog posts today have 500 likes I want to highlight the sports and gossip blog posts (more likes than average vs tech blog with more # of likes but just average for the blog)

The approach I am thinking of taking is to make an entry in a database for each blog post. Every x minutes (say every 15 minutes) I will check how many likes/shares/comments an entry has received on all the social networks (Facebook, Twitter, Google+, LinkedIn). So over time there will be a history of likes for each blog post, i.e

   post 1234 

        after 15 min: 10 fb likes, 4 tweets, 6 g+
        after 30 min: 15 fb likes, 15 tweets, 10 g+
        ...
        ...
        after 48 hours: 200 fb likes, 25 tweets, 15 g+

By keeping a history like this for each blog post I can know the average number of likes/shares/tweets at any give time interval. So for example the average number of fb likes for all blog posts 48hrs after posting is 50, and a particular post has 200 I can mark that as a popular post and feature/highlight it. A consideration in the design is to be able to easily query the values (likes/shares) for a specific time-frame, i.e. fb likes after 30min or tweets after 24 hrs in-order to compute averages with which to compare against (or should averages be stored in it’s own table?)

If this approach is flawed or could use improvement please let me know, but it is not my main question. My main question is what should a database scheme for storing this info look like?

Assuming the approach above is taken, I am trying to figure out what a database schema for storing the likes over time would look like. I am brand new to databases, in doing some basic reading I see that it is advisable to make a 3NF database. I have come up with the following possible schema.

Schema 1

DB Popular Posts

  Table: Post
    post_id ( primary key(pk) )
    url
    title 

  Table: Social Activity
    activity_id (pk)
    url (fk)
    type (i.e. facebook,twitter,g+)
    value
    timestamp

This was my initial instinct (base on my very limited db knowledge). As far as I under stand this schema would be 3NF? I searched for designs of similar database model, and found this question on weight/height storage of multiple users. The scenario in that question is similar (recording weight/height of users overtime). Taking the accepted answer for that question and applying it to my model results in something like:

Schema 2 (same as above, but break down the social activity into 2 tables)

DB Popular Posts

  Table: Post
    post_id (pk)
    url
    title 

  Table: Social Measurement
    measurement_id (pk)
    post_id (fk)
    timestamp

  Table: Social stat
    stat_id (pk)
    measurement_id (fk)
    type (i.e. facebook,twitter,g+)
    value

The advantage I see in schema 2 is that I will likely want to access all the values for a given time, i.e. when making a measurement at 30min after a post is published I will simultaneous check the number of fb likes, fb shares, fb comments, tweets, G+, LinkedIn. So with this schema it may be easier get get all stats for a measurement_id corresponding to a certain time, i.e. all social stats for post 1234 at time x.

Another thought I had is since it doesn’t make sense to compare number of fb likes with number of tweets or g+ shares, maybe it makes sense to separate each social measurement into its own table?

Schema 3

DB Popular Posts

  Table: Post
    post_id (pk)
    url
    title 

  Table: fb_likes
    fb_like_id (pk)
    post_id (fk)
    timestamp
    value

  Table: fb_shares
    fb_shares_id (pk)
    post_id (fk)
    timestamp
    value

  Table: tweets
    tweets__id (pk)
    post_id (fk)
    timestamp
    value

  Table: google_plus
    google_plus_id (pk)
    post_id (fk)
    timestamp
    value

As you can see I am generally lost/unsure of what approach to take.

I’m sure this typical type of database problem (storing measurements overtime, i.e temperature statistic) that must have a common solution. Is there a design pattern/model for this, does it have a name? I tried searching for “database periodic data collection” or “database measurements over time” but didn’t find anything specific.

What would be an appropriate model to solve the needs of this problem?

So, reading this, I see the following specs:

I want to track popularity of blogs. This is accomplished by comparing their aggregate “likes” or whatever (retweets, etc) over a 48 hour period to their “normal” level.
I want to update my current count of likes, retweets, on a configurable periodic interval.
I need to be able to compute the effect of likes, retweets, etc independent of each other.

Seems the simplest way would be to use your third schema. It still allows you to collect all stats simultaneously or independently. The only effect would be if independent, there will always be some window of time where your current rankings doesn’t reflect the true ranking while if simultaneous, your rankings just lag the “truth” by at most the update rate.

Anyway, then, you can periodically run a query for each post_id, compute the fb likes metric over the previous 48 hours + tweets metric over the previous 48 hours, etc, and use that to update your ranking.

To answer the questions you want to ask your application you need to store information about three things: blogs, posts and activities.

Blogs are simply containers for the posts, because you said you want to rank/highlight posts within each of the blogs, not across the blogs, so you need to know which posts belong to which blogs. Posts are fairly static, but are independent of their respective blogs and their social activity. Social activity is highly dynamic (probably looks like a bell curve over time) and there may or may not be a cut-off for the social activity discovery over time.

Now, this leaves you with three core entities: blog, post and activity. The schema could look something like this:

blog          post          activity
----------    -----------   --------
blog_id (pk)  post_id (pk)  activity_id (pk)
url           blog_id (fk)  post_id (fk)           
title         url           facebook_likes
              title         twitter_tweets
                            google_shares

This assumes that you’re not interested in storing the actual social media activity itself, i.e. storing the URL of the tweet, etc, and just storing the results of the social activity discovery for each post. If you run this for a new post today, you would insert the results into the activity table. If you run the discovery again tomorrow, a row in the activity table would already exist and you’d update it with the results at that time.

(Feature creep alert: if you store new rows for each discovery you could gain some valuable insight into how the social media activity develops over time. For instance, you could see which medium is quick to pick up the post and which lag behind. And you could create some useful graphs that would spice up the presentation. In order to do this you’d need to store exactly the same things, but also add a date/timestamp for when the discovery was done.)

A foreign key connects the row to a row in another table. So for instance, a blog has multiple posts, and a post belongs to a single blog. This is a one to many relation — one blog has many posts, a post belongs to one and only one blog. A blog could have the blog_id 1. All posts that belong to that blog would have their blog_id set to 1.

Technically speaking you could eliminate the activity table and move the columns into the post table if you wanted to. The reason I’m keeping them separate is that they’re distinct entities and it leaves the door open for future changes. For instance you could easily add a timestamp and store the activity as something that varies over time. Furthermore, you could break it down even more and add another table (e.g. action) that stores the actual individual social media actions (the tweets, the likes, etc).

As an optimization you can compute and store the metrics on the respective entities (i.e. table and post) if need be. This is primarily a concern when it comes to reading the data after you’ve done the discovery. Remember you’ll be computing and updating the database very little compared to how many times your users will be reading the information — in other words, denormalizing and aggregating will reduce the number of queries needed to produce the data you want to present to your users.

Filed under: softwareengineering - @ 03:45

Thẻ: database, database-design, relational-database, schema, sql

Thiết kế website giá rẻ

Danh mục

Database model for keeping track of likes/shares/comments on blog posts over time