Database design - Friend activities

Question

Hi, Currently I am designing a small twitter/facebook kind of system, where in a user should be able to see his friends latest activities.

I am using ASP.NET with MySQL database.

My Friendships table is as follows:

|Friendshipid|friend1|Friend2|confirmed|

Friend1 and Friend2 in the above table are userids.

User activities table design following:

|activityId|userid|activity|Dated|

Now, I am looking for best way to query the latest 50 friend activities for a user.

For example, let's say if Tom logs into the system, he should be able to see latest 50 activities among all his friends.

Any pointers on the best practices, a query or any information is appreciated.

Answer 1

It largely depends on what data is stored in the Friendships table. For example, what order are the Friend1 and Friend2 fields stored in? If, for the fields (friend1, friend2) the tuple (1, 2) exists, will (2, 1) exist also?

If this is not the case, then this should work:

SELECT activities.* 
FROM Activities 
    INNER JOIN Friendships ON userid = friend1 OR userid = friend2 
WHERE activity.userid != [my own id]
    AND confirmed = TRUE
LIMIT 50;