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
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
The password hash generated by the appropriate algorithm. We must avoid storing plain or en
Hash
The flag to identify whether the user is an administrator. It's not required if RBAC tables are
Admin
database design.
The flag to identify whether the registered user can manage the newsletters and subscribers. I
Customer
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, Read
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,
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 The customer id to identify the customer. It's an optional field and required only if the applicatio
Id customers and their newsletters. Customers can manage their own subscribers.
First Name The first name of the subscriber.
Middle
The middle name of the subscriber.
Name
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 Subsc
Middle Name The middle name used for the address. It can be derived from the corresponding User or Su
Last Name The last name used for the address. It can be derived from the corresponding User or Subsc
Mobile The mobile used for the address. It can be derived from the corresponding User or Subscrib
Email The email used for the address. It can be derived from the corresponding User or Subscribe
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.