43

I am trying to design a notification system similar to facebook and I have reached a bit of a brick wall. My requirement is to be able to support an infinite number of notification types that may have different types of meta data required to be rendered.

I'm thinking that I will design the schema as follows:

**Notification**
Id (int)
TypeId (int)
RecipientId (int)
SenderId (int)
SendDateTime (DateTime)
Read (bool)
MessageData (...Blob?)
Deleted (bool)

**NotificationType**
Id
Name
Description

I really want to try to avoid storing HTML strings in my database, however, I also am not particularly fond of storing blobs either.

It is possible that I could do a look up on the NotificationType table and reference another table that stores data specific to that type, however, that would mean that everytime that I created an new notificationtype i would need to create a new table. I believe that I would also be getting myself into a world of having to write dynamic SQL to get the data out.

Does anyone have any suggestions for me?

mcottingham
  • 1,926
  • 2
  • 18
  • 28
  • I'm working on this same problem. I have a similar structure as you, but went with the html route. In the description column, I have something like . Then I query the notification via user_id and use JavaScript to populate the message based on span ids. – Ricky Mason Feb 26 '13 at 05:42
  • @mcottingham I am dealing with same kind of problem, I thought about doing something similar only storing it as XML, but it just doesn't look right...I wish I could take a peek to see how FB did it.. – formatc Aug 07 '13 at 20:19

1 Answers1

56

Here is how I ended up solving this problem.

Notifications Schema

I decided to use a dictionary to store data that is unique to each notification type. I then serialize that dictionary object into a binary string and store that in the database along with each notification. I have a template assigned to each notification type that contains place-holders ie. '{song-title}' that I can quickly replace with values from my dictionary object.

mcottingham
  • 1,926
  • 2
  • 18
  • 28
  • 8
    did you solve problem "user deleted some content and i have to delete all related notofocations"? – Backs Dec 17 '15 at 15:26
  • I did do similar thing for one of my application. But delete user was soft delete meaning nothing has to be deleted. – Navap May 25 '18 at 02:13
  • 10
    instead of bool for "Read" you could have readAt and use a date/time. same with deleted. – ChatGPT Nov 02 '20 at 07:56