broshnat
-
Posts
1 -
Joined
-
Last visited
Never
We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.
Facebook-style news feed
in Application Design
Posted
Hi everyone, we have just launched a new site and I want to incorporate a Facebook-style news feed for users. The site is based around gaming and users can follow games that they are interested in so we have a "follows" table which matches user_id to game_id. There is a third column in this table that has a value between 1 and 10 and represents the user interest level in that game.
We then have an events table which lists a variety of different events relating to the games - when a news story is added, when the game is updated, new screenshots, videos etc. We have a column in this table with a value of 1-10 that we assign a value of newsworthiness to.
So you can probably see where I want to go - for a given user we want to return a list of events (which we can then parse into a feed) - which seems like a simple join but there are a few complexities:
The main issue really is one of speed and efficiency. I know of many solutions we can engineer quickly but they all seem to require huge queries and large sets of data and table joins which I can see getting messy. Also, both tables will have records added constantly which obviously isn't ideal if you are constantly reading as well.
Obviously a feed only needs to be transient - we only need to look at the most recent events - but clearly a user following 1000 games will have many more recent events than a user following 2 games so I need a system that will work for both. I had thought about producing a temp table of recent events, say every 10 minutes and reading from that but we want things to show up instantly ideally, not have to wait 10 mins for the next cron job to run.
So I'm wondering if anyone has experience at anything like this or has any neat ideas on how it could be run efficiently?