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'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  

Answer 2

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.