Building a notification system [closed]

A notification is about something (object = event, friendship..) being changed (verb = added, requested..) by someone (actor) and reported to the user (subject). Here is a normalized data structure (though I've used MongoDB). You need to notify certain users about changes. So it's per-user notifications.. meaning that if there were 100 users involved, you generate 100 notifications.

╔═════════════╗      ╔═══════════════════╗      ╔════════════════════╗
║notification ║      ║notification_object║      ║notification_change ║
╟─────────────╢      ╟───────────────────╢      ╟────────────────────╢
║ID           ║—1:n—→║ID                 ║—1:n—→║ID                  ║
║userID       ║      ║notificationID     ║      ║notificationObjectID║
╚═════════════╝      ║object             ║      ║verb                ║
                     ╚═══════════════════╝      ║actor               ║
                                                ╚════════════════════╝

(Add time fields where you see fit)

This is basically for grouping changes per object, so that you could say "You have 3 friend requests". And grouping per actor is useful, so that you could say "User James Bond made changes in your bed". This also gives ability to translate and count notifications as you like.

But, since object is just an ID, you would need to get all extra info about object you want with separate calls, unless object actually changes and you want to show that history (so for example "user changed title of event to ...")

Since notifications are close to realtime for users on the site, I would tie them with nodejs + websockets client with php pushing update to nodejs for all listeners as change gets added.

This is really an abstract question, so I guess we are just going to have to discuss it instead of pointing out what you should or shouldn't do.

Here's what I think about your concerns:

• Yes, a notification system is complex, but not as hell though. You can have many different approaches on modeling and implementing such systems, and they can have from a medium to a high-level of complexity;

• Pesonally, I always try to make stuff database-driven. Why? Because I can guarantee having full control of everything that's going on - but that's just me, you can have control without a database-driven approach; trust me, you are gonna want control on that case;

• Let me exemplify a real case for you, so you can start from somewhere. In the past year I've modeled and implemented a notification system in some kind of a social network (not like facebook, of course). The way I used to store notifications there? I had a notifications table, where I kept the generator_user_id (the ID of the user that is generating the notification), the target_user_id (kind of obvious, isn't it?), the notification_type_id (that referenced to a different table with notification types), and all that necessary stuff we need to fill our tables with (timestamps, flags, etc). My notification_types table used to have a relation with a notification_templates table, that stored specific templates for each type of notification. For instance, I had a POST_REPLY type, that had a template kind of like {USER} HAS REPLIED ONE OF YOUR #POSTS. From there, I just treated the {} as a variable and the # as a reference link;

• Yes, performance should and must be ok. When you think of notifications you think of server pushing from head to toe. Either if you are going to do it with ajax requests or whatever, you are gonna have to worry about performance. But I think that's a second time concern;

That model that I've designed is, of course, not the only one that you can follow, neither the best as well. I hope my answer, at least, follows you into the right direction.

╔════════════════════╗
║notification        ║
╟────────────────────╢
║Username            ║
║Object              ║
║verb                ║
║actor               ║
║isRead              ║
╚════════════════════╝

This looks a good answer rather than having 2 collections. You can query by username, object and isRead to get new events(like 3 pending friend requests, 4 questions asked etc...)

Let me know if there is problem with this schema.

I personally don't understand very well the diagram for the accepted answer, So I'm going to attach a database diagram base on what I could learn from the accepted answer and other pages.

Improvements are well received.