Best way of storing user notifications?

Question

Say on a link-sharing site people can share links and post comments. Users following a link need to be notified when comments are posted there. Users following a category need to be notified when links are shared in that category. What would be the best way to store these notifications?

I could store them individually, in which case fetching them would be simple, but every time a link is posted I'd have to add a notification for each of, say, 10,000 people following that category.

I could also just calculate the notifications each time, in which case I would count the number of new comments since the last time a user logged in and display that. However, then I wouldn't be able to save old notifications.

What are my options?

---

Okay, here's my database schema:

comments
 - id
 - user
 - link
 - content

links
 - id
 - user
 - content

subscriptions
 - id
 - user
 - link

Every time a new comment is made on a link, all users who are subscribed to that link should have a "notification" that they will receive on their next login.

Answer 1

Notifications are usually a highly read object, so you want to be able to get the list of currently unread notifications very quickly, not try and calculate on the fly. Also, trying to compare against login times does not make for a good user experience. If the user doesn't notice them initially, or refreshes the page, or clicks on one to view its details all the notifications are gone.

With that in mind, I would suggest having a user_notification table with a read/unread column in it. While it's good to know where in your program scalablity could be an issue, it won't be until you're well underway (millions of records). You can index the user column to increase speed, and if you ever do have the need, you can partition by user to help scale across drives/servers.

It's up to you to decide if you want to have notifications be in a separate table, or just part of the users_notifications table and that would be another area to optimize around.

Answer 2

Just keep track of the last time they viewed the notifications. Then any posts they subscribe to that were created after that time will be in the notifications for next time they view notifications.