How To Design a MySQL Database for your Basic Notification System?

TL;DR: Check out a tabular comparison here: MySQL vs Cassandra vs Elastic Search vs Redis vs Amazon Aurora vs Google cloud spanner

Managing user notifications efficiently is crucial for any application. This comprehensive guide provides step-by-step instructions on designing a MySQL database for a notification system. The schema can be extended to handle notifications for various entities beyond users.

Notification Database Creation

The initial step involves creating the Notification Database using the following SQL query, utilizing the UTF8MB4 character set for broader character support.

CREATE SCHEMA `notification` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

User Table Design

The User Table stores essential user information, allowing users to manage their notifications. Below are the details of each column in the User Table:

• Id: Unique identifier for the user.
• First Name, Middle Name, Last Name: User's name components.
• Mobile: User's mobile number for login and registration.
• Email: User's email for login and registration.
• Password Hash: Securely stored password hash.
• Registered At: Timestamp indicating user registration time.
• Last Login: Timestamp indicating the user's last login.
• Intro: Brief introduction of the user.
• Profile: Detailed user profile.

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,
  `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_mobile` (`mobile` ASC),
  UNIQUE INDEX `uq_email` (`email` ASC)
);

Notification Template Table Design

The Notification Template Table is crucial for generating notification content. It includes the following columns:

• Id: Unique identifier for the notification template.
• Title, Description: Template title and description.
• Type: Classification of templates.
• Source Type: Classification based on the source type.
• Created At, Updated At: Timestamps for template creation and updates.
• Content: Storage for template content.

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 Design

The Notification Table stores triggered notifications, associated with specific users and entities. It contains the following columns:

• Id: Unique identifier for the notification.
• User Id, Source Id: Identifiers for the associated user and entity.
• Source Type, Type: Classifications corresponding to the template.
• Read, Trash: Flags indicating read/unread and trash status.
• Created At, Updated At: Timestamps for notification creation and updates.
• Content: Content generated using the template.

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

This tutorial comprehensively covers the design of a Notification System database in MySQL. It includes the User Table, Notification Template Table, and Notification Table, providing a robust foundation for managing user notifications.