Click here to Skip to main content
Rate this: bad
good
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
 
    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?
Posted 3-Apr-13 21:29pm
Edited 3-Apr-13 21:30pm
v2

1 solution

Rate this: bad
good
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?
  Permalink  
Comments
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 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 OriginalGriff 120
3 PIEBALDconsult 110
4 BillWoodruff 85
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 5 Apr 2013
Copyright © CodeProject, 1999-2014
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