1 to many notification system

Question

I am looking for a push to the right direction, mostly on the database side, but any type of push is accepted (code wise!), regarding a 1 to many notification system.

where a bunch of user receive a notification triggered by the users interest (e.g another user).

Example

• user1, user2 and user3 are interested in Subject A.
• Subject A, posted an activity.
• user1, user2 and user3 receives a notification from Subject A.
• user2 and user3 viewed the update, therefore they will no longer be notified on that update.
• user1 still gets notified because she's yet to view the update.

The Magic: A thousand users can and will continue to receive the same notification, but individual users will stop receiving the notification if they view or interact with the update.

My implementation

I build a notification table in my DB like

id | sender | context | action | receiver | unread 
1  | Nobel  | Prize   | Won    | Moi      | true   #default

the when subject A trigger a notification i get all users interested in subject A, then add that same notification to the table but with different receiver.

when an user interact with the update the "unread column" is updated with false. then when a particular user walks in (not realistically) i select notification from the notification table that has the users name on it, the count the on that are unread false, then display the number in the red notification bubble.

My Opinion

this seems really verbose, and the table can grow to a number that don't even have a name yet like megaZillion very quickly. like if a subject has 10,000 interest, 10,000 new records will be added to the table just because the subject didn't find any soda in the fridge (real tweet).

Question: is there better way to do this.

Answer 1

Obvious alternative is to store the notification only once, and store read/unread flag separately as notificationID, userID pair. "Read" would seem a safer bet since it copes better with inactive users.

Then you'd query for all notifications the user is interested in that don't have the read flag.

There's going to be a lot of data anyways if you require to track all this for each user - that will always mean you need to store the users' relation to each notification. Most you can do is normalize the tables.