How to make a Facebook like Notifications Table Structure?

Question

I am making a social app like Facebook using Yii Framework and MySQL. It has a notifications button at the top similar to Facebook. When someone posts in a group or someone from your friend list is going to any event or if someone sends you an add request then how should I manage these notifications in database?

Like : I joined a group "A". Someone from "A" group will post in the group then a notification should be sent to each user of the group. How could I manage notification table in this case?

I am planning to have a table structure as given below:

user_id | Type_of_notification | notication_text | sender_id | Created_time |

Is my approach right?

Answer 1

You are effectively building an activity stream in MySQL. There is an excellent answer at How to implement the activity stream in a social network. I've used it myself in a smaller app, but the comments in there show that it easily scales up to quite a few million rows too.

Answer 2

You can see facebook's 'notification' table structure for fql which can give you better idea about your table.

These can be some of your columns:

id, sender_id,type_of_notification, title_html, body_html, href ,
recipient_id, is_unread, is_hidden, created_time

See notification table in fql technical-guides