User Activity (ala Facebook) Db Approach. Big Join Or Multiple Simple Queries?

I need to display the users last activity in the website. Reviews posted, images/video uploaded, voted articles and so on... I have two tables: 'user_activity' table with 'userID',

Solution 1:

None.

The solution for high traffic sites is denormalization which means that you'll have some redundant data but your user activity query will be a very simply one, fetching all required data from a single table.

Solution 2:

What you have setup is called a polymorphic association. This is a problem that has been solved over and over again in different ways. You can find a lot of information out on the interwebz about this now that you know how to find it.

To answer your question more specifically: it depends. JOINs can be easier on the database if you setup the tables to use InnoDB and you only join on foreign keys. Unfortunately, InnoDB doesn't support polymorphic foreign keys.

Generally speaking, it's harder to scale the database than to scale your application, so anything you can do to move work from the DB layer into the application layer is a good thing. However, that's really only true of you're worried about scaling (and remember, premature optimization can be a death-blow to a startup).

Check out the top answers to this question on Quora for some more detailed information: What are the scaling issues to keep in mind while developing a social network feed?