Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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 autoincrement, -- 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.