Database design to store notifications to users

push notification database design
database design for reminders or alerts
notification module design
notification data model
messaging app database design
notification table structure
database schema for email system
chat system database design

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 

ruby on rails - Database design to store notifications to users, How to design the database schema to store notification information? user_id must be stored as part of notification data to know which user  In this data model, user and message are the main entities to store users’ and messages’ details. Columns in the user table would be user related attributes like first_name, last_name, etc. Some self-explanatory columns in the message table would be subject, message_body, create_date and expiry_date.

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.

Designing a notification system for software applications, It seems as though the amount of notifications each user of the site would receive will total to a very large number in the database. Ad by Pitney Bowes. How can  User A created a post. User A updated the post. User A deleted the post. When a post is created, server inserts it into the database with post_id as 23. Now for entity_id 23, there can be 3 different types of notifications. To identify the type of notification, we introduce entity_type_id here.

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.

What is the best way to store notifications in the database (MySQL , store only one the last ID read (which means all previous notifications for given user are also read) - this will be just one field in users table. In this scenario using a relational database I could store all of the settings required for each users application in a single table "settings", with each setting taking up a column and each user taking up a row. Whilst this would be the simplest approach to get started, I don't feel like it has longevity.

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.

Notifications database design, Notification "you have 3 friend requests added" can be stored differently. for each user and have several links in your notification text. 4. You usually have to query for settings Normally you will have to query the database to check whether a user has a particular setting, rather than load all of the user settings to find out one particular property. This means you need to store the data in a way that still allows you to query for individual settings.

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.

Understanding a notification system, In this data model, user and message are the main entities to store users' and Data model for a messaging system, table 'User' One of the best usages of this messaging system is to send notifications to users who have  I'm trying to create a notification system for my social network. But I'm stuck on the database design. so when a user comments on another user I want to display "X commented on your post." or when someone follows another I want to display a notification. This is my table so far:

Vertabelo Database Modeler, Notifications can be really annoying, especially if the user gets of how do we store the user's settings in a relational database? This makes it impossible to predict what the database schema should look like ahead of time. - users - id - name - notifications - id - title - content je vais créer un nouveau modèle: - user_notifications - id - user_id - notification_ids: Array[] chaque utilisateur associé à un enregistrement user_notifications, le notification_ids est un tableau qui inclut la liste d'id de notifications associée à cet utilisateur.

Storing User Settings in a Relational Database, The service should be able to send an email or a push notification to users if there is a price change to Ideally, the notification API should only keep reference of the UserID. Manual price update with RDBMS database high level design. A notification is about something (object = event, friendship..) being changed (verb = added, requested..) by someone (actor) and reported to the user (subject). Here is a normalized data structure (though I've used MongoDB). You need to notify certain users about changes.

Architecting a Scalable Notification Service - The Startup, Each user has an inbox database and other users send messages by dropping change notification, CouchDB will have to keep an HTTP connection open. a filter is a JavaScript function that gets stored in a design document and is later  Creating a User Profile Store with Node.js and a NoSQL Database Nic Raboy, Developer Advocate, Couchbase on July 31, 2017 There are many use-cases for NoSQL databases, but one that I encounter frequently is in the realm of storing user data.

Comments
  • Too bad this topic doesn't get a lot of attention. Have you made a choice? I'm just curious...
  • I have no knowledge on designing notification tables but what I really like about this structure is the idea of a direct link. Once you extract the activity_type you can replace any word in the text with the link using Regex +1
  • The idea of storing a direct link is perfect!
  • What would your table of user settings look like? With the purpose of the user being able to toggle notifications on/off for a particular activity.
  • Can you provide an example of user record from your redis list. It would help me lot. I try to implement notification with read/unread.
  • I'm basically trying to copy what deviantArt does for user notification.
  • if you need keep a state for every notifications, e.g. is_read, you need change the user_notifications table: user_id(int), notification_id(int), read_at(datetime) , user has many user_notifications