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.
| 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
-- 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:
- 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.
To send a notification, you can use Amazon Simple Notification Service.
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.
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.
"{{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.
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
- 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.
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.
[
{
"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.
{
"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.
[
{
"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.
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
- This is easy to implement.
- 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
Osmosee's Blog 