Designing a notification system

It has become common for any social mobile or web app to support notifications and track user activities within that application.


In this blog, we’ll talk about how we have implemented a notification system for one of our projects. We’ll also be trying to provide answers to the following questions:

  • How to design the database schema to store notification information?
  • How to generate the notification messages?
  • How to implement server side pagination for notifications?
  • How to improve performance and reduce server response time?

Although the implementation is done using Node.js, the choice of the backend server side programming language is not a major consideration for this blog.


Before we go into the database design and implementation, let us look into some of the major challenges we had faced.

Data Growth

One of the major issues is the rate at which our data grows. For each and every action of a user, there should be a record present in the database. The emphasis has to be on avoiding storage of duplicate and interrelated data. This will dramatically reduce the database size.

Multiple notifiers

A notification can be sent to either a single user or a group of users. The database schema should be designed in such a way that it facilitates the addition of more number of notifiers.
Note: The term notifiers is used to refer to those users who will be receiving notifications.

Multiple entities

An entity here refers to a module. If we take a social networking app like Facebook, then the various entities are posts, friend requests, groups etc. Often application features are extended and new entities will be introduced, so new notifications related to those entities will also have to be added. The database schema should be flexible enough to support notifications for new entities easily.


For every notification, there are 4 key values:

  1. Actor
  2. Notifier
  3. Entity
  4. Entity type


Actor is the user who is responsible for triggering a notification. A user can be identified by user_id (Primary key) of users entity. So, user_id must be stored as part of notification data to know which user has triggered the notification. By using this, we can also identify the user’s activities.

For example: User A has sent a friend request to user B. So, for user B this becomes a notification and for user A this will be an activity.


Notifier is the user to whom the notification has to be sent. Notifier can be either a single user or multiple users. So we need to store the user_ids of all the notifiers to identify the notifier details and send notifications.


Entity will help us to know what the notification is about. For example, a notification can be about a group or a post or a comment etc. To get the details of any entity, all we need is to know entity table’s primary key value. This value will be referred as entity_id.

Entity type

Entity types are the different types of notifications. Suppose if there are 5 types of different notifications supported by an application, then there will be 5 entity types. To identify this, we will give an ID to each and every entity type.

For example: Post is an entity and there are 3 different kinds of notifications related to post.

  1. User A created a post.
  2. User A updated the post.
  3. 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. Using an entity_id and entity_type_id, we can know what notification has to be generated.

entity_id entity_type_id Description Notification
23 1 This notification is sent when a post is created. User A created
a post.
23 2 This notification is sent when a post is updated. User A updated
the post.
23 3 This notification is sent when a post is deleted. User A deleted
the post.
21 4 This notification is sent when user comments on a post. User B
commented on
your post.
21 5 This notification is sent when user updates their comment. User B updated
his/her comment
on your post.




notification_object table

This table will contain the details about the notification entity and entity type.


In an application, there can be many different kinds of notifications. For each and every notification, there is a unique id associated, by which we can identify from which table notification details has to be fetched and what notification message has to be constructed.
For example :

Entity type ID Entity Notification description
1 Post Creating a post.
2 Post Updating a post.
3 Post Deleting a post.
4 Message Send a message.

We can define the notification types in a config file on the server.


This is the primary key value for the notification related entity.
For example: If the user has added a post then all the post related information will be stored in the posts table. Each table will have a unique primary key by which we can identify a row in the table. In this case, we can get the details of the post by post_id, where post_id is the primary key of posts table. So for sending notifications, we store the post_id as entity_id in notification_object table.


entity_type_id entity_id Description
1 23 This notification is about creating a
“Post”, where the “post_id” is 23.
6 3 This notification is about creating a
“Group”, where the “group_id” is 3.
1 5 This notification is about creating a
“Post”, where the “post_id” is 5.
3 5 This notification is about deleting a
“Post”, where the “post_id” is 5.

notification_change table

This table will hold the information regarding the user who is responsible for the creation of the notification; in other words, the actor.


This is a foreign key to the notification_object table to refer to the details of the notification.


This is the user_id of the user who created the notification.

notification table

