I've been trying to figure out a good way to handle a ranking system of this sort. As a rough example, I would like to query a facebook page and grab the likes and comments of each post. Then, there would be three rankings based on a time interval. To give a simplified example:
Hourly
- I pull all the posts updated within the last hour, and compare the # of likes/comments compared to my previous entry (the last pull being an hour prior).
Daily
- I pull down all posts within a 24 hours date range. I compare the # of likes/comments compared to the previous entry. "Post X had 12 more likes and 40 more comments today compared to yesterday"
Weekly
- I pull down all posts within a week's range and do the same as above. "Post X had no new likes, but 10 more comments added this week compared to last week"
In terms of the DB tables, what would be a good way to handle this? Would it make sense to have one giant table with the posts (title, comments_previous, comments_current, likes_previous, likes_current, etc)?
Thank you!
Columns: (PK)timestamp, (index)pageid, count. Set a new timestamp every hour on the hour for pages that are liked. Timestamp is the PK so that you don't get horrible fragmentation from your clustered index / page layout in the database.
If you feel for performance reasons that you need to de-normalize, you can make additional daily and monthly tables that are rolled-up summations. Likely, you'll be able to efficiently generate what you need without the rollup tables by using where clauses on the time / pageid combination, thereby giving you what you need with just one table.
Purge old data as you see fit, or keep it.
When a comment receives a like, do the following:
insert into likeRanking (concat(select left(now(),13), '00:00'), commentid, 1)
on duplicate key update score = score + 1;