0

I am trying to make notification system for my website:

This is the table structure

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

Now my understanding is that when a notification is added, we need to find the users friends and insert all those rows in the notification table right ? If this is correct, then, what would be the best way to achieve this ?

  1. Triggers?
  2. Write T-SQL (sql query in server side) to Select all the friends and then use SQL bulk copy?
Monodeep
  • 1,392
  • 1
  • 17
  • 39
  • Your understanding about having to add more rows to a notification table when a single row is added is not necessarily right. It might be, but it's not clear what your notification system is supposed to do or how it's supposed to do it. – Dan Bracuk Apr 03 '13 at 21:35
  • @DanBracuk I want a notification system similar to facebook, when a user posts something all his friends should see a notification. If someone votes on my post, i should receive a notification. – Monodeep Apr 03 '13 at 21:41
  • 1
    Inserting a notification per friend is not a great way to do that. A better way would be to simply run a query against the relationship between the friends and return the results like in a view. You're storing way less data per event/friend. That's the power of relational data. – RThomas Apr 03 '13 at 21:43
  • @RThomas Can you please explain it in detail? How would i know which user has viewed the notification? I need to show the users the notification even if they log in after a couple of months. Correct me if i am wrong. – Monodeep Apr 03 '13 at 21:46
  • I got a 404 error on the link shown by @Monodeep. Was it just me, or did that happen to others as well. – Dan Bracuk Apr 03 '13 at 21:50
  • [http://stackoverflow.com/questions/1887602/facebook-like-notifications-tracking-db-design/](http://stackoverflow.com/questions/1887602/facebook-like-notifications-tracking-db-design/) @DanBracuk My link formatting was wrong. Sorry for the trouble. Updated now. – Monodeep Apr 03 '13 at 21:52
  • @marc_s Please comment on my question and RThomas answer. I have seen your answers in almost all SQL related questions. Please clarify my doubt. – Monodeep Apr 04 '13 at 06:00

1 Answers1

0

A good relational approach would be to set up your tables so that you have a people table. Each person has a primary key that uniquely identifies them. Then another tables links the peoplekeys together as friends. There are a few ways to go about this but as a rough example:

People
---------------------------------------
PeopleKey | Name | <other profile data>

Then you have a table that relates people to each other as friends.

Friendships
----------------------------------------------------------------------
PeopleKey |  FriendKey (fk to peoplekey) |  <details about friendship>

Then you have your notifications table that says a person has done something.

Notification
-----------------------------------------------------------
NotificationKey | PeopleKey | Date | <notification details>

With a query and your friendship relations you can obtain all the notifications for all the friends of a person:

select notification.* from notification inner join 
friendships on friendships.peoplekey = notification.peoplekey
where date = @importantdate

The relationship can reveal a lot without having to store repetitive data. There are 1000 other ways to join, query, or link the tables to friendships and events. So for example, you could say show all events where peoplekey is one of my friends. Show all events where peoplekey is only one of my friends etc. etc. etc.

RThomas
  • 10,702
  • 2
  • 48
  • 61
  • If i vote up/down a post by a random user who is not my friend, how will the random user get a notification? – Monodeep Apr 03 '13 at 22:25
  • Well, that seems to be leaving the scope of your original question (notifying friends) but adding a log table for those types of events is one idea. I'd still handle the update from your application however at the same time the key event happens leveraging relations in the update query. I wouldn't use a trigger. – RThomas Apr 03 '13 at 22:30
  • The idea about adding a log table is fine but how am i supposed to maintain the read state as per your design? – Monodeep Apr 03 '13 at 22:33