This table will hold the information regarding the notifiers, the users to whom the notification has to be sent.


This is a foreign key to the notification_object table to refer to the details of the notification.


This is the user_id of the user to whom the notification has to be sent. If there are multiple notifiers then multiple records will be inserted in the notification table.


Let’s go through the implementation details by taking some examples:

Suppose there are 3 users in our database. All 3 of them are friends with each other.


user_id username
1 Nancy
2 Scott
3 Linda

The following two actions are performed.

Action 1: Nancy added a post in the group – “Traveling to Mumbai.”
A notification has to be sent to Scott and Linda.

Action 2: Scott has commented on that post – “Happy journey!!”
This notification has to be sent only to Nancy.

How to form the notification data

First, we need to identify the notification data before inserting it into the database. Identifying the notification data is very easy.

We need to gather the following details:

  1. Entity type ID
  2. Entity ID
  3. Actor ID
  4. Notifier IDs

Suppose the entity types are as defined below:

entity_type_id Table Notification description
1 Posts Creation of post.
2 Comments Commenting on a post.

Action 1: Nancy adds a post in the group – “Traveling to Mumbai.”
A notification has to be sent to Scott and Linda.

First, we insert the post in the posts table.


post_id post_desc posted_by
posted_on status
1 “Traveling
to Mumbai.”
1 31-12-2016 09:00:12 1

Data collection: Identify the notification data

entity_type_id 1
entity_id 1
actor_id 1
notifier_id 2 , 3

Action 2: Scott has commented on the post. “Happy journey!!”
This notification has to be sent to only Nancy.

We’ll insert this comment in comments table.


post_id created_on status
1 “Happy
2 1 31-12-2016 09:10:23 1

The notification has to be sent only to the creator of the post. Fetching the creator of the post is done by using post_id and get posted_by value.

Data collection: Identify the notification data

entity_type_id 2
entity_id 1
actor_id 2
notifier_id 1

Once you have the data, next thing to do is to store it in a database.

Storing notification details in database

Storing the notification data should follow the below process

  1. First, insert the notification data in notification_object table.
  2. Get the notification_object_id
  3. Insert in notification_change table.
  4. Insert in notification table.

Let us consider the below notification data

entity_type_id 1
entity_id 1
actor_id 1
notifier_id 2 , 3

Start by inserting a record into the notification_object table


id entity_type_id entity_id created_on status
1 1 1 31-12-2016 09:10:23 1

Get the notification_object_id and then insert it in the notification_change table.


id notification_object_id actor_id status
1 1 1 1

Get the notification_object_id and then insert it in the notification table.


id notification_object_id notifier_id status
1 1 2 1
2 1 3 1

Fetch notification details from database

Notification details are fetched for two purposes:

  1. While sending the immediate notification
  2. When the user wants to view his past notifications

Sending immediate notification

A notification has to be sent immediately after the user performs any action. In our example, as soon as Nancy adds a post in the group both Scott and Linda should receive a notification about the post.

To send a notification, you can use Amazon Simple Notification Service.

Below are the steps to be followed for sending the immediate notification:

  1. After inserting the notification details into the database, we have the notification_object_id.
  2. Generate the “Notification message” using notification_object_id.
  3. Get the list of notifiers.
  4. Send notification using Amazon SNS.

Importance of notification_object_id

Using notification_object_id, the following details can be known:

  1. The notification message (entity_type_id and entity_id from notification_object).
  2. The actor (actor_id from notification_change) .
  3. The notifiers (notifier_id from notification)

Generating the notification message

Generating the notification message is a tricky part. Using the i18n library will be helpful to generate strings with dynamic details.

The notification message in our case should be ‘Nancy added a post: “Traveling to Mumbai”.’

The message has three dynamic parts:

  1. Username
  2. Action type
  3. Post description

You can prepare templates in a file and use i18n to generate the message.

The SQL query to fetch the details of a notification will be different for different entity types. The SQL query will change depending on the data required to generate the notification message. For every entity type, there should be one SQL query which takes entity_id as a parameter to get the required details about that entity which will be used in the notification message.

For example:

