How To Design a MySQL Database for your Basic Notification System?
Tags: mysql, database, javascript, beginners
Published: 2024-01-11 07:39:14

TL;DR: Check out a tabular comparison here: [MySQL vs Cassandra vs Elastic Search vs Redis vs Amazon Aurora vs Google cloud spanner](https://www.suprsend.com/post/choosing-the-best-database-cluster-for-your-notification-system-for-high-performance-scalability-and-reliability)

---

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rv5qp3xvmxusq7gorc1r.png)


## 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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.