Facebook like notifications tracking (DB Design)

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 ?

From stackoverflow

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

  atif089 : so we add rows on this table when a notification is created or when a notification is read by the user ?

  espais : i would imagine that when you have a notification created, this table would be updated as well...once your user views the notification you would update the read_status field from false to true

  atif089 : that means when a notification is added, we need to find the users friends and insert all those rows in the second table right. But the problem arising would be friend requests made after notifications. Friends made after a notification wont have their row in tblUserNotificationStatus

  atif089 : I guess your answer is not what I was actually looking for but it gave me a big hint.

• 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

  Jasie : Why is this join necessary? Just put the read field in the notifications table and you've saved yourself time and space.

  atif089 : The `notificationsread` table will store only 1 row for each user. Which will be updated when the user views his notifications to the latest notificationID. So we dont actually need that field itself. Thanks for the comment :)

  Harry Pham : I think what `Jasie` mean is that, why do you even need the `notificationRead` table. Is there something wrong with just adding a `readStatus` field into your `notification` table. So when a notification create, its `readStatus` is false, and only turn `true` when the user open a notification page.

  atif089 : updated. The `notificationRead` will keep a track of last time the user viewed their notifications and show all other notifications as unread.

  Harry Pham : I am convinced now that this is a good design. Good job.

  Mike : I also think this is the best design posibel, so you you don't have update constantly notofications table (that would be pretty heavy) just push data there and select using 2 conditions (user_id, time) instead of 3 (user_id, time, notificationRead)

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

  atif089 : Exactly! Why didn't I think this before, just add a notification_sync field to user table and use only one table for notifications table. I guess this is the best thing to implement.. Thanks bro and your is the answer

  Joe Mabel : You're welcome.

  Harry Pham : Is the answer below base on this design?