Database design to store notifications to users [closed]

Closed. This question is opinion-based. It is not currently accepting answers. 开发者_如何学Python

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 7 months ago.

The community reviewed whether to reopen this question 7 months ago and left it closed:

Original close reason(s) were not resolved

Improve this 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.

---

I'm working on a project utilizing notifications as well, I'm not sure if you got yours sorted out by now or if it might help but this is the table structure I used:

Notifications:  
- ID (PK)
- recipient_id
- sender_id
- activity_type ('comment on a post', 'sent friend request', etc) 
- object_type ('post', 'photo', etc)
- object_url (to provide a direct link to the object of the notification in HTML)
- time_sent
- is_unread 

---

message :
-id_message(pk)
-to 
-from 
-subject
-message
-status (read,unread)

reply_message :
-id_reply(pk)
-id_message
-from
-message
-status (read,unread)

notification :
-id_user
-id_notify(pk)
-notify_type (message, or reply_message)
-notify_desc (comment/reply on your message)
-status (look,unlook)

notify_detail : (for notify, when user reply or comment more than 1)
-id_notify
-id_sender
-id_detail (input id_message, id_reply)

how about this? you can mix it with comment or reply comment.

---

I recently did this in the Rails backend of an iOS app and basically used (2) with a timestamp but stored in a Redis list indexed by user. The weaker schema (with everything basically stuffed in 'message') let us move far quicker during development and early betas.

In addition, since the notifications are popped off the Redis list, there's not a lot of old messages to worry about as far as changing formats, especially if you can prune 'old' messages.

---

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.

---

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.

---

support there are already 2 models:

- users - id - name - notifications - id - title - content

I will create a new model: - user_notifications - id - user_id - notification_ids: Array[]

Every user related with a user_notifications record, the notification_ids is an array which include this user's associated notifications id list.

So a notification can be shown to many users, and user can easily delete a notification or add new notification, no effect with other users.