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

Question

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.

Answer 1

I know the question is about database structure for the notification systems, but I don't think any of the systems you mentioned doesn't have notification tables similar to the ones you mentioned. Usually, to do such notifications, you are using triggers and a queue system to process them and send them to the correct people and channels.

So how I would design it is having tables in the SQL: Event_type, message text (with any substitution template variables), channel (SMS/Email/Push), parameters

Then when you have commentPost() function, you run the trigger "commentPost", and in parameters, you can already send user_ids that commented before or user_ids that are watching that issue - but that can be checked by some function later too.

So then trigger is sent to some type of queue (SQS/RabbitMQ etc) and there is the processor that is reading that queue, checking event Type, and accordingly can send notifications based on events, but can also retrieve data from post if needed (like all users that commented post). Then if you want to log it, you just send a message to some other queue that will write logs to the database.

sending notifications to user X by a push message should be just another queue that the browser is constantly reading for notifications.