← All schemas
Schema Library

Hospital Management System Database Schema

A hospital management system connects patient care to billing in twelve tables. Patients book appointments with doctors; visits produce medical records; records carry prescriptions that reference a medications catalog; inpatient stays flow through admissions into rooms and beds; and every visit or stay rolls up into bills, which are settled by individual payments. Two modeling choices keep the schema tight: a single staff table covers doctors, nurses, and admin via a role column (so appointments reference staff, not doctors), and billing splits into bills (the aggregate) and payments (each settlement attempt) so partial and failed payments are representable.

Written by Varun Krishnan · Founder, dbdiagramr

Published · Updated

Source: Original design by dbdiagramr, modeled on standard DBMS textbook schemas

Tables
12
Core table
patients
Care flow
Patient -> Appointment -> Record -> Bill
Hospital Management System database schema diagram showing every table, column, and foreign key

Scroll horizontally to see the full diagram. Click it to open at full size.

Open in DBdiagramr to edit →

Embed this diagram

Paste this HTML into a README, docs page, or blog post. The image links back to the full schema page.

<a href="https://www.dbdiagramr.space/schema/hospital-management-system">
  <img src="https://www.dbdiagramr.space/schemas/hospital-management-system-er-diagram.svg" alt="Hospital Management System Database Schema (PostgreSQL)" width="1634" height="1189" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/hospital-management-system">Hospital Management System Database Schema (PostgreSQL)</a> by dbdiagramr</p>

Tables in the Hospital Management System schema

ColumnTypeNullableKey
departments
iduuidNoPK
namevarcharNo-
buildingvarcharYes-
floorintegerYes-
patients
iduuidNoPK
first_namevarcharNo-
last_namevarcharNo-
date_of_birthdateNo-
gendervarcharYes-
blood_groupvarchar(3)Yes-
phonevarcharNo-
emailvarcharYes-
addresstextYes-
emergency_contactvarcharYes-
created_attimestamptzNo-
staff
iduuidNoPK
first_namevarcharNo-
last_namevarcharNo-
rolevarcharNo-
department_iduuidYesFK → departments.id
specializationvarcharYes-
phonevarcharYes-
hire_datedateYes-
rooms
iduuidNoPK
numbervarcharNo-
room_typevarcharNo-
capacityintegerNo-
beds
iduuidNoPK
room_iduuidNoFK → rooms.id
bed_numbervarcharNo-
statusvarcharNo-
appointments
iduuidNoPK
patient_iduuidNoFK → patients.id
doctor_iduuidNoFK → staff.id
department_iduuidYesFK → departments.id
appointment_attimestamptzNo-
statusvarcharNo-
notestextYes-
medical_records
iduuidNoPK
patient_iduuidNoFK → patients.id
doctor_iduuidNoFK → staff.id
appointment_iduuidYesFK → appointments.id
recorded_attimestamptzNo-
diagnosistextNo-
symptomstextYes-
treatment_plantextYes-
medications
iduuidNoPK
namevarcharNo-
unitvarcharYes-
pricenumeric(10,2)Yes-
prescriptions
iduuidNoPK
record_iduuidNoFK → medical_records.id
medication_iduuidNoFK → medications.id
dosagevarcharNo-
frequencyvarcharNo-
duration_daysintegerYes-
admissions
iduuidNoPK
patient_iduuidNoFK → patients.id
bed_iduuidNoFK → beds.id
admitted_attimestamptzNo-
discharged_attimestamptzYes-
admission_typevarcharYes-
bills
iduuidNoPK
patient_iduuidNoFK → patients.id
admission_iduuidYesFK → admissions.id
appointment_iduuidYesFK → appointments.id
total_amountnumeric(10,2)No-
insurance_coverednumeric(10,2)No-
statusvarcharNo-
billed_attimestamptzNo-
payments
iduuidNoPK
bill_iduuidNoFK → bills.id
amountnumeric(10,2)No-
methodvarcharNo-
paid_attimestamptzNo-

