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:

1. Actor
2. Notifier
3. Entity
4. 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.

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 message
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.

DATABASE MODEL

CREATE STATEMENT

MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

-- Notification object CREATE TABLE IF NOT EXISTS `notification_object` (   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,   `entity_type` INT UNSIGNED NOT NULL,   `entity_id` INT UNSIGNED NOT NULL,   `created_on` DATETIME NOT NULL,   `status` TINYINT NOT NULL,   PRIMARY KEY (`id`)) ENGINE = InnoDB; -- Notification CREATE TABLE IF NOT EXISTS `notification` (   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,   `notification_object_id` INT UNSIGNED NOT NULL,   `notifier_id` INT UNSIGNED NOT NULL,   `status` TINYINT NOT NULL,   PRIMARY KEY (`id`),   INDEX `fk_notification_object_idx` (`notification_object_id` ASC),   INDEX `fk_notification_notifier_id_idx` (`notifier_id` ASC),   CONSTRAINT `fk_notification_object`     FOREIGN KEY (`notification_object_id`)     REFERENCES `notification_object` (`id`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,   CONSTRAINT `fk_notification_notifier_id`     FOREIGN KEY (`notifier_id`)     REFERENCES `user` (`id`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB; -- Notification change CREATE TABLE IF NOT EXISTS `notification_change` (   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,   `notification_object_id` INT UNSIGNED NOT NULL,   `actor_id` INT UNSIGNED NOT NULL,   PRIMARY KEY (`id`),   INDEX `fk_notification_object_idx_2` (`notification_object_id` ASC),   INDEX `fk_notification_actor_id_idx` (`actor_id` ASC),   CONSTRAINT `fk_notification_object_2`     FOREIGN KEY (`notification_object_id`)     REFERENCES `notification_object` (`id`)     ON DELETE NO ACTION     ON UPDATE NO ACTION,   CONSTRAINT `fk_notification_actor_id_idx`     FOREIGN KEY (`actor_id`)     REFERENCES `user` (`id`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB;

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:

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.

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

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

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:

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.

MySQL

1	"{{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:

MySQL

1	"{{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.

MySQL

1 2 3 4 5

SELECT u.user_name , p.post_description FROM posts p INNER JOIN users u ON p.posted_by = u.user_id WHERE p.post_id = :entity_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.

MySQL

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

• 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.

MySQL

1 2 3

SELECT n.notifier_id FROM notification n WHERE n.notification_object_id = :notification_object_id;

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.

MySQL

1 2 3 4 5 6 7

SELECT n_o.id, n_o.entity_id, n_o.entity_type_id, n.notifier_id FROM notification_object no INNER JOIN notification n WHERE n.notifier_id = :user_id LIMIT 0, 10; // Limit {{after_row}}, {{number_of_records}} -> 1 - 10 records LIMIT 10, 10; // For records from 11 - 20 LIMIT 20, 10, // For records from 21 - 30

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

order	notification _object_id	entity_ type_id	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.

MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

[   {     "notification_object_id": 78,     "entity_type_id": 2,     "entity_id": 89,     "created_on": "31-01-2017 09:00:12"   },   {     "notification_object_id": 60,     "entity_type_id": 1,     "entity_id": 45,     "created_on": "24-01-2017 08:25:12"   },   {     "notification_object_id": 45,     "entity_type_id": 2,     "entity_id": 56     "created_on": "23-01-2017 11:05:35"   },   {     "notification_object_id": 3,     "entity_type_id": 2,     "entity_id": 3     "created_on": "21-01-2017 12:45:55"   },   {     "notification_object_id": 1,     "entity_type_id": 1,     "entity_id": 23,     "created_on": "20-01-2017 17:52:01"   } ]

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.

MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

{   "1": [     {       "entity_id": 45,       "notification_object_id": 60     },     {       "entity_id": 23,       "notification_object_id": 1     }   ],   "2": [     {       "entity_id": 83,       "notification_object_id": 78     },     {       "entity_id": 56,       "notification_object_id": 45     },     {       "entity_id": 3,       "notification_object_id": 3     }   ] }

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.

MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

[   {     "entity_id": 45,     "entity_type_id": 1,     "notification_object_id": 78,     "message": "Nancy added a post. Travelling to Mumbai.",     "created_on": "31-01-2017 09:00:12"   },   {     "entity_id": 23,     "entity_type_id": 1,     "notification_object_id": 60,     "message": "Linda added a post. Playing cricket.",     "created_on": "24-01-2017 08:25:12"   },   {     "entity_id": 89,     "entity_type_id": 2,     "notification_object_id": 45,     "message": "Nancy added a comment. All the best.",     "created_on": "23-01-2017 11:05:35"   },   {     "entity_id": 56,     "entity_type_id": 2,     "notification_object_id": 3,     "message": "Scott added a comment. All the best.",     "created_on": "21-01-2017 12:45:55"   },   {     "entity_id": 3,     "entity_type_id": 2,     "notification_object_id": 1,     "message": "Nancy added a comment. How is life?",     "created_on": "20-01-2017 17:52:01"   } ]

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.

MySQL

1 2 3 4 5 6 7

SELECT n_o.id, n_o.entity_id, n_o.entity_type_id, n_c.actor_id FROM notification_object no INNER JOIN notification_change n_c WHERE n_c.notifier_id = :user_id LIMIT 0, 10; // Limit {{after_row}}, {{number_of_records}} -> 1 - 10 records LIMIT 10, 10; // For records from 11 - 20 LIMIT 20, 10, // For records from 21 - 30

PROS AND CONS

Pros

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.

Cons

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.