I’m working on a side project that lets people share embeddable videos with a follower/following system a la Twitter. You can see a rough example of the setup I’m using in this old demo app. The basic idea is that each user has a feed of posts created by other users that they follow.
So far, I’ve found that having a feed_items table that stores posts on a per-user basis makes the most sense. If you drop an index in there, it seems speedy enough for my needs. Here’s a simplified example of the relevant portion of the schema:
Now, if you’re going to show a user their “feed” you might do something like this:
The only problem is that, even with those fancy includes, you’re still querying 3 different tables:
So, I started experimenting with storing attributes of the post and associated user information directly in the feed_items table. I played around with Active Record’s serialize method (link), but it didn’t seem like the right fit.
Here’s what I came up with yesterday. I think it’s a pretty good start:
Essentially, I’m just pushing a cached copy of the post (including the associated user) into the feed_items table, using JSON. This way, we can get away with a single query for the user’s feed_items:
I’m curious to hear what other people think about this, and if there are smarter ways to accomplish the same goal.