Facebook like notifications tracking (DB Design)

Question

I am just trying to figure out how Facebook's database is structured for tracking notifications.

I won't go much into complexity like Facebook is. If we imagine a simple table structure for notificaitons:

notifications (id, userid, update, time);

We can get the notifications of friends using:

SELECT `userid`, `update`, `time`
FROM `notifications`
WHERE `userid` IN 
(... query for getting friends...)

However, what should be the table structure to check out which notifications have been read and which haven't?

Answer 1

I dont know if this is the best way to do this, but since I got no ideas from anyone else, this is what I would be doing. I hope this answer might help others as well.

We have 2 tables

notification
-----------------
id (pk)
userid
notification_type (for complexity like notifications for pictures, videos, apps etc.)
notification
time


notificationsRead
--------------------
id (pk) (i dont think this field is required, anyways)
lasttime_read
userid

The idea is to select notifications from notifications table and join the notificationsRead table and check the last read notification and rows with ID > notificationid. And each time the notifications page is opened update the row from notificationsRead table.

The query for unread notifications I guess would be like this..

SELECT `userid`, `notification`, `time` from `notifications` `notificationsRead`
WHERE 
`notifications`.`userid` IN ( ... query to get a list of friends ...) 
AND 
(`notifications`.`time` > (
    SELECT `notificationsRead`.`lasttime_read` FROM `notificationsRead` 
    WHERE `notificationsRead`.`userid` = ...$userid...
))

The query above is not checked. Thanks to the idea of db design from @espais

Answer 2

You could add another table...

tblUserNotificationStatus
-------------------------
- id (pk)
- notification_id
- user_id
- read_status (boolean)

If you wanted to keep a history, you could keep the X latest notifications and delete the rest that are older than your last notification in the list....

Answer 3

If, when you give notifications, you give all relevant notifications available at that time, you can make this simpler by attaching timestamps to notifiable events, and keeping track of when each user last received notifications. If you are in a multi-server environment, though, you do have to be careful about synchronization. Note that this approach doesn't require true date-time stamps, just something that increases monotonically.

Answer 4

I see no-one here addresses the fact, that notifications are usually re-occurring, aka. notification of an upcoming transaction is always going to be the same, but with a different transaction ID or Date in it. as so: { You have a new upcoming payment: @paymentID, with a due date of @dueDate }. Having texts in a different table can also help with

1. If you want to change the notification text later on
2. Making the app multilingual is easier, because I can just layer the notifications table with a language code and retrieve the appropriate string

Thus I also made a table for those abstract notifications, which are just linked under the the user with a middle table, where one notification type can be sent to one user at multiple times. I also linked the notifications to the user not by a foreign key ID, but I made notification codes for all notifications and full_text indexed the varchar field of those codes, for faster read speeds. Due to the fact that these notifications need to be sent at specific times, it is also easier for the developer to write

NotificationService::sendNew( Notification::NOTE_NEW_PAYMENT, ['paymentId'] => 123, ['dueDate'] => Carbon::now(), 'userIdToSendTo' );

Now since my messages are going to have custom data in them, that is inserted into the string, as you can see from the second argument beforehand, then I will store them in a database blob. as such

$values = base64_encode(serialize($valuesInTextArray));

This is because I want to decouple the notifications from other tables and as such I dont want to crete unnessecary FK relations from and to the notifications table, so that I can for example say notification 234 is attached to transaction 23 and then join and get that transaction ID. Decoupling this takes away the overhead of managing these relations. The downside is, it is nigh impossible to delete notifications, when for example a transaction is deleted, but in my use case I decided, this is not needed anyway.

I will retrieve and fill the texts on the App side as follows. Ps. I am using someones vksprintf function (https://github.com/washingtonpost/datawrapper/blob/master/lib/utils/vksprintf.php), props to him!

$valuesToFillInString = unserialize(base64_decode($notification->values));
vksprintf( $notificationText->text, $valuesToFillInString )

Notice also which fields I index, because I am going to find or sort by them

My Database design is as follows

==============================

TABLE: Users

• id (pk)

==============================

TABLE: Notifications

• id (pk)
• user_id (fk, indexed)
• text_id (fk - NotificationTexts table)
• values (blob) [containing the array of values, to input into the text string]
• createdDateTime (DateTime)
• read (boolean)

[ClusterIndex] => (user_id, createdDateTime)

==============================

TABLE: NotificationTexts

• id (pk)
• text_id (uniquem indexed)
• text (varchar) [{ You have a new upcoming payment: @paymentID, with a due date of @dueDate }]
• note (varchar, nullable) [notes for developers, informational column]

Answer 5

I am also trying to figure out how to design a notification system. Regarding notification status (read, unread, deleted, archived, ect) I think that it would be good a good candidate to for ENUM. I think it is possible that there will be more than two different types of status other than READ and UNREAD such as deleted, archived, seen, dismissed, ect.

That will allow you to expand as your needs evolve.

Also I think it may make sense (at least in my case) to have a field to store an action url or a link. Some notifications could require or prompt the user to follow a link.

It also may make sense to have a notification type as well if you want different types. I am thinking there could be system notifications (such as a verify email notification) and user prompted notifications (such as a friend request).

Here is the structure I think would be a minimum to have a decent notification system.

users
-------------
id
username
password
email

notifications
-------------
id
user_id (fk)
notification_type (enum)
notification_status (enum)
notification_action (link)
notification_text
date_created (timestamp)

Answer 6

Table are following

User

• userId (Integer)
• fullName(VarChar)

Notification

• notificationId (Integer)
• creationDate (Date)
• notificationDetailUrl (VarChar)
• isRead (bollean)
• description (VarChar)
• userId (F.K)