Guide To Design Database For Task Manager In MySQL

A complete guide to designing a database in MySQL for task manager or task management to manage tasks and activities.

This tutorial provides the complete steps to design a database schema of the Task Manager to manage the tasks, activities, and comments of the application users. The Task Manager application built using this database design can be used to store the tasks associated with the users. The same application can be used to manage the task activities and comments or notes.

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

Task Management 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, Notifications Database In MySQL, Calendar Event And Reminder Database In MySQL, Blog Database in MySql, Quiz Database in MySQL, Poll & Survey Database in MySQL, Online Shopping Cart Database in MySQL, Inventory Database in MySQL, and Learn Basic SQL Queries In MySQL.

Task Manager Database

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

CREATE SCHEMA `task_manager` 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 profiles. Also, the users can use the application to manage their own tasks and activities. Below mentioned is the description of all the columns of the User Table.

Id	The unique id to identify the user.
Role Id	The role of the user. It can be Admin or 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.
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	A brief introduction of the User.
Profile	User details.

The User Table with the appropriate constraints is shown below.

CREATE TABLE `task_manager`.`user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `roleId` SMALLINT NOT NULL,
  `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) );

Task Table

In this section, we will design the Task Table to store the tasks. Below mentioned is the description of all the columns of the Task Table.

Id	The unique id to identify the task.
User Id	The user id to identify the corresponding user.
Created By	The user id to identify the user who added the task.
Updated By	The user id to identify the user who updated the task.
Title	The title of the Task.
Description	The description of the Task.
Status	The status of the task can be New, In-Progress, or Completed.
Hours	The total hours consumed by the Task. It can either be manually filled or updated on activity completion.
Created At	It stores the date and time at which the task is created.
Updated At	It stores the date and time at which the task is updated.
Planned Start Date	It stores the date and time at which the task is planned to start.
Planned End Date	It stores the date and time at which the task is planned to end.
Actual Start Date	It stores the actual date and time at which the task started.
Actual End Date	It stores the actual date and time at which the task finished.
Content	The column used to store the task details.

It uses the column status to track the status of the task. The status can be either New, In-Progress, or Completed. Also, the user can manually fill the hours in case no activity is planned for the task. The task can also derive its actual hours from the activities associated with it. The Task Table with the appropriate constraints is as shown below.

CREATE TABLE `task_manager`.`task` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `userId` BIGINT NOT NULL,
  `createdBy` BIGINT NOT NULL,
  `updatedBy` BIGINT NOT NULL,
  `title` VARCHAR(512) NOT NULL,
  `description` VARCHAR(2048) DEFAULT NULL,
  `status` SMALLINT NOT NULL DEFAULT 0,
  `hours` FLOAT NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `plannedStartDate` DATETIME NULL DEFAULT NULL,
  `plannedEndDate` DATETIME NULL DEFAULT NULL,
  `actualStartDate` DATETIME NULL DEFAULT NULL,
  `actualEndDate` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_task_user` (`userId` ASC),
  CONSTRAINT `fk_task_user`
    FOREIGN KEY (`userId`)
    REFERENCES `task_manager`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `task_manager`.`task` 
ADD INDEX `idx_task_creator` (`createdBy` ASC);
ALTER TABLE `task_manager`.`task` 
ADD CONSTRAINT `fk_task_creator`
  FOREIGN KEY (`createdBy`)
  REFERENCES `task_manager`.`user` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `task_manager`.`task` 
ADD INDEX `idx_task_modifier` (`updatedBy` ASC);
ALTER TABLE `task_manager`.`task` 
ADD CONSTRAINT `fk_task_modifier`
  FOREIGN KEY (`updatedBy`)
  REFERENCES `task_manager`.`user` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Task Meta

The Task Meta Table can be used to store additional information about tasks. Below mentioned is the description of all the columns of the Task Meta Table.

Id	The unique id to identify the task meta.
Task Id	The task id to identify the parent task.
Key	The key identifying the meta.
Content	The column used to store the task metadata.

The Task Meta Table with the appropriate constraints is as shown below.

CREATE TABLE `task_manager`.`task_meta` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `taskId` BIGINT NOT NULL,
  `key` VARCHAR(50) NOT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_meta_task` (`taskId` ASC),
  UNIQUE INDEX `uq_task_meta` (`taskId` ASC, `key` ASC),
  CONSTRAINT `fk_meta_task`
    FOREIGN KEY (`taskId`)
    REFERENCES `task_manager`.`task` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Tag Table and Task Tag Table

In this section, we will design the Tag Table and Task Tag Table to store the task tags and their mappings. Below mentioned is the description of all the columns of the Tag Table.

Id	The unique id to identify the tag.
Title	The tag title.
Slug	The tag slug to form the URL.

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

