I have a table (MainObject) where each record can have subscribers and have notifications (table structure below). The application needs to send alerts when a notification occurs, for each subscription. Some of these individual records could have lots of subscribers - I have a feeling that the way I'm looking at designing this isn't very good for high usage, so for argument's sake let's say 100s of thousands of subscriptions per MainObject record in the highest cases.

Looking at the table structure below, is that the best way to design this? Let's say a MainObject record has 100,000 subscribers, and an application event occurs that creates a notification (so adds a Notification record). Now for each of the 100,000 subscribers, 100,000 Alert records would need to be inserted in the database. 100 simple application events would result in 10 MILLION alert records being created - there would literally be a backlog of thousands upon thousands of insertion statements at any given time! (Of course, an asynchronous application process could trigger the insertions, but my whole design doesn't seem ideal.)

This doesn't seem like a good design. How do high-throughput systems handle such subscription/notification design requirements? Have any of you modelled something like this before, or could help explain a better way of doing this?

MainObject
-----------
id

Subscription
------------
id
subscriber_id (fk)
mainobject_id (fk)

Notification
-----------
id
mainobject_id (fk)

Alert
-----
id
notification_id (fk)

Do you have to insert a record for each Alert for each user? If not then what about the following method:

Create a new table:

Code:

   create table SubscriberNotified(
      notification_id
      subscriber_id 
      date_added
   )

Alter the create notification process:

You'll now want to add a new record to this table with a null subscriber_id field.

Create new asynchronous process:

To look for records in this table. When it finds a record it would pull the next 1000 subscriber_id's that should be sent the notification, it would then send a notification to these users and afterwards update the SubscriberNotified record to say what subscriber_id it got up to. When there are no more subscribers to send to then it could delete the record.

If the process ever dies then restarting the process will allow it to continue from where it stopped. It would be a good idea to include a heartbeat in the process so you can check it is still running. Another good idea to have a process that continually checks this program is running and restart it if it fails.

This gets round the need for millions of records to be inserted each time a new notification gets sent out. You can alter the 1000 figure up or down depending on how reliable your processes are. You'll want to log the fact that notification has started and ended so you can see what's happening.

Mike