Medical Record System Database Design

Title:  Medical Record System Database Design

Description:

The researcher came up with the study by conducting an observation with regards to the clinics operation and protocol. The Clinic started using the recording of patient’s information thru MS Excel and using manual file folder recording since the start of the operation. By that reason the researcher propose a more convenient way of organizing and managing the patient’s record by means of developing a lan-based system thru EMR (Electronic Medical Record) Technology. The researcher will use Visual Basic for programming and MySQL for the database of the project.

The study is focused on developing the patient’s record. Patient Integrated system is a LAN-Based System that will focus on keeping all the patient records.

The system will consist but not limited to the following modules:

• Patients Record Keeping
• Billings
• Laboratory Results
• Consultation Fee
• Medicine Inventory
• Doctor’s Prescriptions
• Doctor’s in Charge

The proposed system also includes a terminal for the patient to view the following information:

• Patient Medical History
• Reservations of Appointments
• Reservation of Laboratory Request
• Doctor’s Prescription
• Patient Billings

Database Schema with description of table:

Patient table stores basic information such as lastname, firstname, gender, age and contact. Each patient will be given a username and password so that they can login to the system and view their health records.

tblPatient (id, recordNo, lname, fname, mname, gender, age, address, contact, username, password)

Queue table stores patient information and the date.

tblQue (id, patientid, mdate)

doctor table consists of fields such as name, address, specialization, username and password.

tblDoctor (id, doctorrecono, doctorName, address, contact, fieldOfSpecialization, username, password)

tblcases table holds the records of illnesses.

tblCases (id, caseName, description)

Medicine table stores information about the name of the medicine, purpose and quantity

tblMedicine (id, medicineName, purpose, initialQty, dateDelivered)

consultation table stores information about patient, the attending doctor or physician, diagnostic and treatment and as well as the date and the blood pressure and weight.

tblConsultation (id, patientid, diagnostic, treatment, bp, weight, attendingDoctorid, dateRecorded)

tblgivenmedicine is the table for storing the list of medicines given to the patient during the consultation.

tblGivenMedicine(id, consultationid, medicineid, qty, amount, mstatus(sold or not))

laboratory record table will store the records of the patients laboratory result and the date it was released.

tblLaboratoryRecord (id, patientid, labName, resultStatus, dateReleased)

billing table will store records of billing information such as the patient name, the amount and the date it was released.

tblBilling (id, patientid, billTo, billAmount, dateRecorded, tblgivenmedicine.consultationid)

tblclerk stores information about clerk name, address, contact, username and password

tblClerk (id, clerkrecno, fullname, address, contact, username, password)

Table Relationship:

The relationship is used to cross reference information between tables.

Sample Queries:

The query below will display the list or records of consultation (patient info, diagnostic and the attending physician):

SELECT tblpatient.lname, tblconsultation.diagnostic, tblconsultation.treatment, tblconsultation.bpressure, tblconsultation.cweight, tbldoctor.doctorname, tblconsultation.daterecorded

FROM tbldoctor INNER JOIN (tblpatient INNER JOIN tblconsultation ON tblpatient.id = tblconsultation.patientid) ON tbldoctor.id = tblconsultation.doctorid

GROUP BY tblpatient.lname, tblconsultation.diagnostic, tblconsultation.treatment, tblconsultation.bpressure, tblconsultation.cweight, tbldoctor.doctorname, tblconsultation.daterecorded

ORDER BY tblconsultation.daterecorded DESC;

Result:

System Flow/Features:

Features

1. The user will encode the following records:
   1. Patient info
   2. Doctor info
   3. Cases/Diseases
   4. Medicine
2. The user will then add the patient to que list
3. The user will record the consultation info
4. The user will record the Laboratory result
5. The user will issue a billing statement
6. The patient can view his/her medical records

Menu Structure

Administrator

• Manage Clerk
• Manage Doctors
• Manage Medicine

Clerk/Users

• Manage Patient
• Manage Cases
• Manage Medicine
• Record Consultation
• Record Laboratory Result
• Manage Billing
• View Cue
• Change Password

Doctors

• View Cue
• View Consultation Archive
• Change Password
• Lab Result

Nurse

• Manage patient
• Manage cases
• Manage medicine
• Manage billing
• Change password
• View cue

Accounting

• Manage patient
• Manage billing
• Manage medicine

Medical Technician

• View patient
• Manage lab request/result change pass

For comments, suggestions and questions, you can contact us via our facebook page

https://www.inettutor.com/