Guide To Design Database For Calendar Event And Reminder In MySQL

By bhagwatchouhan
Guide To Design Database For Calendar Event And Reminder In MySQL

This tutorial provides the complete steps to design a database schema of the Calendar Event and Reminder System to manage the calendar events of the users and also store the reminders. It can be further enhanced and used to manage the events and reminders of other entities apart from the system user.

 

The Entity Relationship Diagram or visual database design is shown below.

 

Calendar Event and Reminder Database Design

Calendar Event and Reminder Database Design

 

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.

 

Calendar Database

 

The very first step is to create the Calendar Database. It can be created using the query as shown below.

 

CREATE SCHEMA `calendar` 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. Users can manage their own events and reminders. Below mentioned is the description of all the columns of the User Table.

 

IdThe unique id to identify the user.
First NameThe first name of the user.
Middle NameThe middle name of the user.
Last NameThe last name of the user.
MobileThe mobile number of the user. It can be used for login and registration purposes.
EmailThe email of the user. It can be used for login and registration purposes.
Password HashThe password hash generated by the appropriate algorithm. We must avoid storing plain or encrypted passwords.
Registered AtThis column can be used to calculate the life of the user with the application.
Last LoginIt can be used to identify the last login of the user.
IntroThe brief introduction of the User.
ProfileUser details.

 

The User Table with the appropriate constraints is shown below.

 

CREATE TABLE `calendar`.`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,
`username` 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_username` (`username` ASC),
UNIQUE INDEX `uq_mobile` (`mobile` ASC),
UNIQUE INDEX `uq_email` (`email` ASC) );

 

Event Table

 

In this section, we will design the Event Table to store the user events and the events triggered by the application. Below mentioned is the description of all the columns of the Event Table.

 

IdThe unique id to identify the event.
User IdThe user id to identify the corresponding user.
Source IdThe source id to identify the corresponding entity.
Source TypeThe source type to distinguish the corresponding entity among other entities.
TitleThe event title.
DescriptionThe event description to store the key highlights of the event.
TypeThe type to distinguish between the different event types.
URLThe URL to redirect the user to a specific link associated with the event.
ActiveFlag to identify whether the event is active and eligible to be displayed on the calendar.
SystemFlag to identify whether the event is generated by the application. The application events will always be eligible to be displayed on the calendar.
Reminder CountThe maximum number of reminders that can be triggered for the event.
Reminder IntervalThe reminder interval.
Reminder UnitThe reminder unit to identify the reminder interval in minutes, hours, or days.
Created AtIt stores the date and time at which the event is created.
Updated AtIt stores the date and time at which the event is updated.
Scheduled AtIt stores the date and time on the Calendar.
Triggered AtIt stores the date and time at which the event was triggered.
ContentThe column used to store the event content.

 

It uses the column Active to identify whether the user-generated event can be displayed on the calendar. The System flag can be used to mark the application-generated events. The application-generated events can always be displayed on the calendar. The columns Source Id and Source Type can be used to identify the other tables or entities associated with the event. The column Triggered At stores the date and time at which the event was last triggered. The Event Table with the appropriate constraints is shown below.

 

CREATE TABLE `calendar`.`event` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`userId` BIGINT NOT NULL,
`sourceId` BIGINT,
`sourceType` VARCHAR(50) NULL DEFAULT NULL,
`title` VARCHAR(1024) NOT NULL,
`descritpion` VARCHAR(2048) NULL DEFAULT NULL,
`type` SMALLINT(6) NOT NULL DEFAULT 0,
`url` VARCHAR(1024) NULL DEFAULT NULL,
`active` TINYINT(1) NOT NULL DEFAULT 0,
`system` TINYINT(1) NOT NULL DEFAULT 0,
`reminderCount` SMALLINT(6) NOT NULL DEFAULT 0,
`reminderInterval` SMALLINT(6) NOT NULL DEFAULT 0,
`reminderUnit` SMALLINT(6) NOT NULL DEFAULT 0,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
`scheduledAt` DATETIME NULL DEFAULT NULL,
`triggeredAt` DATETIME NULL DEFAULT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_event_user` (`userId` ASC),
CONSTRAINT `fk_event_user`
FOREIGN KEY (`userId`)
REFERENCES `calendar`.`user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

 

Event Template Table

 

In this section, we will design the Event Template Table used to generate the event content. The application can further use the appropriate template system to parse the template to generate the event content. Below mentioned is the description of all the columns of the Event Template Table.

 

IdThe unique id to identify the event template.
TitleThe template title.
DescriptionThe template description.
TypeThe type to classify the templates.
Source TypeThe source type to classify the templates according to the source type.
Created AtIt stores the date and time at which the template was created.
Updated AtIt stores the date and time at which the template was updated.
ContentThe column used to store the template content.

 

The Event Template Table with the appropriate constraints is as shown below.

 

CREATE TABLE `calendar`.`event_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) NULL DEFAULT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NULL DEFAULT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`) );

 

Reminder Table

 

We also need a table to store the reminders triggered by active or system events. This section provides the table and columns required to manage the reminders. Below mentioned is the description of all the columns of the Reminder Table.

 

IdThe unique id to identify the reminder.
Event IdThe event id to identify the event associated with the reminder.
User IdThe user id to identify the user associated with the reminder.
ReadThe flag to mark the reminder as read/unread.
TrashThe flag to mark the reminder as trash.
Created AtIt stores the date and time at which the reminder is created.
Updated AtIt stores the date and time at which the reminder is updated.
ContentThe reminder message.

 

The Reminder Table with the appropriate constraints is as shown below.

 

CREATE TABLE `calendar`.`reminder` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`eventId` BIGINT NOT NULL,
`userId` BIGINT NOT NULL,
`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_reminder_event` (`eventId` ASC),
CONSTRAINT `fk_reminder_event`
FOREIGN KEY (`eventId`)
REFERENCES `calendar`.`event` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

ALTER TABLE `calendar`.`reminder`
ADD INDEX `idx_reminder_user` (`userId` ASC);
ALTER TABLE `calendar`.`reminder`
ADD CONSTRAINT `fk_reminder_user`
FOREIGN KEY (`userId`)
REFERENCES `calendar`.`user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

 

Enhancements

 

We can also manage group events using the same database schema. It can be done by adding the group flag to the event table and a new table is required to manage the participants of the group event.

 

Summary

 

In this tutorial, we have discussed the database design of a Calendar System to store user events and reminders. It also provided the database design to manage the user events triggered by the systems or application.

 

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.

share on :

Profile picture for user bhagwatchouhan
bhagwatchouhan