A Project Management Data Model
Database designer and developer, financial analyst.
- database model
- example data model
- example ER diagram
- example ERD diagram
Project management is a booming field. In this article, we’ll examine a data model to support a project management app.
Project management is anything but an easy task. You are limited in many ways – materials, costs, human resources, and project deadlines spring to mind – but it’s still up to you to deliver a result on time.
If you think of building a pyramid, you can easily conclude it was a case of project management! It had a project sponsor (usually Pharaoh), a deadline (Pharaohs’ deadline ☺), human resources (mostly workers and slaves), material resources (stone blocks) and of course a project manager. A lot has changed since then, but the idea is still the same. We need to be as organized as possible if we expect to deliver a project result on time, up to its expected quality, and within its budget.
In this article, we’ll describe a data model that could run a project management application.
A Short Introduction to Project Management
Before we take a look at the model, we need to get some background on project management. I’ll run through some of the most popular terms and describe features that our application should include.
What is a project?
A project is a time-limited effort that, if completed successfully, will create something new and valuable.
In the introduction, we compared project management to building a pyramid. Nowadays, project management can mean building roads or cities, developing new software, defining new methodologies, etc. All of these imply that the project will deliver something completely new (or an improved version of something else).
What is project management?
Project management is the process of accomplishing a predefined goal within a set time period and budget. It requires a varied group of techniques, skills, and tools.
During the project management cycle, we’ll create and implement a plan for our project. We’ll track progress throughout. In some cases, we’ll have to make changes to cope with unplanned situations and events. If that happens, we’ll need to reallocate resources to critical activities and tasks in order to stay on schedule; in a worst-case scenario, we may have to alter our project plan.
In a perfect world, we could deliver project results on time, on budget and with the right quality. In real life, we need to balance scheduling, budgeting and quality during the entire process.
What are some project management terms I need to know?
There are a few popular project management approaches (Lean, PRINCE2, process-based, traditional, etc.) but we won’t go into these now. I’ll stick to the terms that are common in most project management methodologies. Some of these terms are:
- Project stakeholders – All private individuals or business entities that are interested in the successful finishing of the projects. This includes the clients or sponsors of the project, but it can also include user groups, government agencies, and people who work on the project, among many others.
- Project manager – The person in charge of planning, executing, and closing the project. Project managers should be properly educated in the field; they should also be able to use various techniques and tools to fulfill their role.
- Activity – A single action that produces a “small” result. An activity is usually related to other activities. Some can run simultaneously and independently of each other; others must wait until some previous activity is completed. For example, ordering needed software is an activity.
- Task – A group of related activities that produces a “larger” result. If we complete all project tasks successfully, we’ll also close our project successfully. So, after the ordered software is delivered (1 st activity of the task) we can install it (2 nd activity on the same task) and see if it works as expected (3 rd activity). Once we’ve completed all these activities, we’ve also completed this task.
- Critical path – A sequence of related activities that have no time buffer. If any activity on that path requires more time than allotted to it, we’ll need to respond in some way. This could mean modifying our plan, reallocating resources, decreasing quality, or extending the whole project.
- Gantt chart – A graphical representation that is often used to track project progress. The Gantt chart clearly displays all tasks, activities, planned resources and budgets, activity and task statuses, critical paths etc. The X-axis is the time axis (usually measured in weeks) and the Y-axis shows the project tasks and activities.
What should project management software look like?
Project management software should be as simple as possible. It would be best to have the entire Gantt chart on one screen. We can expect that we’ll still need to scroll to see various parts, but we’ll have everything clearly visible on a single screen.
The Data Model
The data model consists of three main subject areas:
- Users and roles
- Tasks and activities
I’ll explain the Users and roles and Project and partners subject areas first. Then we’ll move to the Task and activities subject area, which is the central part of this model.
Section 1: Users and Roles
This section contains all the tables needed to store details about app users, project teams, and team members and their roles.
Some of the employees on our project will be able to log into our application, but most will not need to. Therefore, we need two separate tables: the user_account table and the employee table.
The user_account table contains everything we need to know about the app users. We’ll store the username and password needed for login and personal details like first_name , last_name , and email . The username and email values are UNIQUE in this table. The is_project_manager flag indicates if the user has the authority to make changes on the project. The last attribute in this table is the self-explanatory registration_time .
As I’ve already mentioned, any user with login rights could participate in the project, but that is not the requirement. On the other hand, most of the employees on the project won’t have login rights. Still, we need to store their details in order to relate them with tasks and activities. A list of all employees that are part of any project is stored in the employee table. The employee_code attribute is the alternate UNIQUE key of the table. The employee_name attribute stores the first and the last name of all employees on the project. If the employee has login rights, his user_account_id attribute will have his related ID number from the user_account table.
We’ll usually assign individual employees to a certain task or activity. There are some situations when we’ll use an entire team to complete a certain activity. In these cases, it would be wise to group all members of that team. Otherwise, we risk assigning activities to each employee separately. The team_member table serves that purpose in our model. We’ll store team_id , employee_id and role_id for all employees in a given team. Notice that employees can be assigned to a team only once; therefore the team_id – employee_id pair forms the UNIQUE key of this table. On the other hand, an employee could be a member of several teams.
The remaining two tables in this section are dictionaries. The team table lists all teams we’ve defined in our organization, while the role table lists all the roles that could be assigned to employees on the project. For example, some roles in a software development company are developer, consultant, and project manager. In both tables, the name attributes can contain only UNIQUE values.
Section 2: Projects
In the first section we defined the human resources needed to execute projects. In this section we’ll explain the structure needed to organize project details, partners, and clients.
The most important table in this section is the project table. It is where we’ll store all the projects we’re using our application to manage. For each project, we’ll include the following attributes:
- project_name – is the actual project name. It is NOT UNIQUE because we could have two projects with the same project_name . We differentiate between them according to their start and end dates.
- planned_start_date and planned_end_date – are the expected start and end dates for the project. These are inserted during the planning phase.
- actual_start_date and actual_end_date – are the project’s actual start and end dates. They are inserted during the project execution phase.
- project_description – is a text description of the project, with all the relevant details.
In the project_manager table, we’ll store a list of all users who can manage projects, create new tasks and activities, assign employees to tasks and activities, and modify or delete existing tasks and activities. To assign a user as the manager of a certain project, that user should have the user_account . is_project_manager attribute set to True. The project_id – user_account_id pair holds only UNIQUE values.
Project stakeholders are all entities that have an interest in the successful completion of the project. These could be investors, government agencies, NGOs or not-for-profit organizations, etc. We’ll also likely work with clients, to whom we’ll deliver the project result. We’ll store these interested parties in the client_partner table. For each client or partner on any of our projects, we’ll store a full name, address, and other text details.
The last table in this section, the on_project table, relates clients and partners with projects. The attributes in this table are:
- project_id – is a reference to the project table.
- client_partner_id – is a reference to the client_partner table.
- date_start and date_end – are the dates when the client/partner started and ended their engagement on that project. The date_end attribute can be NULL because we’ll update its value when the engagement ends.
- is_client and is_partner – are flags to denote the role of the entity from the client_partner table. Only one of these should be set at the same time.
- description – is a detailed explanation of the client or partner’s role and engagement in the project.
Section 3: Tasks and Activities
The last section in our model is also the core of our application. We’ll define tasks and activities here, relate them together, and relate them to other parts of the model.
A project is composed of multiple tasks and each task is composed of one or more activities. The task table will store the following details for each task:
- task_name – is the task’s onscreen name.
- project_id – references the project that the task is part of.
- priority – prioritizes the task with an integer value. We can expect a range of numbers (e.g. 1 to 5) to show the task’s priority within the project. This could be crucial information when you have to decide which tasks to start at what time.
- description – is a detailed task description, if needed.
- planned_start_date , planned_end_date and planned_budget – are initial values for the task. These are set in the planning phase.
- actual_start_date , actual_end_date and actual_budget – are the actual values for the task’s start, end, and budget. These are set during the execution phase, as they are completed.
Project tasks generally are done in order. One or more tasks may have to be finished for a new task to start. A list of all such prerequisite tasks is stored in the preceding_task table. We’ll define the task_id and preceding_task_id attributes here. The “preceding_task_id” attribute will store the ID of whatever task is immediately before the current task. The task_id – preceding_task_id pair forms the alternate UNIQUE key of this table.
Each task is composed of one or more activities. The activity table is very similar to the task table. The attributes in this table are:
- activity_name – is an activity’s onscreen name.
- task_id – references the related task.
- priority – uses a range of integers to denote the priority of that activity within its task.
- description – is a detailed activity description, if needed.
- planned_start_date , planned_end_date and planned_budget – are initial values set for that activity in the planning phase.
- actual_start_date , actual_end_date and actual_budget – are the actual values, entered once the activity is completed during the project execution phase.
Like tasks, activities may be ordered in a certain way, so we’ll need another table to store prerequisite activities. In the preceding_activity table, the activity_id – preceding_activity_id pair are UNIQUE.
The last table in this section (and in the model) is the assigned table. It is simple but very important. It relates employees with activities. When we assign an employee to a certain activity, we’ll also define their role (via role_id ) for that activity. Since activities are the smallest job unit, the same employee can’t be assigned to the same role in the same activity more than once.
We could assign a whole team to a certain activity, but the result would be the separate insertion of each team member into the assigned table. We could also assign an employee or even a team to an entire task but the database will store that information as employees assigned to single activities.
How Can This Project Management Data Model Be Used?
I hope that after reading this article you have at least a general idea of how you could build a project management app. I focused on the Gantt chart because it’s one of the most popular chart types for project management. Still, it’s not the only option we could use. We could go with some other graphical or textual representation. Charts are nice and clear, but what happens if your display size is limited? In that case, a simplified graphical or even textual application could be a reasonable option.
Help Improve This Model!
Project management is a really complex area. There are some different concepts and methodologies, but the main idea is the same. In this example, I went with the Gantt chart because most of us are visual types. On smaller projects, we might use a simple text-only To-Do List. In other cases, maybe the Gantt chart isn’t the best choice.
Please share your experience about the project management tools you’ve used, what you loved about them, and what you wanted to change. These suggestions could help us to improve this model significantly.
You may also like
A database model for a freelance job platform, tips for better database design.
Build a Project Management Application From Scratch
What is a Project Management Application?
A project management application is a software system used for project planning, resource allocation, tracking of project components, and change management.
What is in a Project Management Application?
We will be creating a simple and easily customizable PM system to tracks projects, including milestones, tasks, hours, costs, and more. Since every project is unique, this tutorial merely means to build an application foundation; you should be able to easily extend it based on your requirements.
In our project management application, employees will be able to:
View their tasks
Log in hours worked
Managers will be able to:
Manage costs and hours
Manage employee resources
The Building Blocks of a Project Management System
Here are the primary functions of a project management system:
Project planning : To define a project schedule, a project manager may use the software to map project tasks and visually describe task interactions.
Task management : Allows the project manager to create and assign tasks, establish deadlines, and produce status reports.
Resource management : Defines responsibilities – who is supposed to do what.
Budgeting and cost tracking : A good project management application facilitates budget reporting as well as viewing, notifying, and updating costs for stakeholders.
Time tracking : The software must have the ability to track time spent on all tasks and maintain records for third-party consultants.
enter image description here
MySQL or MariaDB
Creating a Project Management Database
We will start by creating our project management database. The main tables we will use are:
Clients – customer company data
Contacts – client contact data. A client can have one or more contacts.
Projects – project information
Milestones – project milestone
Tasks – project tasks
Hours – time spent on each task
Costs – cost of a task
Users – user data; one can be either an employee or a manager
Other tables ( lookup tables ):
Complete Database Schema Diagram
A database schema is the structure that represents the logical view of the entire database: tables, views, and primary and foreign keys. A database schema includes all entities and the relationships between them.
Below is the database diagram of our simple project management application. The key symbol in each table represents the table’s primary key while the magnifying glass indicates a foreign key linking it to another table (lookup table) in the database.
The diagram is made with DBSchema .
Our simple project management contains many DataGrids. The DataGrid is a spreadsheet-like data table which displays rows and columns representing records and fields from the database table. The DataGrid provides the end-user with the ability to read and write to the database tables on a web page.
To create the DataGrid, we use a dataGrid tool from phpGrid . The reason why we use a tool instead of building our grids from scratch is that developing a DataGrid in php is usually extremely tedious and prone to errors. The phpGrid DataGrid library will handle all internal database CRUD (Create, Remove, Update, and Delete) operations for us offering faster and better results with minimal coding.
To install phpGrid, follow these steps:
- Unzip the phpGrid download file.
- Upload the phpGrid folder to the phpGrid folder.
- Complete the installation by configuring the conf.php file.
Before we begin coding, we must include the following information in “conf.php,” the phpGrid configuration file. Here is an example of the database connection settings:
PHPGRID_DB_HOSTNAME – web server IP or host name
PHPGRID_DB_USERNAME – database user name
PHPGRID_DB_PASSWORD – database password
PHPGRID_DB_NAME – database name
PHPGRID_DB_TYPE – type of database
PHPGRID_DB_CHARSET – always ‘utf8’ in MySQL
Our page will be comprised of a header, menu, body, and footer. Instead of creating the same page elements repeatedly, we will start by creating a reusable page template.
This is a basic HTML5 template header; it includes a link to a custom stylesheet that will be created in a later step.
Notice the usage of $_GET[‘currentPage’] . Each page will set a value that will highlight the name of the current page on the top menu bar.
Include the following code in style.css for menu styling; it will transform the above unordered list into a menu.
Simply includes the closing tags for the elements we opened in the header:
Our Complete Reusable Page Template
The main content will go after the section title.
Project Management Main Pages
Our project management application for managers has four pages:
The Clients page displays a list of clients with links to individual client details ( Client Details page).
The Projects page displays a list of projects being managed with links to project details ( Project Details page).
Here is our project management application design mockup for project managers who manage one or more projects and assign tasks to employees.
When a manager logs in to the project management system, the first page he sees is the Clients page which contains a complete list of companies.
The following code will give us a list of clients.
The first line creates a phpGrid object by passing the SELECT SQL statement with its primary key id followed by the name of the database table – clients .
The second line creates a dynamic URL from the primary key “id”; it uses a function called set_col_dynalink() . This function sets a specific column-to-display HTML hyperlink URL based on dynamic values. If the primary key “id” has the value 100, it will display a URL like this client-details.php?id=100 which drills down the client detail page.
The third line, enable_edit() , makes the DataGrid editable and all CRUD (Create, Read, Update, Delete) operations are now accessible.
The last line calls the display() function to render the DataGrid on the screen.
You can check out more demos below:
Display a Dynamic URL: https://phpgrid.com/example/display-dynamic-url/
CRUD PHP Datagrid (Editable Datagrid): https://phpgrid.com/example/edit-datagrid/
From the Clients page, the client name has a hyperlink that redirects to the Client Details page for that client when clicked.
From the Client Details page, we need to obtain the Client ID that is passed as the URL parameter.
In our application, the Client ID should always be an integer. Thus, we use the PHP intval() function to ensure the Client ID is returned as an integer.
The following code displays projects associated with the current $clientId using the filter function set_query_filter() . In addition, we make the DataGrid editable with the enable_edit() function; phpGrid will take care of any CRUD operations for us.
Client Details > Projects
As you may notice, we again use the same function, set_col_dynalink() , to create hyperlinks to the Project Details table using the Project ID. We will get into the project-details.php page next.
Client Details > Contacts
Under the Projects DataGrid, a list of contacts associated with the $clientid is displayed using the same functions set_query_filter() and enable_edit() .
Now, let’s build the Projects page.
The Projects page displays a list of managed projects. It is very similar to the Clients page except that the database table is “Projects,” and the hyperlinks have the URL project-details.php instead of client-details.php .
From the Projects page, each project name has a hyperlink which redirects to each individual Project Details page when clicked.
And from the Project Details page, we retrieve the Project ID for the URL parameter.
Look familiar? It should because both Projects and Project Details pretty much follow the same coding pattern used in the Clients and Client Details pages; there are not really that many surprises.
The Project Details page is made of the following grids, all filtered by the $projectId obtained from the URL parameter.
A milestone marks a major event in a project timeline. Here, we can easily display all the milestones of a project by filtering the value $projectId. Project managers have the necessary access rights to modify the milestones.
Likewise, we can easily filter and display a list of tasks for the current project.
I think you are probably getting the hang of it now. Here’s the code for the two remaining DataGrids.
We can now move on to the final part of the tutorial, the Employees page. Employees can login to view active project tasks assigned to them, track task hours, and costs. Their responsibility is simple: to monitor the tasks and log hours worked on any specific project task.
The Employees page has only one menu item: Tasks .
My Active Tasks
The first part of the page shows a list of active tasks assigned to the current employee. Each task will have the hours reported by the current employee; this is a perfect situation in which to use a phpGrid subgrid .
We also need to use set_query_filter() to display only active tasks that have Status value “2”, and only for the current employee.
For demo purposes, we hard-coded the Employee ID to 2. In a real-world application, the Employee ID value should be stored and retrieved using PHP SESSION .
We then create the active Tasks DataGrid for the current employee. Notice the filter function.
Once we’ve defined the grid for the active tasks, we create a DataGrid to log the hours reported by the current employee.
Lastly, set_subgrid() causes the Hours DataGrid to become a subgrid of the Tasks DataGrid. The linking field in the Hours subgrid is ‘TaskID’, which is the second parameter, and in the main grid Tasks , it is “id,” the third parameter.
My Hours History
Lastly, we would like to display a read-only DataGrid using data from the Hours table for the current employee for reviewing purposes.
Notice we used a function called set_jq_gridName() . You can find more documentation here . This function sets a unique object name for the DataGrid. By default, phpGrid uses the database table name as its internal object name. Since we already created a DataGrid from the Hours table in the last part, we must set a unique name for our second Hours DataGrid.
* Included in Premium and Ultimate editions.
Customer Relationship Management (CRM) is a system that manages customer interactions and data throughout the customer…
Introduction We will learn how to build a fully functional, single-page database content administration system…
* CRUD PHP Datagrid feature is only available in paid versions. The PHP datagrid is…
Academia.edu no longer supports Internet Explorer.
To browse Academia.edu and the wider internet faster and more securely, please take a few seconds to upgrade your browser .
Enter the email address you signed up with and we'll email you a reset link.
- We're Hiring!
- Help Center
Database Design for the Web Based University Project Management Information System
2013, Tem Journal Vol.2, No.2
Web applications and particularly Web based Information Systems are very popular due to several reasons. The main reason is the ability to update and maintain them without distributing and installing software on thousand of client computers. This paper describes the main stages of the development process and mainly requirement analysis and database design. Such kind of system will provide students with the opportunity to choose among more projects, to work with different specialists in other conditions, in international teams, in different environments and to have more opportunities for their future professional realization.
– Web applications and particularly Web based Information Systems are very popular due to several reasons. The main reason is the ability to update and maintain them without distributing and installing software on thousand of client computers. This paper describes the main stages of the development process and mainly requirement analysis and database design. Such kind of system will provide students with the opportunity to choose among more projects, to work with different specialists in other conditions, in international teams, in different environments and to have more opportunities for their future professional realization.
Christian Chibueze Nwachukwu
V. Mardiris , Leonidas L Fragidis , V. Chatzis
Procedia - Social and Behavioral Sciences
International Journal of Scientific and Research Publications
This work is on design and implementation of a software system for students' projects (dissertation) allocation system. It will be used to enable final year students of Imo State Polytechnic Umuagwo apply for projects and upload completed project. Model View Controller (MVC) methodology was used in the in designing the system. The database used is MySQL, while implementation was done with PHP. Primary and secondary sources were used for data collection. The result of the research is the development of a project allocation system that successfully allows students to apply for projects online; which the supervisors will mark and the system allocates a project to the student using the highest score awarded to the student's proposals by the supervisor.
Over the years, cataloguing of students' projects are carried out manually. The project titles and supervisor's names are documented according to the year they were carried out on papers and in some cases, no record of these projects are found. This manual method is usually time consuming, strenuous, inefficient and frustrating. This work focuses on the development of a database management system for cataloguing students' project by using My-Structured Query Language (MySQL) database management software. In this work, the authors proposed a software system called Thesis Management. Thesis Management is a software application running on database application platforms. It stores project information in a local database. Project information can easily be added, deleted, updated or searched in the project database. To test run Thesis Management, information of thirty (30) PhD thesis were uploaded into the database. This information includes thesis title, thesis abstract, details of the student and the supervisor. Information about any of the uploaded thesis were retrieved by entering keywords (student's name, supervisor's name, the thesis title or any word included in the thesis title) on the search field.
Afan Izang Aaron
— As business become increasingly dependent on Information Technology for their operations, project managers find themselves under pressure to remain innovative and go forward to deliver quality projects, on time and within budget constraints. However, some organizations still find it hard to plan and track project components, stakeholders and resources. Additionally, project managers, team members and customers do not communicate frequently to share their expert opinions. Projects tend to extend beyond scheduled deadlines, not necessarily due to lack of resources or incompetence of projects members, but often because of inability to elicit requirements completely and inadequacy of proper communication. To this end, with the advent of Information Technology, there has been an increase in the demand for software that make jobs easier for people, as a result, to keep up with rising demand, project managers need a way to effectively manage their software projects. Using the iterative methodology, a web based software project management system was developed, that fully monitors project progress, allocates tasks, creates milestones and provides an avenue for stakeholders to track project progress during its development phase. The developed system solves the problem of unity and lack of communication. With the system, once added to a new project, all participating members may send messages to one another and keep tabs on the progress of the project so as to implement the stakeholder's requirements efficiently.
Management information systems are computer based information systems, which collect and analyse data, and transform this data to the information, which is valuable for the managers who are responsible from the organizational activities. A management information system, established in a university may increase the in-house activity by improving the decision making process of the senior management. Software development life cycle has essential steps. These steps involve planning, analysis, design production and maintenance. In this study, a software application planning, analysis and design processes are implemented in order to create a model management information system for universities. Planning phase include feasibility and requirement specification. Analysis phase include an examination of an existing organizational structure of a university and exposure of the essential problems and requirements. Design phase include the basic software structure in order to meet the requirement...
Jurnal Riset Informatika
International Journal of Engineering Research and Technology (IJERT)
Procedia-Social and Behavioral Sciences
Computers & Education
César Domínguez , Arturo Jaime
JOURNAL OF CRITICAL REVIEWS
Abdul Cader Mohamed Nafrees
JEEMECS (Journal of Electrical Engineering, Mechatronic and Computer Science)
Nawroz Fadhil Ahmed
International Journal for Research in Applied Science & Engineering Technology (IJRASET)
COMMUNICATION AND COGNITION
e-Journal Universitas Tribuana Kalabahi
THE IJES Editor
IJCSMC Journal , Ihsan Hassan
Igor Sikorsky Kyiv Polytechnic Institute
Proceedings of 3rd Global Congress on …
Proceedings of the 11th …
Mathematical and Software Engineering, Var εpsilon Ltd, varepsilon.com
Akpasam J O S E P H Ekanem
International Research Journal of Computer Science
American Journal of Software Engineering and Applications
Wisdom Atiwoto , Wisdom Takramah
Lecture Notes in Computer Science
Shaina Jessa B . Bondad
ILKOM Jurnal Ilmiah
abdul quddos wali khan
Computer Applications in Engineering Education
International Journal of Recent Innovations in Academic Research
- We're Hiring!
- Help Center
- Find new research papers in:
- Health Sciences
- Earth Sciences
- Cognitive Science
- Computer Science
- Academia ©2023
- Skip to content
- Skip to search IOPscience
- Skip to Journals list
- Accessibility help
- Accessibility Help
Click here to close this panel.
Design of Project Data Management Information System
A P Fadillah 1 and D Fitriana 1
Published under licence by IOP Publishing Ltd IOP Conference Series: Materials Science and Engineering , Volume 662 , Issue 2 Citation A P Fadillah and D Fitriana 2019 IOP Conf. Ser.: Mater. Sci. Eng. 662 022014 DOI 10.1088/1757-899X/662/2/022014
588 Total downloads
Share this article
1 Universitas Komputer Indonesia, Jl. Jalan Dipatiukur No. 112-116 Indonesia
Buy this article in print
The purpose of this research is to further improve the design of the project management information. PT. XYZ is a company located in Kiarapayung, West Bandung, Indonesia. This company is engaged in services in the procurement of building construction or road construction. The company has not used an integrated information system in the management of project data, companies have difficulty in managing both transaction data and create reports. Information system aims to support operations, management, and decisionmaking . Data that has not been integrated has resulted in difficulties in managing reports, and has made it difficult to process the salary of workers who are involved in ongoing project implementation which needs a management information system to help the project data so that data management and report generation can be easily created, because the data is already integrated with the database. It can be concluded from the problems described earlier that the company needed a data management information system project. Object-oriented approach method and prototype system development method were used for this study. The result is that project management system can improve the process of managing data more easily and quickly.
Export citation and abstract BibTeX RIS
Content from this work may be used under the terms of the Creative Commons Attribution 3.0 licence . Any further distribution of this work must maintain attribution to the author(s) and the title of the work, journal citation and DOI.
Online Programming Lessons, Tutorials and Capstone Project guide
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.
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.
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.
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.
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.
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.
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.
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.
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.
- Class Scheduling System Chapter 1
- News Portal Database Design