Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Looking for a good database structure to achieve Facebook/SO like notifications

I want to be able to have notifications on my site, similar to the way SO does it. I have looked for a good table structure to do this, but I cant seem to figure it out.

I was thinking something like this.

Notifications

id, notification_type_id, user_id, type_id

Notification Types

id, notification_text

Where the notification type would relate to either a new post, a new comment, or whatever features I add later down the line... User Id would relate to whoever the notification is for. Type_id and notification type would go hand in hand, so if the notification_type was a new comment, the type_id would be the comment_id to go to.

This seems good to me, but i want to be able to notify ALL users when something changes.. like on facebook when you comment on something, you get a notification that someone else has also commented on the same thing after you.

I cant seem to figure this out... Help wanted

Thanks

EDIT: The way I thought about it was, the notification_type_id would map to the notification_type table, which would hold the text for each notification ("You have a new comment", "blah blah blah has also commented on blah blah blah", etc.), type_id would map to the primary_id of whatever the comment is about. for example, if the notification is saying you have a new comment on your post, then the type_id would be the primary id of the post for easy linking.. IDK, it was just a thought.

like image 819
BDuelz Avatar asked Mar 28 '10 23:03

BDuelz


1 Answers

Look at these stackoverflow questions:
Facebook like notifications tracking (DB Design)
Database design to store notifications to users

Pay particular attention to this answer that has a clean solution of your request.

like image 146
systempuntoout Avatar answered Sep 28 '22 12:09

systempuntoout