For the above notification message, the SQL query will fetch username and post description by using entity_id value which is post_id.

action_type can be known from entity_type_id, i.e. action_type can be ‘added a’, ‘updated the’, ‘deleted the’ which will result in the following example results.

  • Nancy added a post: “Travelling to Mumbai”.
  • Nancy updated the post: “Travelling to Kerala”.
  • Nancy deleted the post: “Travelling to Kerala”.

List of notifiers

The list of notifiers can be fetched from the notification table by using the notification_object_id.

By doing an inner join with the notification table on notification_object_id, we can get every notifier_id.

Once the notification message is generated and the notifiers list is available, we can hit the launch button to send notification via Amazon SNS.

Viewing past notifications

Sometimes we might miss the immediate notifications received on our smartphone. It is better to let the user view their past notifications.

The notification has to be sent based on the page size. Page size is the number of notifications to be shown per page. If page size is 10, then the server would send only 10 notifications at a time.

Also, server side pagination can be implemented very easily. The API request should contain the following parameters

  1. user_id
  2. page_size
  3. page_number

user_id refers to the user who has requested for the notifications.
page_size is the number of notifications that has to be sent by the server.
page_number will determine the range of notifications that have to be taken from the database.

For example:

page_size page_number Range
10 1 (1 – 10) First 10 notifications.
10 2 (11 – 20) Next 10 notifications
10 3 (21 – 30) Next 10 notifications

Get the notification_object details

The following details are required to build a notification message.

  1. entity_type_id
  2. entity_id

These details can be taken from the notification_object table. So we can join notification_object and notification table on notification_object_id and add a condition where notifier_id is equal to the requesting user_id.

Suppose we wanted to fetch only last 5 notifications and the result set is as shown below.

order notification
entity_id notifier_id
1 78 2 89 23
2 60 1 45 23
3 45 2 56 23
4 3 2 3 23
5 1 1 23 23

The results should be ordered by created_on date. So the notification_object_id will be in decreasing order.

Generate an array from the result set

>From the result set, generate an array of objects in the same order in which we have received the data.


Generating the notification message

Now that we know the entity_type_id and entity_id, we can easily identify which table to query and what details have to be taken from that table to generate the notification message based on entity_type_id.

Instead of querying the database 5 times, we can group similar entity_type_ids together and fetch at once. This way we’ll have to hit the database only twice.

Once we group entity_ids based on entity_type_id, the data should appear like the above example.
Next, make asynchronous (parallel) database calls and generate the notification message.


Once we get all notifications, we have to group them back in the original order in which they were fetched from database initially. For that, use notification_object_id from the initial array and reorder the array.

This JSON will be sent as a response to the user.

Fetch user activities from database

Fetching the activities of a user is similar to the notification but instead of joining with notification table, we should join with notification_change table and instead of notifier_id we should use actor_id.



  1. This is easy to implement.
  2. This can be easily extended to support new notifications for new entities. All we have to do is define a new entity_type_id.
  3. All SELECT queries will run on the primary key, which will ensure that things are fast.
  4. Notification messages are built dynamically.
    Suppose the notification message is “Nancy was added to group Titans.” And later if someone changes the group name to “Pirates”, the notification message will be “Nancy was added to group Pirates”, since we are building the messages at runtime.


  1. The process of generating a message and sending it should be a part of another component listening on a message queue. This is easily fixable by copy-pasting this code and creating a component.
  2. There is no caching of data for quicker response time.

Posted By: Vishnu K, Osmosee

Are you interested? follow us and get notified of new posts

