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`
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_type (for complexity like notifications for pictures, videos, apps etc.)
id (pk) (i dont think this field is required, anyways)
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`
`notifications`.`userid` IN ( ... query to get a list of friends ...)
(`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 ?
Write T-SQL (sql query in server side) to Select all the friends and then use SQL bulk copy?