database design for project management system

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.

Our website uses cookies. By using this website, you agree to their use in accordance with the browser settings. You can modify your browser settings on your own. For more information see our Privacy Policy .

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 projects

Manage milestones

Manage tasks

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

System Requirements

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 ):

ms_status –

proj_status –

task_status –

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 .

Setup phpGrid

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

Page Template

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:

Client Details

Project details.

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).

Design Mockup

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.

Employees Page

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.

Screenshots

* Included in Premium and Ultimate editions.

Related Posts

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.

Related Papers

Irena Valova

– 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

Christian Nwachukwu

The need for project research and writing cannot be over emphasis as it is an obligation and one of the requirement for graduating in a tertiary institution, nevertheless there are many problems facing the current system of project research and managements in tertiary institution, ranging from topic or research duplication, times factors between students and supervisors and expenses involved in writing projects, hence the motivation to do this project work which arose from the stated problems without doubts can be eradicated by introducing a well-organized project research and management system to handle all project research ranging from the proposal phase to the summary phase, The programing language to be used include: PHP, jQuery, HTML, CSS and MySQL, with the front-end designed using HTML,CSS, jQuery and back-end designed using PHP and JavaScript, while the Database is designed using MySQL.The methodology employed in this research work is Structured System Analysis and Design Method – SSADM, and hence this system software is more secured, user-friendly and less time and memory consuming which will enhance project writing as well as yielding better results as regards to time factor, topic selection, eradication of duplicated project topics, project submission and convince for both supervisors and students.

V. Mardiris , Leonidas L Fragidis , V. Chatzis

Procedia - Social and Behavioral Sciences

Murat Iskefiyeli

International Journal of Scientific and Research Publications

Anthony Amadi

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.

Francis Inegbedion

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.

Crista Mohammed

Muhammet Damar

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...

Agenagnew Asfaw

RELATED PAPERS

Francis Ganya

Ravish Kumar

Jurnal Riset Informatika

International Journal of Engineering Research and Technology (IJERT)

IJERT Journal

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)

diana rahmawati

IJERA Journal

Nawroz Fadhil Ahmed

Samson AREKETE

Abhay Kolhe

Arockiasamy Soosaimanickam

International Journal for Research in Applied Science & Engineering Technology (IJRASET)

IJRASET Publication

Dana Hussein

COMMUNICATION AND COGNITION

Pavol Navrat

e-Journal Universitas Tribuana Kalabahi

Erna Malaikosa

THE IJES Editor

IJCSMC Journal , Ihsan Hassan

Igor Sikorsky Kyiv Polytechnic Institute

Ruslan Taranenko

Proceedings of 3rd Global Congress on …

Ioana Moisil

IRE JOURNALS

Adebola OROGUN

Proceedings of the 11th …

Shadi Aljawarneh

Maurice Ifergan

Mathematical and Software Engineering, Var εpsilon Ltd, varepsilon.com

Akpasam J O S E P H Ekanem

yishak mustefa

International Research Journal of Computer Science

Wole Olatokun

American Journal of Software Engineering and Applications

Wisdom Atiwoto , Wisdom Takramah

saleem alzoubi

Lecture Notes in Computer Science

J. Krogstie

Shaina Jessa B . Bondad

ILKOM Jurnal Ilmiah

Benny Panggabean

abdul quddos wali khan

Santiago Poaquiza

IRJET Journal

Computer Applications in Engineering Education

Mamoun Awad

International Journal of Recent Innovations in Academic Research

RELATED TOPICS

•   We're Hiring!
•   Help Center
• Find new research papers in:
• Health Sciences
• Earth Sciences
• Cognitive Science
• Mathematics
• Computer Science
• Academia ©2023

Accessibility Links

• 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

Article metrics

588 Total downloads

Share this article

Author e-mails.

[email protected]

Author affiliations

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 [1]. 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.

iNetTutor.com

Online Programming Lessons, Tutorials and Capstone Project guide

Project Management System Database Design

Please enable JavaScript

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.

Post navigation

• Class Scheduling System Chapter 1
• News Portal Database Design

Similar Articles

Donation Information System in PHP and MySQL

Integrated Enrolment with Student Kiosk System

Boreas – 618 A Firefighting Robot