Patient Information System Database Model

Patient Information System Database Model

This article will provide you with the list of database tables for the system entitled patient information system. The content includes the entities or columns of each table and as well as the table structure and the different constraints and relationships of the tables.

tblconsultation (consultation_id, patient_id, complaints, diagnosis, treatment, date_recorded, doctor_id, user_id)

Description: consultation table stores the information of the patient and as well as the consultation or health history, it is the compilation report for every time the patient ask the advice of his/her doctor. The table has 8 fields or entities; (1) consultation_id stands for the primary key of the table, (2) patient_id is a foreign key that links to the information of patient in the tblpatient, (3) complaints or the list of illnesses of the patient, (4) diagnosis is the initial findings of the doctor, (5) treatment refers to the initial advice of the doctor to the patient, (6) date_recorded is the date of data entry to the system, (7) doctor_id is the foreign key that links to the information of doctor in the tbldoctor, and (8) user_id the one who encodes the data to the system.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblconsultation` (
`consultation_id` int(11) NOT NULL AUTO_INCREMENT,
`patient_id` int(11) NOT NULL,
`complaints` varchar(100) NOT NULL,
`diagnosis` varchar(100) NOT NULL,
`treatment` varchar(100) NOT NULL,
`date_recorded` date NOT NULL,
`doctor_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`consultation_id`),
KEY `patient_id` (`patient_id`,`doctor_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `doctor_id` (`doctor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbldiseaseinfo (disease_id, disease_name, description, user_id)

Description: list of diseases are also recorded in the system that will be used in the several reports. The tbldiseaseinfo has 4 entities; (1) number 1 is the primary key of the table which is the disease_id, (2) next is the disease_name, (3) then the detailed information about the disease represented by the description column, and (4) user_id the one who encodes the data to the system.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbldiseaseinfo` (
`disease_id` int(11) NOT NULL AUTO_INCREMENT,
`disease_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`disease_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbldoctorinfo (doctor_id, name, address, contact, username, password, user_id)

Description: tbldoctorinfo is the database table that stores the information of the doctors. Doctors can access the system and view the list of patients under their supervision or care. Information includes the name, address, contact, and their desired username and password. The system will also record the user who encodes the data to the system.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbldoctorinfo` (
`doctor_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`contact` varchar(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`doctor_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbldoctorspecialization (doctor_specialization_id, doctor_id, specialization_id, user_id)

Description: there are cases that the doctor has multiple specialized areas; it is the reason why tbldoctorspecialization was created in order to address that can kind of scenario. The table has 4 entities; (1) doctor_specialization_id is the primary key, (2) doctor_id is the foreign key that links to the tbldoctor, (3) specialization_id is another foreign key that links to the tblspecialization, and lastly (4) user_id the one who encodes the data to the system.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbldoctorspecialization` (
`doctor_specialization_id` int(11) NOT NULL AUTO_INCREMENT,
`doctor_id` int(11) NOT NULL,
`specialization_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`doctor_specialization_id`),
KEY `doctor_id` (`doctor_id`,`specialization_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `specialization_id` (`specialization_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblmedicine (medicine_id, medicine_name, description, user_id)

Description: list of medicines are also part of the system, the information of medicines which includes the name and description are stored in the tblmedicine table.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblmedicine` (
`medicine_id` int(11) NOT NULL AUTO_INCREMENT,
`medicine_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`medicine_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblmedicinefordisease (med_disease_id, disease_id, medicine_id, , user_id)

Description: not all of the time the users are able to determine the proper medicine to a certain illness and in order to avoid confusion and to give the proper medication to the patient the system has the records that will identify the list of possible medicine for a specific case or illness. Those information are stored in the tblmedicinefordisease.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblmedicinefordisease` (
`med_disease_id` int(11) NOT NULL AUTO_INCREMENT,
`disease_id` int(11) NOT NULL,
`medicine_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`med_disease_id`),
KEY `disease_id` (`disease_id`,`medicine_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `medicine_id` (`medicine_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpatientinfo (patient_id, name, address, contact, gender, birthdate, age, user_id)

Description: information of the patient must be encoded in the system for proper monitoring of consultation and to provide a real-time health records.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpatientinfo` (
`patient_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`contact` varchar(11) NOT NULL,
`gender` int(1) NOT NULL,
`birthdate` date NOT NULL,
`age` int(3) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`patient_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblprescription (prescription_id, consultation_id, medicine_id, prescription_details, user_id)

Description: prescription table has 5 entities; (1) prescription_id is the primary key of the table, (2) consultation_id is the column that links to the consultation table, (3) information of the medicine(s) are represented by the foreign key medicine_id, (4) prescription_details is the recommended dosage of the medicine, and (5) user_id the one who encodes the data to the system.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblprescription` (
`prescription_id` int(11) NOT NULL AUTO_INCREMENT,
`consultation_id` int(11) NOT NULL,
`medicine_id` int(11) NOT NULL,
`prescription_details` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`prescription_id`),
KEY `consultation_id` (`consultation_id`,`medicine_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `medicine_id` (`medicine_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblspecialization (specialization_id, specialization_name, description, user_id)

Description: specialization of doctors refers to their expertise. The list of expertise or specialization are stored in the tblspecialization.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblspecialization` (
`specialization_id` int(11) NOT NULL AUTO_INCREMENT,
`specialization_name` varchar(50) NOT NULL,
`description` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`specialization_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (id, username, password, user_type, name, address, contact)

Description: users of the system will be categorized into two; (1) the administrator has all the privilege to access all modules of the system, (2) the staff accounts is limited and it will be based on the admin what are the modules and actions the staff can operate and access.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`user_type` int(1) NOT NULL,
`name` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`contact` varchar(11) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables

—
— Constraints for table `tblconsultation`
—
ALTER TABLE `tblconsultation`
ADD CONSTRAINT `tblconsultation_ibfk_3` FOREIGN KEY (`doctor_id`) REFERENCES `tbldoctorinfo` (`doctor_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblconsultation_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblconsultation_ibfk_2` FOREIGN KEY (`patient_id`) REFERENCES `tblpatientinfo` (`patient_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbldiseaseinfo`
—
ALTER TABLE `tbldiseaseinfo`
ADD CONSTRAINT `tbldiseaseinfo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbldoctorinfo`
—
ALTER TABLE `tbldoctorinfo`
ADD CONSTRAINT `tbldoctorinfo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbldoctorspecialization`
—
ALTER TABLE `tbldoctorspecialization`
ADD CONSTRAINT `tbldoctorspecialization_ibfk_3` FOREIGN KEY (`specialization_id`) REFERENCES `tblspecialization` (`specialization_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbldoctorspecialization_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tbldoctorspecialization_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `tbldoctorinfo` (`doctor_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblmedicine`
—
ALTER TABLE `tblmedicine`
ADD CONSTRAINT `tblmedicine_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblmedicinefordisease`
—
ALTER TABLE `tblmedicinefordisease`
ADD CONSTRAINT `tblmedicinefordisease_ibfk_3` FOREIGN KEY (`disease_id`) REFERENCES `tbldiseaseinfo` (`disease_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblmedicinefordisease_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblmedicinefordisease_ibfk_2` FOREIGN KEY (`medicine_id`) REFERENCES `tblmedicine` (`medicine_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblpatientinfo`
—
ALTER TABLE `tblpatientinfo`
ADD CONSTRAINT `tblpatientinfo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblprescription`
—
ALTER TABLE `tblprescription`
ADD CONSTRAINT `tblprescription_ibfk_3` FOREIGN KEY (`medicine_id`) REFERENCES `tblmedicine` (`medicine_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblprescription_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblprescription_ibfk_2` FOREIGN KEY (`consultation_id`) REFERENCES `tblconsultation` (`consultation_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblspecialization`
—
ALTER TABLE `tblspecialization`
ADD CONSTRAINT `tblspecialization_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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.