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