Facebook like notifications tracking (DB Design)

Question

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

Wont 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 has been read and which hasn't ?

Answer 1

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 2

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 helps others.

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