Database Schema For Notification System Similar to Facebooks
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?
Answer
Here is how I ended up solving this problem.
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.