Guide To Design Database For Notifications In MySQL
A complete guide to designing a database in MySQL for a simplified notification system. It shows how to design the database to store user notifications.
This tutorial provides the complete steps to design a database schema of the Notification System to manage the user notifications. It can be further enhanced and used to manage the notifications of other entities apart from the system user.
The Entity Relationship Diagram or visual database design is shown below.
Notification Management Database
You can also visit the popular tutorials including How To Install MySQL 8 on Ubuntu 20.04 LTS, How To Install MySQL 8 on Windows, How To Install MySQL Workbench On Ubuntu, How To Install MySQL 8 With Workbench On Windows 10, RBAC Database in MySql, Blog Database in MySql, Quiz Database in MySQL, Poll & Survey Database in MySQL, Online Shopping Cart Database in MySQL, and Learn Basic SQL Queries In MySQL.
Notification Database
The very first step is to create the Notification Database. It can be created using the query as shown below.
CREATE SCHEMA `notification` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
I have used the character set utf8mb4 to support a wide range of characters.
User Table
In this section, we will design the User Table to store user information. Users can manage their own notifications. Below mentioned is the description of all the columns of the User Table.
| Id | The unique id to identify the user. |
| First Name | The first name of the user. |
| Middle Name | The middle name of the user. |
| Last Name | The last name of the user. |
| Mobile | The mobile number of the user. It can be used for login and registration purposes. |
| The email of the user. It can be used for login and registration purposes. | |
| Password Hash | The password hash generated by the appropriate algorithm. We must avoid storing plain or encrypted passwords. |
| Registered At | This column can be used to calculate the life of the user with the application. |
| Last Login | It can be used to identify the last login of the user. |
| Intro | The brief introduction of the User. |
| Profile | User details. |
The User Table with the appropriate constraints is shown below.
CREATE TABLE `notification`.`user` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`firstName` VARCHAR(50) NULL DEFAULT NULL,
`middleName` VARCHAR(50) NULL DEFAULT NULL,
`lastName` VARCHAR(50) NULL DEFAULT NULL,
`username` VARCHAR(50) NULL DEFAULT NULL,
`mobile` VARCHAR(15) NULL,
`email` VARCHAR(50) NULL,
`passwordHash` VARCHAR(32) NOT NULL,
`registeredAt` DATETIME NOT NULL,
`lastLogin` DATETIME NULL DEFAULT NULL,
`intro` TINYTEXT NULL DEFAULT NULL,
`profile` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `uq_username` (`username` ASC),
UNIQUE INDEX `uq_mobile` (`mobile` ASC),
UNIQUE INDEX `uq_email` (`email` ASC) );
Notification Template Table
In this section, we will design the Notification Template Table used to generate the notification content. The application can further use the appropriate template system to parse the template to generate the notification content. Below mentioned is the description of all the columns of the Notification Template Table.
| Id | The unique id to identify the notification template. |
| Title | The template title. |
| Description | The template description. |
| Type | The type to classify the templates. |
| Source Type | The source type to classify the templates according to the source type. |
| Created At | It stores the date and time at which the template was created. |
| Updated At | It stores the date and time at which the template was updated. |
| Content | The column used to store the template content. |
The Notification Template Table with the appropriate constraints is shown below.
CREATE TABLE `notification`.`notification_template` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`description` VARCHAR(2048) NULL DEFAULT NULL,
`type` SMALLINT(6) NOT NULL DEFAULT 0,
`sourceType` VARCHAR(50) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`) );
Notification Table
In this section, we will design the Notification Table to store the notifications triggered by the application. The notification can be triggered using the template of a specific type and source type. The type and source type of the notification will be the same as that of the template used to generate the notification content. Below mentioned is the description of all the columns of the Notification Table.
| Id | The unique id to identify the notification. |
| User Id | The user id to identify the user associated with the notification. |
| Source Id | The source id to identify the entity associated with the notification. |
| Source Type | The source type to identify the entity associated with the notification. It will be the same as the template source type. |
| Type | The type to classify the notifications. It will be the same as the template type. |
| Read | The flag to mark the notification as read/unread. |
| Trash | The flag to mark the notification as trash. |
| Created At | It stores the date and time at which the notification is created. |
| Updated At | It stores the date and time at which the notification is updated. |
| Content | The notification content generated using the corresponding template. |
The Notification Table with the appropriate constraints is as shown below.
CREATE TABLE `notification`.`notification` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`userId` BIGINT NOT NULL,
`sourceId` BIGINT NOT NULL,
`sourceType` VARCHAR(50) NOT NULL,
`type` SMALLINT(6) NOT NULL DEFAULT 0,
`read` TINYINT(1) NOT NULL DEFAULT 1,
`trash` TINYINT(1) NOT NULL DEFAULT 1,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_notification_user` (`userId` ASC),
CONSTRAINT `fk_notification_user`
FOREIGN KEY (`userId`)
REFERENCES `notification`.`user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
Summary
In this tutorial, we have discussed the database design of a Notifications System to store the users and manage their notifications. It also includes the Notification Template Table required to generate the notifications.
You may submit your comments to join the discussion. You may also be interested in designing the database of the Blog and Poll & Survey applications. The complete database schema is also available on GitHub.
