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?

Solution 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.

Solution 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

Comments

[Amit Chaurasia]

developers.facebook.com/docs/technical-guides/fql. As of August 8 2016, FQL will no longer be available and cannot be queried. To migrate your app, use the API Upgrade Tool to see the Graph API calls you can make instead. Learn more about the API v2.1 migration.

[Shantaram Tupe]

link is broken now,