Guide To Design Database For Newsletter In MySQL

Guide To Design Database For Newsletter In MySQL

A complete guide to designing a database in MySQL for newsletter system and management. It shows how to design a database for Newsletter System.

July 11, 2020

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

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

Write a Comment
Click the captcha image to get new code.
Discussion Forum by DISQUS