Building a notification system

I am at the start of building a Facebook style notification system for our page (social gaming type) and I'm now researching what would be the best way to design such system. I'm not interested in how to push notifications to the user or anything like that (for now even). I am researching how to build the system on the server (how to store notifications, where to store them, how to fetch them etc...).

So ... some requirements that we have:

• at peak times we have about 1k concurrent logged-in users (and many more guests, but they don't matter here as they will not have notifications) that will generate many events
• there will be different types of notifications (user A has added you as a friend, user B has commented on your profile, user C has liked your image, user D has beaten you on game X, ...)
• most events will generate 1 notification for 1 user (user X has liked your image), but there will be cases where one event will generate many notifications (it's user Y's birthday for instance)
• notifications should be grouped together; if for instance four different users like some image, the owner of that image should get one notification stating that four users have liked the image and not four separate notifications (just like FB does)

OK so what I was thinking is that I should create some sort of queue where I would store events when they happen. Then I would have a background job (gearman?) that would look at that queue and generate notifications based on those events. This job would then store notifications in the database for each user (so if an event affects 10 users, there would be 10 separate notifications). Then when user would open a page with the list of notifications I would read all those notifications for him (we ware thinking to limiting this to 100 latest notifications) and group them together and then finally display them.

Things I'm concerned about with this approach:

• complex as hell :)
• is database the best storage here (we are using MySQL) or should I use something else (redis seems like a good fit too)
• what should I store as a notification? user ID, user ID who initiated the event, type of event (so that I can group them and display appropriate text) but then I kinda don't know how to store the actual data of the notification (for instance URL&title of the image that was liked). Should I just "bake" that info when I generate the notification, or should I store the ID of the record (image, profile, ...) being affected and pull the info out of the DB when displaying the notification.
• performance should be OK here, even if I have to process 100 notifications on-the-fly when displaying the notifications page
• possible performance problem on every request because I would have to display the number of unread notifications to the user (which could be a problem in its own since I would group notifications together). This could be avoided though if I generated the view of notifications (where they are grouped) in the background and not on-the-fly

So what do you think about my proposed solution and my concerns? Please comment if you think I should mention anything else that would be relevant here.

Oh, we are using PHP for our page, but that shouldn't be a big factor here I think.

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.