SQL DB design to support user feeds (in application like facebook) I have a social network server with a MySql DB. I want to show the users feeds like done in Facebook. Example - UserX now Friend with userY, userX did like on postX etc.

Currently I have table: C1 : UserId C2 : LogType (now friend, did like etc) C3 : ObjectId (Can be userId or postId) - set depending on the LogType.

Currently to get all related logs to show to the user I do the following queries: 1. Get All user Friends userIds 2. Query all rows which C1 is in userIds (I query completed) 3. Scan the DB and see - if LogType equals DidLike, check if post's OwnerId is the userId - if yes add it to logs.

And so on.

Obvious this is not efficient at all.

I am looking for a better way. I thought I had in mind: Create a new table (in addition to the Log table) C1 : UserId C2 : LogId (from Log table) C3 : UserID of the one who did the action When querying logs - look in the table and get related Logs (by LogId) from LogTable.

Updating the table: Whenever user doing action that should be in the log: 1. Add the Log entry to LogTable. 2. Scan the DB and see which users are interested with the Log (Who my friends are, Who is the owner of the post) and add related entries to the new table. (must be done in BG). 3. If user UNFRIEND another user - then loko in the logs for all rows where C3 == UNFRIENDED user id and delete them.

Any opinions? Other suggestions?
Get rid of ambiguous columns like object_id.  Use log_type to determine what you are querying on.  So something like:

CREATE TABLE logs (
    log_id bigint AUTO_INCREMENT, -- not sure if this is the mysql syntax
    user_id int references users(id),
    post_id bigint references posts(id)
);


A second point is I would suggest changing the friend/unfriend to be immediate so it doesn't hit this table.  This should be only for logging actions to posts, not friendships and the like.  If you must log and propagate, create a separate table for this.