Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. 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 look in the logs for all rows where C3 == UNFRIENDED user id and delete them.

Any opinions? Other suggestions?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

Your Problem is Data is not normalized, That is what writing this query a Pain.
It can still be done though, (I would give you the SQL if I fully understood your data Model).

TO properly normalize your data, You need the following tables:

Users
UserFriends - M:M With Users (Needs a timestamp for the action)
Posts - 1:M With Users (Needs a timestamp for the action)
PostsLikes - 1:M With Posts (Needs a timestamp for the action)

Now I know what you are thinking I created the LOG table to Speed this up, to not have so many joins! TO avoid this exactly this! Well you basically shot yourself in the foot. Because writing efficient complex queries against your data is basically an impossibility.

That that your new data model is Done, If you need better performance Denormalize the entire Data model into one Giant table called TimeLine, that is used to display the timeline. This Table Does not replace your data model, it Complements it. Any time you insert a record into the any of the other tables insert a record here too logging that action. (Insert to records if the action effects two users). Index this table On Userid & TimeStamp. You should always be able to rebuild this table based off you other data.

If you need a complex query, query your base tables.

share|improve this answer
    
Well.... Actually I have these tables. But I need to show to the user the activities sorted by timestamp. Each of the 3 tables has different data. So HOW do I collect data from the 3 tables and sort the joined data? Maybe I missunderstood some stuff. So in the Timeline I put userId,TS,TableId and do queries on the table according to it? probably use FK to these tables to do it fast? –  Yoav Mar 27 '12 at 14:21
add comment

This is largely within the realm of "things not ideal for rdbms"

Graph databases solve problems like...

  • User A is friends with User B
  • User A likes Product Z and user B also likes Product Z
  • User A is friends with User B who KNOWS User C

http://neo4j.org/

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.