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.