Storing "likes" of an item in a database?

I'm running a Rails app on Postgres through Heroku.开发者_高级运维

I'd like to implement similar to Facebook "likes" on my site for various items, such as user comments. What's the smartest way to store these in my database that will be efficient and fast?

The obvious one is just to have a like join table between users and items, something like this:

user_id int
item_id int
item_type string
created_at datettime

However, when being displayed, this would mean every time I pull an item, I would have to pull a join across the entire like table, which could get very big.

The obvious response for this would be to store a counter in the items, for their ongoing like count. However, this won't work because who liked an item matters, both for display next to the item, and also to hide the like button for things a user has already liked.

My plan is to add to all likable items a text field in which I would store a serialized array. That way, every pull of an item would come with the complete list of who liked it. Is there a better way to do this, or is this the recommended approach?

Do you have reason to believe that your dataset is going to be so large that the join is going to be too expensive? Postgres, while not as fast as the fastest RDBMSs out there, is pretty fast these days. I used to run a website that got millions of pageviews a day, and required some pretty complicated queries to generate each page. By doing a bit of simple caching we were able to run it on very modest hardware.

You give up a lot of the benefits of using an RDBMS when you denormalize. I would only do so if I knew I had to. And if that were the case I would consider using something else, like a simple key/value database, for that data. But I think that that's only likely to be the case for you if you have an awful lot of data.