Best way to store user notifications?

Question

Tell people on the link exchange site that people can share links and post comments. Users following the link should be notified when comments are posted there. Users following a category should be notified that links are included in this category. What would be the best way to store these notifications?

I could store them separately, in which case their receipt would be simple, but each time a link is posted, I will need to add a notification for each of, say, 10,000 people following this category.

I could also just count the notifications every time, in which case I would count the number of new comments since the last user login and display this. However, I could not save the old notifications.

What are my options?

---

Ok, here is my database schema:

comments - id - user - link - content links - id - user - content subscriptions - id - user - link 

Each time a new comment is created for a link, all users who subscribe to this link must have a “notification” that they will receive the next time they log in.

Answer 1

Notifications are usually a highly readable object, so you want to quickly get a list of unread notifications, rather than trying to count on the fly. In addition, trying to compare with login times does not contribute to a good user experience. If the user does not notice them initially or refreshes the page or clicks on it to view its data, all notifications have disappeared.

With that in mind, I would suggest having a user_notification table with a read / unread column in it. Although it’s good to know where a problem may arise in your software scalability, it will not be so until you start to keep (millions of records). You can index the user column to increase speed, and if you ever have such a need, you can divide by user to help scale all drives / servers.

It is up to you to decide whether you want to have notifications in a separate table or just in the users_notifications table, and this will be another area for optimization.

Answer 2

Keep track of how they viewed notifications for the last time. Then, any messages they sign were created after the time they appear in the notifications the next time they look at the notifications.