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 ?
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.
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.
Now my understanding is that when a notification is added, we need to find the users friends and insert all those rows(a notification per friend) in the notification table right ? If this is correct, then, what would be the best way to achieve this ?
Triggers?
Write T-SQL (sql query in server side) to Select all the friends and then use SQL bulk copy?