Table relationships

16 relationships connect the tables in this schema. Each entry shows the child columns, the parent columns they reference, and the cardinality.

One-to-many

staff.department_id → departments.id

One department can have many staffs (staff.department_id -> departments.id, optional).

One-to-many

beds.room_id → rooms.id

One room can have many beds (beds.room_id -> rooms.id, required).

One-to-many

appointments.patient_id → patients.id

One patient can have many appointments (appointments.patient_id -> patients.id, required).

One-to-many

appointments.doctor_id → staff.id

One staff can have many appointments (appointments.doctor_id -> staff.id, required).

One-to-many

appointments.department_id → departments.id

One department can have many appointments (appointments.department_id -> departments.id, optional).

One-to-many

medical_records.patient_id → patients.id

One patient can have many medical records (medical_records.patient_id -> patients.id, required).

One-to-many

medical_records.doctor_id → staff.id

One staff can have many medical records (medical_records.doctor_id -> staff.id, required).

One-to-many

medical_records.appointment_id → appointments.id

One appointment can have many medical records (medical_records.appointment_id -> appointments.id, optional).

One-to-many

prescriptions.record_id → medical_records.id

One medical record can have many prescriptions (prescriptions.record_id -> medical_records.id, required).

One-to-many

prescriptions.medication_id → medications.id

One medication can have many prescriptions (prescriptions.medication_id -> medications.id, required).

One-to-many

admissions.patient_id → patients.id

One patient can have many admissions (admissions.patient_id -> patients.id, required).

One-to-many

admissions.bed_id → beds.id

One bed can have many admissions (admissions.bed_id -> beds.id, required).

One-to-many

bills.patient_id → patients.id

One patient can have many bills (bills.patient_id -> patients.id, required).

One-to-many

bills.admission_id → admissions.id

One admission can have many bills (bills.admission_id -> admissions.id, optional).

One-to-many

bills.appointment_id → appointments.id

One appointment can have many bills (bills.appointment_id -> appointments.id, optional).

One-to-many

payments.bill_id → bills.id

One bill can have many payments (payments.bill_id -> bills.id, required).

Common queries

These PostgreSQL queries run against the schema shown on this page.

Appointment schedule

Upcoming appointments with the patient, doctor, and department.

select a.appointment_at, p.first_name || ' ' || p.last_name as patient,
       st.first_name || ' ' || st.last_name as doctor,
       d.name as department, a.status
from appointments a
join patients p on p.id = a.patient_id
join staff st on st.id = a.doctor_id
left join departments d on d.id = a.department_id
where a.appointment_at >= now()
order by a.appointment_at;

Outstanding bills

Bills with the amount paid so far and the remaining balance.

select b.id, p.first_name || ' ' || p.last_name as patient,
       b.total_amount,
       coalesce(sum(pay.amount), 0) as paid,
       b.total_amount - coalesce(sum(pay.amount), 0) as balance
from bills b
join patients p on p.id = b.patient_id
left join payments pay on pay.bill_id = b.id
where b.status <> 'paid'
group by b.id, p.first_name, p.last_name, b.total_amount
order by balance desc;

Current admissions

Inpatients with their room and bed.

select p.first_name || ' ' || p.last_name as patient,
       r.number as room, b.bed_number, a.admitted_at
from admissions a
join patients p on p.id = a.patient_id
join beds b on b.id = a.bed_id
join rooms r on r.id = b.room_id
where a.discharged_at is null
order by a.admitted_at;

Prescriptions per medical record

Every medication prescribed during a visit.

select mr.id as record_id, m.name as medication,
       pr.dosage, pr.frequency, pr.duration_days
from prescriptions pr
join medical_records mr on mr.id = pr.record_id
join medications m on m.id = pr.medication_id
order by mr.id, m.name;

Frequently asked questions

Related schema diagrams

Visualize your own database

Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.

Try it free →