Database design to store notifications to users

Question

I'm working on a literature community website. (screenshot) And I'm trying to figure out how to notify users when someone comments on something they posted to the site, when someone they are watching submissions a new literature peice, etc.

I'm trying to figure out how to structure the database to store this information. I've come up with two possible ideas.

1. Store a link to the notifiable object, a field describing the type of action (new, update, etc) that the user is being notified of. This makes for complex display code but it means I can change how notifications work rather easily. This also increase the data I need to pull from the database unless I use a cache field to dump a hash of relevant attributes into the table.

   • notifiable_type
   • notifiable_id
   • user_id
   • action
   • notifiable_cache (optional, stores a hash of selected attributes from notifiable object)

2. Treat the notifications like email and just save them to the database with a subject and message. This results in a simple view but a complex model and prevents me from easily changing how notifications work.

   • user_id
   • title
   • message

I'm looking for other ideas and comments on the two I listed above.

Answer 1

I think your first option is the best. It's more scalable than the second and it gives you the ability to look up every notification of a certain type quite easily. The total amount of data you will be putting away will also be smaller, because you won't have to save entire messages to users.
If you take care in how you write and design your code, I don't think it'll be too complicated.

If, however, the notifications are not likely to change, the second option may be easier to implement.

Answer 2

I'm not completely sure what the "action" field in #1 is intended to be, but if I understand your need correctly, you're essentially wanting to have users subscribe to a queue of notifications, right? Are these notifications intended to be sent to the user via email, or only displayed when they login, or both?

I'd be tempted to really think of this as a queue, where you're "publishing" notifications, and users are "subscribed" to any notification with their user_id associated with it. In a relational schema, you probably do want to use something like #1, where you have a notification with a type, associated with a user. Index on user_id of course to make sure that you can get their notifications quickly. If you'll be querying this a lot, caching whatever you need for display purposes makes a great deal of sense so that you don't have to join in any other tables -- that's assuming that the display data can't change after the notification has been 'sent'.

However, if these notifications won't need to update in real-time while the user's on the site (for example if they're shown at login, or delivered by email), then you can query just once when they login, and cache the notifications. If you'll be querying this constantly in real-time to check for new notifications, and you have a lot of users, this is going to cause you trouble eventually as the table grows. You can shard it by setting up separate tables for different notification types, perhaps, or divide by user_id, but that will only get you so far.

You'll also need to make sure that you prune the table. You may need to add a flag to indicate that the user has seen the notification already, but ideally once they've seen it you can delete it, and that will keep the table small.

Another alternative is to keep the notifications outside your rdbms. Consider keeping the notifications in memcached, for example, if the possibility of losing them is acceptable (if the server goes down, for example). Or look at Redis (http://code.google.com/p/redis/), which would make this problem very easy -- store notifications, and create a set for each user, and you're almost done.

Just some thoughts, hope they're useful.