如何实现像Facebook这样的通知系统？

Question

I wanted to do a Facebook-similiar notifiaction system, but I just can't think of the way, how to realize it.

I read here about various opinions like having one table for it, others says to have two.

However I'm trying the most effecient way with only neccesarry data entries. This is my notfication table so far: id, uid, type, read (boolean), date.

So I thought of one example:

Insert a comment, call notification function that inserts my uid (user_id), type (comment, like, etc), read='1', NOW().

I set read to 1, because I don't want to get notified when I'm posting something. Then I thought I'll update notification by setting read to 0 by all other users, because they haven't read this new post yet.

But now I'm thinking of another post that will be made 5sec later or something. Then all other read entries are set to 0 again, which is right, but in this scenario I can't think of displaying (SELECT) the right results for each single user. Because it isn't specified which user missed which new notification(s).

I hope you could follow me and have any suggestions. If I'm doing it the wrong way in general I'd also appreciate advices.

Answer 1

First, you have to know if your notifications are broad casted to all your uses or can be user specific. In case of Facebook, I would say it's the second option.

My first recommendation, is to find an open-source project (or may be even 2) that implement this feature and look at their code.

My second recommendation, is to write down all the requirements for this feature, as often, a small restriction can induce a modification on the table structure or even an addition of a new table (For example, can a notification be sent to multiple users at once? can a user send a notification to another user?...).

Here's the way I would go, using 2 tables, one for the message and one for the NtoN relation with users: Table Notification

ID // auto increment ID
sender_id // Can be a subsystem or another user. To be defined / optional. Does not have to be a foreign key.
title // Title of the notification
body // The text message
type // warning, message, error or any notification class you can think of
... // you can add other options, like priority, attachment...

Table Notification_User

notification_id // Foreign Key for the notification
receiver_id // Foreign Key for the user
is_read // is the notification read
is_deleted // is the notification deleted, this implements a Trash bin like feature
created_at // time at which the notification was sent
read_at // time at which the notification was read
deleted_at // Time at which the notification was deleted