Suggestions for a user notification system in MySql and PHP

Question

I am implementing a notification system and seeing if these suggestions are valid to set up, if one is better than the other or a better solution is available:

A notification is added to the database. A guest / identifiable user logs onto or uses the site. They are greeted with notifications they haven't seen before with the option to close or read later.

• Notification table stores notification texts and Id.
• Option 1: Alerts table stores all users who have read the notification
• Option 2: Alerts table stores all users who have NOT read the notification

Are these options much of a muchness, is it better to add potentially 100,000+ alerts and as those users discard or interact with the notice, their status is changed or the alert deleted. This could become a very large table...

What's a more extensible set up for custom notifications based on user activity?

Answer 1

I wouldn't do it that way. I'd store a record for each (user,notification) and mark each record as read or unread. You can then record when they read it, which might be important depending on your application (eg if you needed an audit trail of some kind).

100k records is not very big. Don't worry about size until you get to at least 10 million records. If necessary archive them at some point. But you should do some estimates on how quickly you'll generate 10 million records. If it's 3 days, then yeah you have a problem. If it's 3 years then you don't.

This option of course has the notification text in a separate table.

This should also scale extremely well with even higher number of messages as you select the unread messages for a user (indexed) and can either join to get the notification text (if your table is in the tens of millions of records size) or select them and then separately select the messages.

Partitioning the (user,notification) table is easy: you base it on user ranges.

And when users delete messages, generally you should just mark it as deleted rather than actually deleting it. Most of the time there's not much reason to delete anything in a database.

Answer 2

Alternatively, you could store a set of references to the notes which have yet to be read on a per-user-profile basis, and then remove them when they're displayed (general use case is probably read all notes at once). This way you only have a tiny query when you pull an individual user, and your cost is in the insertion time for the global message, rather than filtering a big unread message table.