10 thoughts on “Designing a notification system

  1. Shantaram
    at 4:52 pm

    I’m referring to your blog, nice detailed explanation,
    Also I have Question:
    Are entity_id and entity_type_id foreign keys referring to entity and entity_type ?
    could you please share entity and entity_type tables with some rows/details, thank you

  2. Shantaram
    at 12:02 pm

    Please ignore my previous question
    Now my question is how/where you have mapped action_type/s based on entity_type_id ?
    action_type can be known from entity_type_id, i.e. action_type can be ‘added a’, ‘updated the’, ‘deleted the’

    1. Admin
      at 1:46 pm

      Here is a statement from the blog.
      We can define the notification types in a config file on the server.
      The above statement can be found under NOTIFICATION_OBJECT TABLE >> entity_type_id section

      We don’t have a table for entity_type_ids and action_type/s.
      These are maintained in our notification config file which might look like this
      The key represents entity_type_id.
      // Entity type id 1 is for adding post
      1: {
      name: “Adding post”,
      messageProp: ‘post.add’
      // Can add more details here for each entity
      // Call a method to pre-process message data before building the message etc
      2: {
      name: “Adding post”,
      messageProp: ‘post.update’
      ‘post’: {
      ‘add’: ‘{{username}} added a post. {{description}}.’,
      ‘update’: ‘{{username}} updated a post. {{description}}.’,
      ‘delete’: ‘{{username}} deleted a post. {{description}}.’,
      ‘comment’: ‘{{username}} commented on your post. {{description}}.’ }
      Once you have the data to build message you can use.
      i18n.__(entityTypeDetails[entity_type_id].messageProp, messageData);

      Action types are not limited to only add, update and delete.

      We added different message string for each entity_type_id as shown below en.json file instead of using
      {{username}} {{action_type}} a post. {{post_description}}.
      As you can see in the above example comment message is different from add, update and delete.

      Having different message strings made it simple to change the notification messages.
      Maintaining different tables for entity_type_id and action_type will reduce the response time, since we need to join with 2 more tables to build message for every notification.
      We decided to maintain this information in an object in our server code to improve response time.

      1. Shantaram
        at 3:11 pm

        thanks for the reply
        can you point me to i18n library, so I can came to know about building message.
        // Entity type id 1 is for adding post
        1: {
        name: “Adding post”,
        messageProp: ‘post.add’
        // Can add more details here for each entity
        // Call a method to pre-process message data before building the message etc

        and how to build message using :
        i18n.__(entityTypeDetails[entity_type_id].messageProp, messageData);

        and this one too as you mentioned in your reply:
        // Call a method to pre-process message data before building the message etc

        1. Admin
          at 10:17 am

          can you point me to i18n library
          >> i18n library:
          Here is an example
          res.__(‘Hello {{name}}’, { name: ‘Marcus’ }); // Hallo Marcus
          and how to build message using :
          i18n.__(entityTypeDetails[entity_type_id].messageProp, messageData);
          >> If you go through the above mentioned library you will understand how the message is built.
          and this one too as you mentioned in your reply:
          // Call a method to pre-process message data before building the message etc

          >> Consider the following example
          entityTypeDetails = {
          1: {
          name: “Say hi”,
          preProcessing: getFullName
          define getFullName method before.
          function getFullName (data) {
          return {
          fullName: data.firstName + ‘ ‘ + data.lastName
          Suppose you get first name and last name from database in the following format:
          originalData = {
          firstName: “Mark”,
          lastName: “Zuckerberg”
          Your notification message should say “Hello Mark Zuckerberg”.
          Then before building the message check if there is any preProcessing property for entity type “1”.
          If yes then your data should be pre processed before building the message.
          processedData = entityTypeDetails[entity_type_id].preProcessing(originalData);
          message = i18n.__(‘Hello {{fullName}}’, processedData);
          You can also do
          message = i18n.__(‘Hello {{firstName}} {{lastName}}’, originalData);

          1. Shantaram
            at 11:28 am

            thanks for the library + such great details of Notification System,

  3. Shantaram
    at 3:48 pm

    One more question what is the reason behind adding status field in 3 tables ?

    1. Admin
      at 10:19 am

      If any user is inactive then all notifications related to that user should not be shown.
      In that case we perform a soft deletion by changing status value to 0.
      1 -> Active
      0 -> Inactive
      All SELECT statement should have status = 1 in the WHERE condition.
      If we perform a hard delete on the tables by deleting the records then we can never have any history.

  4. Kanchan
    at 11:49 am

    Hi , Thanks for the detailed article . I am able to build the notification system which is working quite well. I have a query when a particular comment is deleted how we should go with changes then ?

Leave A Reply

five × 5 =