Centralized Hospital Outpatient Services Database
From registration to billing, in one relational model.
A relational database designed to centralize hospital outpatient service information, including patient, doctor, appointment, consultation, laboratory, prescription, and billing-related data.
Status: A database and system design project. It is not deployed in a hospital.
Entity relationship diagram: 7 entities · 6 relationships.
- Patient books Appointment (one to many).
- Doctor attends Appointment (one to many).
- Appointment results in Consultation (one to one).
- Consultation orders Laboratory (one to many).
- Consultation issues Prescription (one to many).
- Appointment is billed by Billing (one to one).
- Patient
- patient_id, primary key
- Doctor
- doctor_id, primary key
- Appointment
- appointment_id, primary key
- patient_id, foreign key
- doctor_id, foreign key
- Consultation
- consultation_id, primary key
- appointment_id, foreign key
- Laboratory
- lab_record_id, primary key
- consultation_id, foreign key
- Prescription
- prescription_id, primary key
- consultation_id, foreign key
- Billing
- billing_id, primary key
- appointment_id, foreign key
Design documents3 to be added
ScreenshotEntity Relationship Diagram
To be added
ScreenshotRelational schema
To be added
ScreenshotData dictionary
To be added
Problem & solution
- Problem
- Hospital outpatient information can become fragmented when registration, appointments, consultations, laboratory records, prescriptions, and billing are managed separately.
- Solution
- Design a centralized relational database that connects the major entities and maintains structured relationships between them. The schema is normalized so each fact is stored once and referenced by key.
Technologies
2 technologies
- PostgreSQL / MySQLRelational database engine
- SQL
Architecture
Outpatient data flow
Patient
Registration
Appointment
Patient + doctor + schedule
Consultation
Findings for the visit
Lab · Prescription
Orders from the consultation
Billing
Charges for the visit
Normalization
- UNF
Unnormalized form
The starting point: repeating groups and multi-valued fields.
- 1NF
First normal form
Every field holds one atomic value, with no repeating groups.
- 2NF
Second normal form
Every non-key attribute depends on the whole key.
- 3NF
Third normal form
No transitive dependencies between non-key attributes.
Keys and relationships
Primary keys identify each record, and foreign keys connect patients, doctors, appointments, consultations and their outcomes.
Key technical concepts
- Entity Relationship Diagram
- Relational schema
- Database normalization
- 1NF
- 2NF
- 3NF
- Primary keys
- Foreign keys
- Relationships
- Data dictionary
- SQL