Health Center Patient Information Database Design

Health Center Patient Information Database Design

Introduction

The Health Center Patient Information is a system that can be installed in a stand-alone computer and in local area networks.

With the use of the proposed, Health Center Patient Information every transaction will be automated and there will be fast response and efficient service given to the patients who will avail of the services in the health center.

Database Design/Schema

tblactivities (activityid, activityname, description, date, dateencoded, encodedby)

Description: tblactivities will store information of the different scheduled activities of the health center, it includes the name of activity and the scheduled date.

Create Table SQL Statement:

CREATE TABLE `tblactivities` (
  `activityid` int(11) NOT NULL,
  `activityname` varchar(50) NOT NULL,
  `description` varchar(100) NOT NULL,
  `date` date NOT NULL,
  `dateencoded` date NOT NULL,
  `encodedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblconsultation (consultationid, patientid, weight, temperature, bloodpressure, prescription, dateofconsultation, encodedby, referredto)

Description: consultation table will store information of the patient/resident, it also includes the weight, temperature, blood pressure of the patient and the prescription given by the attending physician.

Create Table SQL Statement:

CREATE TABLE `tblconsultation` (
  `consultationid` int(11) NOT NULL,
  `patientid` int(11) NOT NULL,
  `weight` float NOT NULL,
  `temperature` float NOT NULL,
  `bloodpressure` varchar(10) NOT NULL,
  `prescription` varchar(50) NOT NULL,
  `dateofconsultation` date NOT NULL,
  `encodedby` int(11) NOT NULL,
  `referredto` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblhealthcenterinfo (id, healthcentername, address, contactinfo, officehead)

Description: tblhealthcenterinfo is the table responsible for storing the information of health center such as the name of the center, address, contact person and the head of the office. The users of the system can update such information.

Create Table SQL Statement:

CREATE TABLE `tblhealthcenterinfo` (
  `id` int(11) NOT NULL,
  `healthcentername` varchar(30) NOT NULL,
  `address` varchar(50) NOT NULL,
  `contactinfo` varchar(15) NOT NULL,
  `officehead` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblillnessinfo (illnessid, illnessname, symptoms, dateencoded, encodedby)

Description: illness information table will store the information of illnesses recorded in the consultation module, this is to simplify the encoding so that the users will not anymore type the illness and instead it will search the illness info recorded in this table.

Create Table SQL Statement:

CREATE TABLE `tblillnessinfo` (
  `illnessid` int(11) NOT NULL,
  `illnessname` varchar(30) NOT NULL,
  `symptoms` varchar(100) NOT NULL,
  `dateencoded` date NOT NULL,
  `encodedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblillnessoccurencerecord (id, consultationid, illnessid)

Description: the system can provide statistics for the occurrences of illnesses. This table is connected to the consultation table.

Create Table SQL Statement:

CREATE TABLE `tblillnessoccurencerecord` (
  `id` int(11) NOT NULL,
  `consultationid` int(11) NOT NULL,
  `illnessid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblmedicine (medicineid, medicinename, genericname, purpose, dateencoded, encodedby)

Description: the system can also store information of medicines such as the name of the medicine, it’s generic name and purpose/usage of the medicine.

Create Table SQL Statement:

CREATE TABLE `tblmedicine` (
  `medicineid` int(11) NOT NULL,
  `medicinename` varchar(30) NOT NULL,
  `genericname` varchar(30) NOT NULL,
  `purpose` varchar(50) NOT NULL,
  `dateencoded` date NOT NULL,
  `encodedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblmedicinedispense (id, consultationid, medicineid, quantity, patientid, dateencoded, encodedby)

Description: another core feature of the system is to monitor the inventory of medicine. Distribution of medicines are stored in the tblmedicinedispense, information includes the consultation id, medicine id, number or quantity of medicine distributed to the patient and the person encoded the transaction.

Create Table SQL Statement:

CREATE TABLE `tblmedicinedispense` (
  `id` int(11) NOT NULL,
  `consultationid` int(11) NOT NULL,
  `medicineid` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `patientid` int(11) NOT NULL,
  `dateencoded` date NOT NULL,
  `encodedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblpurok (purokid, purokname, dateencoded, encodedby)

Description: the users of the system can also add and update the list of purok covered by their health center.

Create Table SQL Statement:

CREATE TABLE `tblpurok` (
  `purokid` int(11) NOT NULL,
  `purokname` varchar(50) NOT NULL,
  `dateencoded` date NOT NULL,
  `encodedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblresident (residentid, firstname, middlename, lastname, age, gender, purokid, dateofbirth, status, nationality, occupation, dateencoded, encodedby)

Description: tblresident is the table that stores the information of resident/patient, such information includes the name, age, gender, contact, occupation and others.

Create Table SQL Statement:

CREATE TABLE `tblresident` (
  `residentid` int(11) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `middlename` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `age` int(3) NOT NULL,
  `gender` int(2) NOT NULL,
  `purokid` int(11) NOT NULL,
  `dateofbirth` date NOT NULL,
  `status` int(2) NOT NULL,
  `nationality` varchar(20) NOT NULL,
  `occupation` varchar(30) NOT NULL,
  `dateencoded` date NOT NULL,
  `encodedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbluser (id, username, password, completename, contact, address, designation, status)

Description: only authorized users can access and open the said system. for security and auditing purposes every table is connected to the tbluser which means that every transaction is recorded.

Create Table SQL Statement:

CREATE TABLE `tbluser` (
  `id` int(11) NOT NULL,
  `username` varchar(25) NOT NULL,
  `password` varchar(25) NOT NULL,
  `completename` varchar(50) NOT NULL,
  `contact` varchar(15) NOT NULL,
  `address` varchar(50) NOT NULL,
  `designation` varchar(30) NOT NULL,
  `status` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbluserlog (logid, userid, logintime, logouttime, date)

Description: this table is responsible for storing the login and logout time of the users.

Create Table SQL Statement:

CREATE TABLE `tbluserlog` (
  `logid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `logintime` time NOT NULL,
  `logouttime` time NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Development Tools:

The system is available in Visual Basic, C#, and web version (PHP, MySQL and Bootstrap)

Expert testing

In this phase the health center personnel compared the developed Health Center Patient Information System to the present manual barangay health center recording/profiling system where they had been using it. And the experts rate our owned made user acceptance testing together with our questionnaire.

As the Health Center Patient Information System underwent examinations or testing in this phase we will know if the proposed system developed functioned well or work well, and or there is a bit changes of the system for  improvement but depending upon the suggestion of the three IT experts if there is need to be improve or to add. In the evaluation of the Health Center Patient Information System, the developers had been used the McCall’s Software Quality Model for the assurance of the evaluation by the software expert. The interpretation is 3.97 it is mean GOOD.

Final Testing

The final testing instrument is categorized in two (2) the Ease to Use and the User interface. The respondents will be rating after the initial and the expert testing the developed proposed system and the interpretation is 4.57 it is mean Very Good.

After the series of testing and evaluating the system by the three (3) IT experts, the system was prepared for the final testing to check the features and its function of the Health Center Patient Information System.To meet the users expectation, thus the system work correctly/accurately and accordingly.

User Acceptance/Final Testing Phase

This is the phase, where the Health Center Patient Information System was be installed and maintained after the actual implementation. For the proper installation of the Health Center Patient Information System, one must take consideration the hardware and software requirements were considered. Similarly, this was also the stage where the supports about the software were provided. The clients/users was required to have a training to enable to them familiarize fully the whole system and how to operate the system properly.

Integration and System Testing Phase

This is the phase where the Health Center Patient Information System was integrated and tested after it was implemented and undergone testing to the intended users. It informs the developer if there are any suggestions, errors, and if the systems functionality worked well.

Operation and Maintenance Phase

This is where the operations and maintaining the system happens; this will be the time that all errors are corrected, weak areas are pointed out to be strengthened as well as security issues. These problems arise after the system has been tested by the practical users so the issues related to the system can be solved and upgraded for efficient and effectiveness of the system.