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.

PROBLEM UNDERSTANDING

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.

NOTIFICATION DATA

For every notification, there are 4 key values:

Actor

Notifier

Entity

Entity type

Actor

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

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

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.

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. Using an entity_id and entity_type_id, we can know what notification has to be generated.

notification_object table

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

entity_type_id

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.

entity_id

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.

Example

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.

notification_object_id

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

actor_id

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.

notification_object_id

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

notifier_id

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.

IMPLEMENTATION DETAILS

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.

Users

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:

Entity type ID

Entity ID

Actor ID

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.

Posts

post_id

post_desc

posted_by
(user_id)

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.

Comments

comment
_id

comment
_desc

commented
_by

post_id

created_on

status

1

“Happy
journey!!”

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

First, insert the notification data in notification_object table.

Get the notification_object_id

Insert in notification_change table.

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

notification_object

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.

notification_change

id

notification_object_id

actor_id

status

1

1

1

1

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

notification

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:

While sending the immediate notification

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.

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

After inserting the notification details into the database, we have the notification_object_id.

Generate the “Notification message” using notification_object_id.

Get the list of notifiers.

Send notification using Amazon SNS.

Importance of notification_object_id

Using notification_object_id, the following details can be known:

The notification message (entity_type_id and entity_id from notification_object).

The actor (actor_id from notification_change) .

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:

Username

Action type

Post description

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

"{{actor_user_name}} {{action_type}} post. {{post_description}}."

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:

"{{actor_user_name}} {{action_type}} post. {{post_description}}."

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

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

user_id

page_size

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.

entity_type_id

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.

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.

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.

PROS AND CONS

Pros

This can be easily extended to support new notifications for new entities. All we have to do is define a new entity_type_id.

All SELECT queries will run on the primary key, which will ensure that things are fast.

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.

Cons

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.

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

Follow

10 thoughts on “Designing a notification system”

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

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

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.

thanks for the reply
can you point me to i18n library, so I can came to know about building message.
e.g.
entityTypeDetails
{
// 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

can you point me to i18n library
>> i18n library: https://github.com/mashpie/i18n-node
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);

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.