Click here to Skip to main content
11,796,774 members (79,422 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: ASP.NET SQL SQL-Server
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

id (pk)
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?
Posted 3-Apr-13 20:29pm
Edited 3-Apr-13 20:30pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Firstly, I think facebook is based on a NoSQL database architecture... But...

What I would do is have each users notification sequence have a sequential notificationId. Then if A is subscribed to or following or friended B then in the subscription link, record what the last read notificationId is and present everything greater.

This way you are only tracking where A is up to in B's notification sequence, not a record for every notification.

Should C also follow B, then the start point can easily be set to the current notificationId, so all the history doesn't appear.

Does this make sense, and help?
Member 7781963 at 5-Apr-13 4:23am
Thanks for the answer. Can you please show me the DB Design? I am unable to understand your answer. Thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Abhinav S 379
1 OriginalGriff 360
2 Maciej Los 360
3 CPallini 295
4 KrunalRohit 280
0 OriginalGriff 2,032
1 Maciej Los 1,725
2 KrunalRohit 1,310
3 CPallini 1,055
4 ppolymorphe 883

Advertise | Privacy | Mobile
Web01 | 2.8.151002.1 | Last Updated 5 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100