Facebook like Notifications System

I am developing a fac开发者_高级运维e book like notification module in php/mysql where in the user is notified with the latest activities among his friends (Added Photo/Added Video/Commented etc....). What is the best approach to design this module in terms of database. Does it need to have a seperate table for each activity(say photos). Or a single table is enough.

And, what is the best logic. One option is whenever a user performs an action, that activity can be inserted into table with all the friend ids, i.e. if he has 50 friends, 50 records. In this way no php script is needed, just scan the table when user login. Second option is whenever a user login, scan his friends, store in an array & scan the notification table for every friend whether the friend has done any activities.

Thanks in advance, Hari

From a normalization point of view I'd say have a base table and implement an IS-A relationship between your tables (so that would be "separate tables"). From a pragmatic and performance-oriented point of view I'd say you better use one table for all of them and have a column like type or something similar.

Your first logic example doesn't make sense. What if the guy logs in, gets the 50 records and promptly adds another friend? No information for that guy... Thus, I'd go with your second suggestion.

Index all activities with an ID. Then have a record column in the user table with all IDs that are 'Viewed'. Check each time the user logs in, which IDs of activities (cross checked by friends' only activities also) aren't already in the 'Viewed' column and display them to the user.