This tutorial provides the complete steps to design a database schema of the Newsletter System to manage the users, newsletters, subscribers, and mailing lists. It can be further enhanced and used to develop an email-based marketing platform to provide Newsletter services. The same database architecture or schema can be used as a reference to manage online newsletters or to distribute the hard copies of the newsletters and magazines. It can also be used by digital marketing agencies to manage their leads and marketing campaigns.
The Entity Relationship Diagram or visual database design is shown below.
Newsletter Database Design
Notes: The database can be further enhanced by adding the Role-Based Access Control (RBAC) tables. The security can be handled by following RBAC Database in MySql. Also, it does not include the tables required for customer billing. You may refer to the Online Shopping Cart Database in MySQL to derive the tables required to manage the orders.
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.
Newsletter Database
The very first step is to create the Newsletter Database. It can be created using the query as shown below.
CREATE SCHEMA `newsletter` 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. The same table can be used to manage different types of users including admins and customers. It can also be used to relate to the newsletter managers. Users can track their own newsletters and mailing lists. 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. |
| Admin | The flag to identify whether the user is an administrator. It's not required if RBAC tables are created by following the RBAC database design. |
| Customer | The flag to identify whether the registered user can manage the newsletters and subscribers. It's not required if RBAC tables are created by following the RBAC database design. |
| 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 | Customer details. |
The User Table with the appropriate constraints is shown below.
CREATE TABLE `newsletter`.`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,
`admin` TINYINT(1) NOT NULL DEFAULT 0,
`customer` TINYINT(1) NOT NULL DEFAULT 0,
`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) );
Newsletter Table
In this section, we will design the Newsletter Table to store the newsletter data. Below mentioned is the description of all the columns of the Newsletter Table.
| Id | The unique id to identify the newsletter. |
| User Id | The user id to identify the admin or customer. |
| Title | The newsletter title to identify the newsletter. |
| Description | The newsletter description. |
| Type | The type to distinguish between the different newsletter types. |
| Multiple | The flag to mark whether the Newsletter will be sent once or multiple times. |
| Global | The flag to mark whether the Newsletter will be sent to all the subscribers. |
| Status | It can be used to identify the status. The possible status of the newsletter includes New, Ready, Published. |
| Created At | It stores the date and time at which the newsletter is created. |
| Updated At | It stores the date and time at which the newsletter is updated. |
| Published At | It stores the date and time at which the newsletter is published. |
| Content | The column used to store the newsletter content if the multiple flag is set to false. |
It uses the column multiple to identify whether the Newsletter is planned to send only once or multiple times. The Newsletter content can be stored in the content column in case it's planned to send only once. In case the multiple flag is set to true, the edition table must be used to store the content of each edition. The Newsletter Table with the appropriate constraints is as shown below.
CREATE TABLE `newsletter`.`newsletter` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`userId` BIGINT NOT NULL,
`title` VARCHAR(75) NOT NULL,
`descritpion` VARCHAR(2048) NULL DEFAULT NULL,
`type` SMALLINT(6) NOT NULL DEFAULT 0,
`multiple` TINYINT(1) NOT NULL DEFAULT 0,
`global` TINYINT(1) NOT NULL DEFAULT 0,
`status` SMALLINT(6) NOT NULL DEFAULT 0,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
`publishedAt` DATETIME NULL DEFAULT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_newsletter_user` (`userId` ASC),
CONSTRAINT `fk_newsletter_user`
FOREIGN KEY (`userId`)
REFERENCES `newsletter`.`user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
Newsletter Meta
The Newsletter Meta Table can be used to store additional information about newsletters including the newsletter banner URL etc. Below mentioned is the description of all the columns of the Newsletter Meta Table.
| Id | The unique id to identify the newsletter meta. |
| Newsletter Id | The newsletter id to identify the parent newsletter. |
| Type | The type to categorize the metadata. |
| Key | The key identifying the meta. |
| Content | The column used to store the newsletter metadata. |
The Newsletter Meta Table with the appropriate constraints is as shown below.
CREATE TABLE `newsletter`.`newsletter_meta` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`newsletterId` BIGINT NOT NULL,
`type` VARCHAR(50) NOT NULL,
`key` VARCHAR(160) NOT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_meta_newsletter` (`newsletterId` ASC),
UNIQUE INDEX `uq_pnewsletter_meta` (`newsletterId` ASC, `key` ASC),
CONSTRAINT `fk_meta_newsletter`
FOREIGN KEY (`newsletterId`)
REFERENCES `newsletter`.`newsletter` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Edition Table
In this section, we will design the Edition Table to store the newsletter editions required for newsletters with multiple flag set to true. Below mentioned is the description of all the columns of the Edition Table.
| Id | The unique id to identify the edition. |
| Newsletter Id | The newsletter id to identify the parent newsletter. |
| Title | The edition title. |
| Description | The edition description. |
| Status | It can be used to identify the status. The possible status of the edition includes New, Ready, Published. |
| Created At | It stores the date and time at which the edition was created. |
| Updated At | It stores the date and time at which the edition was updated. |
| Published At | It stores the date and time at which the edition was published. |
| Content | The column used to store the edition content. |
The Edition Table with the appropriate constraints is as shown below.
CREATE TABLE `newsletter`.`edition` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`newsletterId` BIGINT NOT NULL,
`title` VARCHAR(100) NOT NULL,
`description` VARCHAR(2048) NULL DEFAULT NULL,
`status` SMALLINT(6) NOT NULL DEFAULT 0,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
`publishedAt` DATETIME NULL DEFAULT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_edition_newsletter` (`newsletterId` ASC),
CONSTRAINT `fk_edition_newsletter`
FOREIGN KEY (`newsletterId`)
REFERENCES `newsletter`.`newsletter` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
Subscriber Table
In this section, we will design the Subscriber Table to store the subscriber details. The subscriber table can be used to directly trigger global newsletters. Below mentioned is the description of all the columns of the Subscriber Table.
| Id | The unique id to identify the subscriber. |
| Customer Id | The customer id to identify the customer. It's an optional field and required only if the application is designed to manage the customers and their newsletters. Customers can manage their own subscribers. |
| First Name | The first name of the subscriber. |
| Middle Name | The middle name of the subscriber. |
| Last Name | The last name of the subscriber. |
| The email of the subscriber. | |
| Mobile | The mobile number of the subscriber. |
| Phone | The phone number of the subscriber. |
| Active | The flag to identify whether the subscriber is active. |
| Created At | It stores the date and time at which the subscriber is registered. |
| Updated At | It stores the date and time at which the subscriber is updated. |
The Subscriber Table with the appropriate constraints is as shown below.
CREATE TABLE `newsletter`.`subscriber` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`customerId` BIGINT DEFAULT NULL,
`firstName` VARCHAR(100) NOT NULL,
`middleName` VARCHAR(100) NULL DEFAULT NULL,
`lastName` VARCHAR(100) NULL DEFAULT NULL,
`email` VARCHAR(100) NOT NULL,
`mobile` VARCHAR(50) NULL DEFAULT NULL,
`phone` VARCHAR(50) NULL DEFAULT NULL,
`active` TINYINT(1) NOT NULL DEFAULT 1,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_subscriber_customer` (`customerId` ASC),
CONSTRAINT `fk_subscriber_customer`
FOREIGN KEY (`customerId`)
REFERENCES `newsletter`.`user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
ALTER TABLE `newsletter`.`subscriber` ADD UNIQUE `uq_sub_cust_email`(`customerId`, `email`);
Address Table
In this section, we will design the Address Table to store the customer and subscriber address. The address can be used for the physical delivery of the Newsletter. Below mentioned is the description of all the columns of the Address Table.
| Id | The unique id to identify the address. |
| User Id | The user id to identify the user associated with the address. |
| Subscriber Id | The subscriber id to identify the subscriber associated with the address. |
| First Name | The first name used for the address. It can be derived from the corresponding User or Subscriber. |
| Middle Name | The middle name used for the address. It can be derived from the corresponding User or Subscriber. |
| Last Name | The last name used for the address. It can be derived from the corresponding User or Subscriber. |
| Mobile | The mobile used for the address. It can be derived from the corresponding User or Subscriber. |
| The email used for the address. It can be derived from the corresponding User or Subscriber. | |
| Line 1 | The first line to store address. |
| Line 2 | The second line to store address. |
| City | The city of the address. |
| Province | The province of the address. |
| Country | The country of the address. |
| Area Code | The area code to identify the delivery area. |
| Created At | It stores the date and time at which the address is created. |
| Updated At | It stores the date and time at which the address is updated. |
The Address Table with the appropriate constraints is as shown below.
CREATE TABLE `newsletter`.`address` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`userId` BIGINT NULL DEFAULT NULL,
`subscriberId` BIGINT NULL DEFAULT NULL,
`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,
`line1` VARCHAR(50) NULL DEFAULT NULL,
`line2` VARCHAR(50) NULL DEFAULT NULL,
`city` VARCHAR(50) NULL DEFAULT NULL,
`province` VARCHAR(50) NULL DEFAULT NULL,
`country` VARCHAR(50) NULL DEFAULT NULL,
`areaCode` VARCHAR(50) NULL DEFAULT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_address_user` (`userId` ASC),
CONSTRAINT `fk_address_user`
FOREIGN KEY (`userId`)
REFERENCES `newsletter`.`user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
ALTER TABLE `newsletter`.`address`
ADD INDEX `idx_address_subscriber` (`subscriberId` ASC);
ALTER TABLE `newsletter`.`address`
ADD CONSTRAINT `fk_address_subscriber`
FOREIGN KEY (`subscriberId`)
REFERENCES `newsletter`.`subscriber` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Mailing List Table
In this section, we will design the Mailing List Table to store the mailing lists of specific newsletters. The mailing list can be used to trigger the non-global newsletters. The subscriber table can be used to trigger global newsletters. Below mentioned is the description of all the columns of the Mailing List Table.
| Id | The unique id to identify the newsletter subscription. |
| Newsletter Id | The newsletter id to identify the newsletter associated with the newsletter subscription. |
| Subscriber Id | The subscriber id to identify the subscriber associated with the newsletter subscription. |
| Active | The flag to identify whether the newsletter subscription is active. |
| Created At | It stores the date and time at which the subscription is created. |
| Updated At | It stores the date and time at which the subscription is updated. |
The Mailing List Table with the appropriate constraints is as shown below.
CREATE TABLE `newsletter`.`mailing_list` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`newsletterId` BIGINT NOT NULL,
`subscriberId` BIGINT NOT NULL,
`active` TINYINT(1) NOT NULL DEFAULT 1,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_mlist_newsletter` (`newsletterId` ASC),
CONSTRAINT `fk_mlist_newsletter`
FOREIGN KEY (`newsletterId`)
REFERENCES `newsletter`.`newsletter` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
ALTER TABLE `newsletter`.`mailing_list`
ADD INDEX `idx_mlist_subscriber` (`subscriberId` ASC);
ALTER TABLE `newsletter`.`mailing_list`
ADD CONSTRAINT `fk_mlist_subscriber`
FOREIGN KEY (`subscriberId`)
REFERENCES `newsletter`.`subscriber` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Newsletter Trigger Table
We also need a table to track Newsletter delivery. This section provides the table and columns required to track the newsletter delivery to the subscriber. Below mentioned is the description of all the columns of the Newsletter Trigger Table.
| Id | The unique id to identify the newsletter trigger. |
| Newsletter Id | The newsletter id to identify the newsletter associated with the trigger. |
| Edition Id | The edition id to identify the newsletter edition associated with the trigger. |
| Subscriber Id | The subscriber id to identify the subscriber associated with the trigger. |
| Sent | The flag to check whether the newsletter has been sent to the subscriber. |
| Delivered | The flag to check whether the newsletter has been delivered to the subscriber. |
| Mode | The mode of newsletter delivery can be either Online or Offline. |
| Created At | It stores the date and time at which the trigger is created. |
| Updated At | It stores the date and time at which the trigger is updated. |
| Sent At | It stores the date and time at which the trigger was processed. |
| Delivered At | It stores the date and time at which the newsletter was delivered. |
The Newsletter Trigger Table with the appropriate constraints is as shown below.
CREATE TABLE `newsletter`.`newsletter_trigger` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`newsletterId` BIGINT NOT NULL,
`editionId` BIGINT NULL DEFAULT NULL,
`subscriberId` BIGINT NOT NULL,
`sent` TINYINT(1) NOT NULL DEFAULT 1,
`delivered` TINYINT(1) NOT NULL DEFAULT 1,
`mode` SMALLINT(6) NOT NULL DEFAULT 0,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
`sentAt` DATETIME NULL DEFAULT NULL,
`deliveredAt` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_trigger_newsletter` (`newsletterId` ASC),
CONSTRAINT `fk_trigger_newsletter`
FOREIGN KEY (`newsletterId`)
REFERENCES `newsletter`.`newsletter` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
ALTER TABLE `newsletter`.`newsletter_trigger`
ADD INDEX `idx_trigger_edition` (`editionId` ASC);
ALTER TABLE `newsletter`.`newsletter_trigger`
ADD CONSTRAINT `fk_trigger_edition`
FOREIGN KEY (`editionId`)
REFERENCES `newsletter`.`edition` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
ALTER TABLE `newsletter`.`newsletter_trigger`
ADD INDEX `idx_trigger_subscriber` (`subscriberId` ASC);
ALTER TABLE `newsletter`.`newsletter_trigger`
ADD CONSTRAINT `fk_trigger_subscriber`
FOREIGN KEY (`subscriberId`)
REFERENCES `newsletter`.`subscriber` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Summary
In this tutorial, we have discussed the database design of a Newsletter System to store the users and manage the newsletters. It also provided the database design to manage the subscribers and mailing lists.
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.
