Project Management System Database Design

Project Management System Database Design

This article will provide you with the list of tables and entities for every table in the development of project management system.

This is the first phase of the project, next is to prepare the screen design and layout of the system and it will be converted into html file using the Bootstrap Framework. Functions of the system will be the last part of the development; the developers will use PHP as the scripting language.

tbl_project – information of the project will recorded and stored in the tbl_project table, the table has 10 attributes as presented below.

• project_id – this is the primary key of the table.
• company_id – this is a foreign key that links to the information of company in the tbl_company. it refers to the company that owns and requested the project.
• category_id – category of the project.
• project_name – refers to the name of the project.
• project_description – detailed information about the project.
• project_code – the reference code that will be used in the monitoring of project.
• project_banner – the image that will be uploaded to represent the project.
• project_manager_id – the foreign key that links to the project manager in the tbl_project_manager table. It refers to the project manager who will manage the project.
• project_start_date – the date the project will start.
• project_end_date – the date the project will end.
• project_remarks – the status of the project, comments, notes and observations about the project.

Create SQL Statement – the statement below is used to create the tbl_project, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbl_project` (
`project_id` int(11) NOT NULL AUTO_INCREMENT,
`company_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`project_name` varchar(50) NOT NULL,
`project_description` varchar(100) NOT NULL,
`project_code` varchar(15) NOT NULL,
`project_banner` longblob NOT NULL,
`project_manager_id` int(11) NOT NULL,
`project_start_date` date NOT NULL,
`project_end_date` date NOT NULL,
`project_remarks` varchar(30) NOT NULL,
PRIMARY KEY (`project_id`),
KEY `company_id` (`company_id`,`project_manager_id`),
KEY `category_id` (`category_id`),
KEY `project_manager_id` (`project_manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_project_member_assignment – this table is used to store the list of project members that will be a part of the project development and maintenance.

• project_details_id – this is the primary key of the table, it is usually unique and duplicate entry is not allowed.
• project_id – the foreign key that links to the tbl_project table.
• project_member_id – it refers to the project members that will be a part of the project, it is a foreign key that links to the tbl_project_members.

Create SQL Statement – the statement below is used to create the tbl_project_member_assignment, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbl_project_member_assignment` (
`project_details_id` int(11) NOT NULL AUTO_INCREMENT,
`project_id` int(11) NOT NULL,
`project_member_id` int(11) NOT NULL,
PRIMARY KEY (`project_details_id`),
KEY `project_id` (`project_id`,`project_member_id`),
KEY `project_member_id` (`project_member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_project_manager – information of the project manager will be stored in this table and it has 8 attributes.

• project_manager_id – primary key of the table.
• project_manager_code – the code given by the system to the project managers, this serves as their reference number.
• project_manager_name – the fullname of the project manager.
• project_manager_contact – contact number information of the project manager.
• project_manager_email – email address of the project manager.
• project_manager_username – the desired username of the project manager, this can be manage by the project manager.
• project_manager_password – the desired password of the project manager, this can be manage by the project manager. For security purpose the minimum password length is set to 12 characters.
• account_status – this is for the activation and deactivation of the user account. Deactivated account can no longer access the platform.

Create SQL Statement – the statement below is used to create the tbl_project_manager, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbl_project_manager` (
`project_manager_id` int(11) NOT NULL AUTO_INCREMENT,
`project_manager_code` varchar(15) NOT NULL,
`project_manager_name` varchar(50) NOT NULL,
`project_manager_contact` varchar(15) NOT NULL,
`project_manager_email` varchar(30) NOT NULL,
`project_manager_username` varchar(30) NOT NULL,
`project_manager_password` varchar(30) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`project_manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_project_category – the project were group according to the scope and nature of the project, this is the table that list down those groups.

• category_id – primary key of the table.
• category_name – name of category.
• category_description – description of the category.

Create SQL Statement – the statement below is used to create the tbl_project_category, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbl_project_category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`category_name` varchar(30) NOT NULL,
`category_description` varchar(150) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_company – company information will be stored in the tbl_company table. The table has 10 fields or column.

• company_id – primary key of the table.
• company_name – the name of the company.
• company_logo – logo or banner of the company.
• company_information – information about the company such as the about us, vmgo, etc.
• company_website – the website of the company (if applicable).
• company_contact_info – contact information of the company.
• company_email – email address of the company.
• company_username – username of the representative delegated by the company in this platform.
• company_password – password used to access the platform.
• company_account_status – this is for the activation and deactivation of the user account. Deactivated account can no longer access the platform.

Create SQL Statement – the statement below is used to create the tbl_company, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbl_company` (
`company_id` int(11) NOT NULL AUTO_INCREMENT,
`company_name` varchar(30) NOT NULL,
`company_logo` longblob NOT NULL,
`company_information` varchar(150) NOT NULL,
`company_website` varchar(50) NOT NULL,
`company_contact_info` varchar(15) NOT NULL,
`company_email` varchar(30) NOT NULL,
`company_username` varchar(30) NOT NULL,
`company_password` varchar(30) NOT NULL,
PRIMARY KEY (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_project_member  – this table will store the information of the project members that will be a part of the project development.

• project_member_id – primary key of the table.
• project_ member _code – the code given by the system to the project member, this serves as their reference number.
• project_ member _name – the fullname of the project member.
• project_ member _contact – contact number information of the project member.
• project_ member _email – email address of the project member.
• project_ member _username – the desired username of the project member, this can be manage by the project member.
• project_ member _password – the desired password of the project member, this can be manage by the project member. For security purpose the minimum password length is set to 12 characters.
• account_status – this is for the activation and deactivation of the user account. Deactivated account can no longer access the platform.

Create SQL Statement – the statement below is used to create the tbl_project_member, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbl_project_member` (
`project_member_id` int(11) NOT NULL AUTO_INCREMENT,
`project_member_code` varchar(15) NOT NULL,
`project_member_name` varchar(50) NOT NULL,
`project_member_contact` varchar(15) NOT NULL,
`project_member_email` varchar(30) NOT NULL,
`project_member_username` varchar(30) NOT NULL,
`project_member_password` varchar(30) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`project_member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbl_project_updates – the list of updates for every project will be stored in this table, it has 6 attributes as presented below.

• update_id – primary key of the table.
• update_code – code reference of the update transaction.
• date_of_update – the date the update was created.
• description_of_update – remarks, comments and description of the update
• project_id – the foreign key that links to the project information (tbl_project).
• member_id – the project member who posted the update. Foreign key that links to the tbl_project_member table

Create SQL Statement– the statement below is used to create the tbl_project_updates, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE IF NOT EXISTS `tbl_project_updates` (
`update_id` int(11) NOT NULL AUTO_INCREMENT,
`update_code` varchar(15) NOT NULL,
`date_of_update` date NOT NULL,
`description_of_update` varchar(30) NOT NULL,
`project_id` int(11) NOT NULL,
`member_id` int(11) NOT NULL,
PRIMARY KEY (`update_id`),
KEY `project_id` (`project_id`,`member_id`),
KEY `member_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Download .sql file

Constraints for dumped tables
—
— Constraints for table `tbl_project`
—
ALTER TABLE `tbl_project`
ADD CONSTRAINT `tbl_project_ibfk_3` FOREIGN KEY (`project_manager_id`) REFERENCES `tbl_project_manager` (`project_manager_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_project_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `tbl_project_category` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_project_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `tbl_company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbl_project_member_assignment`
—
ALTER TABLE `tbl_project_member_assignment`
ADD CONSTRAINT `tbl_project_member_assignment_ibfk_2` FOREIGN KEY (`project_member_id`) REFERENCES `tbl_project_member` (`project_member_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_project_member_assignment_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`project_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbl_project_updates`
—
ALTER TABLE `tbl_project_updates`
ADD CONSTRAINT `tbl_project_updates_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `tbl_project_member` (`project_member_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbl_project_updates_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `tbl_project` (`project_id`) ON DELETE CASCADE ON UPDATE CASCADE;

You may visit our facebook page for more information, inquiries and comments.

Hire our team to do the project.