CREATE TABLE `task_manager`.`tag` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(75) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`));

Below mentioned is the description of all the columns of the Task Tag Table.

Task Id	The task id to identify the task.
Tag Id	The tag id to identify the tag.

The Task Tag Table with the appropriate constraints is as shown below.

CREATE TABLE `task_manager`.`task_tag` (
  `taskId` BIGINT NOT NULL,
  `tagId` BIGINT NOT NULL,
  PRIMARY KEY (`taskId`, `tagId`),
  INDEX `idx_tt_task` (`taskId` ASC),
  INDEX `idx_tt_tag` (`tagId` ASC),
  CONSTRAINT `fk_tt_task`
    FOREIGN KEY (`taskId`)
    REFERENCES `task_manager`.`task` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tt_tag`
    FOREIGN KEY (`tagId`)
    REFERENCES `task_manager`.`tag` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Activity Table

In this section, we will design the Activity Table to store the task activities required to complete the task. Below mentioned is the description of all the columns of the Activity Table.

Id	The unique id to identify the activity.
User Id	The user id to identify the corresponding user.
Task Id	The task id to identify the corresponding task.
Created By	The user id to identify the user who added the task.
Updated By	The user id to identify the user who updated the task.
Title	The title of the Task.
Description	The description of the Task.
Status	The status of the activity can be New, In-Progress, or Completed.
Hours	The total hours consumed by the Activity. The corresponding task hours can be updated on the completion of the activity.
Created At	It stores the date and time at which the activity is created.
Updated At	It stores the date and time at which the activity is updated.
Planned Start Date	It stores the date and time at which the activity is planned to start.
Planned End Date	It stores the date and time at which the activity is planned to end.
Actual Start Date	It stores the actual date and time at which the activity started.
Actual End Date	It stores the actual date and time at which the activity finished.
Content	The column used to store the activity details.

It uses the column status to track the status of the activity. The status can be either New, In-Progress, or Completed. Also, the user can manually fill in the activity hours. The task can derive its actual hours from the activities associated with it. The Activity Table with the appropriate constraints is as shown below.

CREATE TABLE `task_manager`.`activity` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `userId` BIGINT NOT NULL,
  `taskId` BIGINT NOT NULL,
  `createdBy` BIGINT NOT NULL,
  `updatedBy` BIGINT NOT NULL,
  `title` VARCHAR(512) NOT NULL,
  `description` VARCHAR(2048) DEFAULT NULL,
  `status` SMALLINT NOT NULL DEFAULT 0,
  `hours` FLOAT NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `plannedStartDate` DATETIME NULL DEFAULT NULL,
  `plannedEndDate` DATETIME NULL DEFAULT NULL,
  `actualStartDate` DATETIME NULL DEFAULT NULL,
  `actualEndDate` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_activity_user` (`userId` ASC),
  CONSTRAINT `fk_activity_user`
    FOREIGN KEY (`userId`)
    REFERENCES `task_manager`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `task_manager`.`activity` 
ADD INDEX `idx_activity_task` (`taskId` ASC);
ALTER TABLE `task_manager`.`activity` 
ADD CONSTRAINT `fk_activity_task`
  FOREIGN KEY (`taskId`)
  REFERENCES `task_manager`.`task` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `task_manager`.`activity` 
ADD INDEX `idx_activity_creator` (`createdBy` ASC);
ALTER TABLE `task_manager`.`activity` 
ADD CONSTRAINT `fk_activity_creator`
  FOREIGN KEY (`createdBy`)
  REFERENCES `task_manager`.`user` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

ALTER TABLE `task_manager`.`activity` 
ADD INDEX `idx_activity_modifier` (`updatedBy` ASC);
ALTER TABLE `task_manager`.`activity` 
ADD CONSTRAINT `fk_activity_modifier`
  FOREIGN KEY (`updatedBy`)
  REFERENCES `task_manager`.`user` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Comment Table

In this section, we will design the Comment Table to store the task and activity comments. Below mentioned is the description of all the columns of the Comment Table.

Id	The unique id to identify the product review.
Task Id	The task id to identify the parent task.
Activity Id	The activity id to identify the parent activity.
Title	The review title.
Created At	It stores the date and time at which the comment is created.
Updated At	It stores the date and time at which the comment is updated.
Content	The column used to store the comment details.

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

CREATE TABLE `task_manager`.`comment` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `taskId` BIGINT NOT NULL,
  `activityId` BIGINT NULL DEFAULT NULL,
  `title` VARCHAR(100) NOT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NULL DEFAULT NULL,
  `content` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_comment_task` (`taskId` ASC),
  CONSTRAINT `fk_comment_task`
    FOREIGN KEY (`taskId`)
    REFERENCES `task_manager`.`task` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

ALTER TABLE `task_manager`.`comment` 
ADD INDEX `idx_comment_activity` (`activityId` ASC);
ALTER TABLE `task_manager`.`comment` 
ADD CONSTRAINT `fk_comment_activity`
  FOREIGN KEY (`activityId`)
  REFERENCES `task_manager`.`activity` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Summary

In this tutorial, we have discussed the database design of a Task Management application to manage the tasks and activities of the application users. It also provided the database design to manage the comments of the tasks and activities.

You may submit your comments to join the discussion. You may also be interested in designing the database of the Notifications, and Calendar Event, and Reminder. The complete database schema is also available on GitHub.

Write a Comment

Discussion Forum by DISQUS