COVID-19 Facilities Management Information System Database Design

COVID-19 Facilities Management Information System Database Design

About the System

The capstone project, titled “COVID-19 Facilities Management Information System,” is a system that automates the management of the various COVID-19 facilities that the barangays provide. The system will track and manage the status and availability of the rooms so that information about where COVID-19 facilities are still available and capable of accepting COVID patients can be easily retrieved.

COVID – 19 has put people in fear due to its capability of transmission when exposed to the virus. The health sectors and the government provide isolation facilities for COVID-19 patients to mitigate the spread and transmission of the virus. However, proper communication for the availability of the facilities is inefficient resulting to surge of patients in just one facility and some are transferred multiple times due to unavailability. The COVID-19 respondents must have an advance tools to manage the COVID-19 facilities where respondents can easily look for available facilities to cater more patients.

In this article/video tutorial, we will discuss the database design of a Faculty Evaluation System. Let’s get started with creating the tables and their columns. Please watch the video for a more in-depth demonstration.

Database Tables

Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.

tbl_barangay – this table store the information of the barangays registered in the system as COVID-19 facilities.

• barangay_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• barangay_name – the name of the barangay
• contact_info – the contact information of the barangay, it can be a mobile or telephone number.

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

CREATE TABLE `tbl_barangay` (
`barangay_id` int(11) NOT NULL,
`barangay_name` varchar(50) NOT NULL,
`contact_info` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_patient – the patient’s information are held in this table.

• patient_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• patient_code – a unique code number given to a specific patient.
• first_name – first name of the patient
• middle_name – middle name of the patient.
• last_name – last name of the patient.
• contact – contact number of the patient, preferably mobile or telephone number.
• email – email address of the patient.
• barangay_id – this is a foreign key that points out to the barangay where the patient is a resident
• age – the age of the patient
• gender – the gender of the patient
• remarks- additional information about the patient
• status – the status of the patient in the facility
• user_id – encoded by

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

CREATE TABLE `tbl_patient` (
`patient_id` int(11) NOT NULL,
`patient_code` varchar(15) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`barangay_id` int(11) NOT NULL,
`age` int(3) NOT NULL,
`gender` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL,
`status` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_room – this table hold the information of the rooms in the COVID-19 facility.

• room_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• room_number – the number assigned to the room.
• status – the status of the room, it’s either occupied or not

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

CREATE TABLE `tbl_room` (
`room_id` int(11) NOT NULL,
`room_number` int(4) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_room_occupants –  the details of the room occupants are store in this table.

• occupant_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• room_id – this is a foreign key that points out to the room being occupied
• patient_id – this is a foreign key that points out to the patient that occupied the room
• date_transferred_to_facility – the date the patient transferred to the the facility
• date_discharged – the date the patient was discharged in the facility
• remarks – additional information about the room’s occupants
• user_id – encoded by

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

CREATE TABLE `tbl_room_occupants` (
`occupant_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`patient_id` int(11) NOT NULL,
`date_transferred_to_facility` date NOT NULL,
`date_discharged` date NOT NULL,
`remarks` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – this table hold the information of the system’s users.

• user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• username – desired username of the user that is combined with the password when logging in to the system.
• password – the preferred password of the user used to logged in to the system.
• avatar – this will hold the picture of the user in the system
• full_name – the full name of the user
• contact – the contact details of the user
• email – the email address of the user
• category – the user’s category
• status – the status of the user assigned to 1 and 0, active or inactive

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

CREATE TABLE `tbl_user` (
`user_id` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`avatar` longblob NOT NULL,
`full_name` varchar(100) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`category` int(1) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_lgu_setting- this table hold the lgu setting information in the system.

• setting_id -primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• lgu_name – the name of the lgu
• lgu_address – the address where the lgu is located
• lgu_contact – the contact details of the lgu
• lgu_logo – the logo image of the lgu

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

CREATE TABLE `tbl_lgu_setting` (
`setting_id` int(11) NOT NULL,
`lgu_name` varchar(50) NOT NULL,
`lgu_address` text NOT NULL,
`lgu_contact` varchar(50) NOT NULL,
`lgu_logo` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_backup – this table store the backup details of the system.

• backup_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• backup_name – the backup name
• backup_date – the date when is the backup created
• user_id – process backup

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

CREATE TABLE `tbl_backup` (
`backup_id` int(11) NOT NULL,
`backup_name` varchar(30) NOT NULL,
`backup_date` date NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Summary

A well-designed database can provide a variety of advantages, including the ones listed below:

1. Development time and costs have been reduced. A well-planned database can be built and executed significantly more rapidly and at a lesser cost than a database that has not been carefully considered.
2. Increased overall performance. A well-planned database is more likely to be created for maximum performance than one that has not been planned.
3. A system that is more ordered and efficient. In comparison to a database that has not been well designed, a well-planned database is more likely to be orderly and efficient.
4. Improved data quality and dependability are achieved. A well-planned database will typically be constructed in such a way that excellent data quality and dependability are ensured.
5. Less time and money spent on training. A well-designed database will typically necessitate less training than a database that has not been properly planned.
6. Increased system adaptability and adaptability as a result of increased flexibility. It is more likely that a well-designed database will be more flexible and adaptive than one that has not been adequately organized.

Please watch the video tutorial on how to prepare and create the different tables of the database of COVID-19 Facilities Management Information System.

Video Tutorial

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

Hire our team to do the project.

Related Topics and Articles:

Smart Healthcare Support for Remote Patient Monitoring During COVID-19 Quarantine

Online Platform for COVID-19 Contact Tracing System

COVID-19 Facilities Information System

City COVID-19 Profiling System with Decision Support

Help Desk Web Information System for COVID-19

COVID-19 Online Platform for Complaints and Communication System

Online Patient Information System Free Download in Bootstrap and PHP

Virtual Online Tour Application Free Bootstrap Template Source code

Restaurant Food Delivery System Free Download Bootstrap Source code