DEV Community

Cover image for How To Design a MySQL Database for your Basic Notification System?
Nik L.
Nik L.

Posted on

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

#mysql #javascript #beginners #database

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.

Image description

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;
Enter fullscreen mode Exit fullscreen mode




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)

);
Enter fullscreen mode Exit fullscreen mode




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)

);
Enter fullscreen mode Exit fullscreen mode




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

);
Enter fullscreen mode Exit fullscreen mode




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.

Top comments (1)

Collapse
 
prsaya profile image
Prasad Saya • • Edited

The createdAt and updatedAt columns can be:

createdAt DATETIME 
  NOT NULL DEFAULT (CURRENT_TIMESTAMP()),
updatedAt DATETIME 
  NOT NULL DEFAULT (CURRENT_TIMESTAMP()),
Enter fullscreen mode Exit fullscreen mode

[ EDIT: corrected to CURRENT_TIMESTAMP() from CURDATE()]
instead of

`createdAt` DATETIME 
  NOT NULL,
`updatedAt` DATETIME 
  NULL DEFAULT NULL,
Enter fullscreen mode Exit fullscreen mode

Setting default values for date (date and time) fields in a database is often used, especially in the case of created and updated timestamps. As such TIMESTAMP type is also acceptable instead of DATETIME type.