Task Management System Database Design

Task Management System Database Design

Task management system is an information system that record, update and monitor every details of the task or project. This article will serve as a guide in terms of preparing and planning a database design and model for a task or project management.

The task management system is available in the following programming languages

• Visual Basic (MS Access, MySQL/MariaDB)
• C#
• PHP

tbladmin (admin_id, admin_name, admin_email, username, password)

Description: admin table includes the information such as the complete name, email address together with the username and password. Admin account can only encode and update information of employee, project management, client and as well as to encode the task and task category. Moreover, the admin account can assign a task for the project manager.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbladmin` (
`admin_id` int(11) NOT NULL AUTO_INCREMENT,
`admin_name` varchar(50) NOT NULL,
`admin_email` varchar(30) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblclient (client_id, client_name, client_company, client_contact, client_email, username, password, account_status)

Description: tblclient stores the information of the clients such as the name, company name, contact information, email address, username and password. The client can also monitor the progress of the project and can communicate with the employees and project manager assigned to the tasks.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblclient` (
`client_id` int(11) NOT NULL AUTO_INCREMENT,
`client_name` varchar(50) NOT NULL,
`client_company` varchar(50) NOT NULL,
`client_contact` varchar(11) NOT NULL,
`client_email` varchar(30) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblemployee (employee_id, employee_id_no, employee_name, employee_email, username, password, account_status)

Description: tblemployee is the database table that stores the information of the employees; those information includes the id number, complete name, email address, username and password. The employee account will be encoded by the admin and the admin can also enable/disable the account which means that the admin has the option not to allow the employees to gain access to the system.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblemployee` (
`employee_id` int(11) NOT NULL AUTO_INCREMENT,
`employee_id_no` varchar(11) NOT NULL,
`employee_name` varchar(50) NOT NULL,
`employee_email` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblprojectmanager (manager_id, manager_idno, manager_name, manager_email, username, password, account_status)

Description: the admin account will also encode the information of the project managers; it includes the id number, name, email address, username and password. The project manager can monitor the progress and updates of the tasks. The project manager can also view the work and can communicate with the different employees assigned in a specific task.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblprojectmanager` (
`manager_id` int(11) NOT NULL AUTO_INCREMENT,
`manager_idno` varchar(11) NOT NULL,
`manager_name` varchar(50) NOT NULL,
`manager_email` varchar(30) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbltask (task_id, task_name, task_description, category_id, start_date, end_date, task_status, client_id, manager_id, admin_id)

Description: the core feature of the project is to be able encode different types of task requested by the clients and assigned those tasks to the different project managers. The database for storing the task information is the tbltask; it includes the name of the task, description, the task category, the date that the project will start and when it will end, the client who requested the task and the project manager or leader of the project/task.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbltask` (
`task_id` int(11) NOT NULL AUTO_INCREMENT,
`task_name` varchar(50) NOT NULL,
`task_description` varchar(100) NOT NULL,
`category_id` int(11) NOT NULL,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
`task_status` int(5) NOT NULL,
`client_id` int(11) NOT NULL,
`manager_id` int(11) NOT NULL,
`admin_id` int(11) NOT NULL,
PRIMARY KEY (`task_id`),
KEY `category_id` (`category_id`,`client_id`,`manager_id`,`admin_id`),
KEY `admin_id` (`admin_id`),
KEY `manager_id` (`manager_id`),
KEY `client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbltaskcategory (task_category_id, category_name, admin_id)

Description: tbltaskcategory is where the types of tasks are encoded and stored.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbltaskcategory` (
`task_category_id` int(11) NOT NULL AUTO_INCREMENT,
`category_name` varchar(30) NOT NULL,
`admin_id` int(11) NOT NULL,
PRIMARY KEY (`task_category_id`),
KEY `admin_id` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbltaskupdate (task_update_id, task_id, update_info, date_updated, employee_id)

Description: every task and project has different phases or parts, in order to properly monitor a every updates or changes to the project, it must be recorded and the database table where all the updates are being stored is the tbltaskupdate. Once an employee has done some changes or progress to the project the system will record and store those activities, records from this table can also be monitor by the project manager and clients.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbltaskupdate` (
`task_update_id` int(11) NOT NULL AUTO_INCREMENT,
`task_id` int(11) NOT NULL,
`update_info` varchar(50) NOT NULL,
`date_updated` date NOT NULL,
`employee_id` int(11) NOT NULL,
PRIMARY KEY (`task_update_id`),
KEY `task_id` (`task_id`,`employee_id`),
KEY `employee_id` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables

— Constraints for table `tbltask`

ALTER TABLE `tbltask`
ADD CONSTRAINT `tbltask_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `tbladmin` (`admin_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tbltask_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `tbltaskcategory` (`task_category_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tbltask_ibfk_3` FOREIGN KEY (`manager_id`) REFERENCES `tblprojectmanager` (`manager_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tbltask_ibfk_4` FOREIGN KEY (`client_id`) REFERENCES `tblclient` (`client_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

— Constraints for table `tbltaskcategory`

ALTER TABLE `tbltaskcategory`
ADD CONSTRAINT `tbltaskcategory_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `tbladmin` (`admin_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

— Constraints for table `tbltaskupdate`

ALTER TABLE `tbltaskupdate`
ADD CONSTRAINT `tbltaskupdate_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tbltask` (`task_id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tbltaskupdate_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `tblemployee` (`employee_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.

Hire our team to do the project.