I want to create a Facebook like newsfeed for my users that basically does the following
but i am not quite sure how best to implement this in mysql. Like what sort of tables will I need? What is the structure of these tables? What will I store in these tables? How will I keep track of user and friends activities. I want to build an efficient system that will scale up. I prefer working with PHP and Mysql if possible!
I was thinking of having a NEWSFEED table and when a user does something (posts a comment, etc), I add an entry to that table for the user's friends and the user himself that will show up on their newsfeed.
The table structure would be as follows
uid INT
activity ENUM
activity_id INT
So let's say I have a comments table with the following structure:
title VARCHAR(255)
message TEXT
If the user posts to that table and an entry with ID=5 gets created for that user, then the following entry will be added to the NEWSFEED table
uid=3 activity="comment" activity_id=5
that means that to display the newsfeed for each user, I would have to do the following:
SELECT * FROM newsfeed WHERE UID = ....
FOR EVERY ROW:
CHECK activity type (if activity == 'comment')
**QUERY the corresponding table and display the result (so I would query the comments table an display the person's comment)**
I am thinking of deleting entries more than >3 months old to prevent the table from getting too huge. But I don't like that I am doing independent queries to display for each activity type (just as I did above where I queried the COMMENTS table to display the comment whose ID is mentioned in the NEWSFEED table).