Hospital Management System Database Schema database schema

What this schema models

A complete hospital management system database design, built the way real clinical systems are built rather than the way textbook diagrams are drawn. 38 tables, 22 enums and 67 relationships spanning departments and staff, patient records, insurance policies, allergies, wards and beds, appointment scheduling, admissions and discharges, vital signs, clinical notes, ICD and CPT coding, pharmacy and medication administration, lab and imaging orders, and the full billing and insurance-claims chain. THE FOUR DESIGN RULES 1. The ENCOUNTER is the spine. Almost every clinical fact — vitals, notes, diagnoses, procedures, prescriptions, lab orders, imaging — hangs off an encounter, so any record can always be traced back to when and why the patient was seen. Appointments are intentions; an encounter is created only when the patient actually arrives, which is why walk-ins and emergencies have a null appointment_id. 2. Clinical records are append-only. A corrected lab result is a NEW row pointing at the one it supersedes, never an UPDATE over the original. An unsigned clinical note is a draft; amendments are new rows. Regulators ask for the old value. 3. Coded concepts live in catalog tables. diagnosis_codes and procedure_codes hold ICD-10 and CPT entries; free text is for humans, codes are for reporting and claims. 4. Money is stored in minor units as integers. Invoice totals are snapshotted at issue so a later price-list change cannot rewrite an issued invoice. DETAILS WORTH STEALING - One staff table for everyone. Doctors, nurses, technicians and pharmacists differ by role, not by table. - The MRN is what humans quote; the uuid is what foreign keys use, so reissuing a medical record number never breaks history. - beds.status is a cached convenience — the authoritative occupancy answer is an open row in bed_assignments, which also lets a patient move between beds mid-stay. - Insurance policies carry a priority, so claims bill the primary payer first and the secondary gets its own claim row after adjudication. - medication_administrations is a proper MAR, with a witness column required for controlled substances. - Imaging stores an accession_number that ties the row to the PACS. Pixels never live in this database. WHAT YOU GET Clean DBML that imports directly into DB Planner, plus a laid-out board with cards colour-coded by zone — staff, patients, insurance, facilities, encounter spine, clinical record, coding, pharmacy, diagnostics and billing — and sticky notes explaining the reasoning behind each group. Every table carries a Note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building an HMS or EHR, students who need a realistic hospital database ER diagram for a DBMS project, and health-tech teams looking for a reference model before their first migration. This is a schema design reference, not a certified clinical system — validate against your own regulatory requirements before production use.

Tables (38)

Relationships (67)

• admissions.admitting_staff_id references staff.id many to one
• admissions.encounter_id references encounters.id many to one
• admissions.patient_id references patients.id many to one
• appointments.department_id references departments.id many to one
• appointments.patient_id references patients.id many to one
• appointments.staff_id references staff.id many to one
• bed_assignments.admission_id references admissions.id many to one
• bed_assignments.bed_id references beds.id many to one
• beds.room_id references rooms.id many to one
• clinical_notes.author_staff_id references staff.id many to one
• clinical_notes.encounter_id references encounters.id many to one
• doctor_schedules.department_id references departments.id many to one
• doctor_schedules.staff_id references staff.id many to one
• doctor_specialties.specialty_id references specialties.id many to one
• doctor_specialties.staff_id references staff.id many to one
• emergency_contacts.patient_id references patients.id many to one
• encounter_diagnoses.diagnosed_by_staff_id references staff.id many to one
• encounter_diagnoses.diagnosis_code_id references diagnosis_codes.id many to one
• encounter_diagnoses.encounter_id references encounters.id many to one
• encounter_procedures.encounter_id references encounters.id many to one
• encounter_procedures.performed_by_staff_id references staff.id many to one
• encounter_procedures.procedure_code_id references procedure_codes.id many to one
• encounters.appointment_id references appointments.id many to one
• encounters.attending_staff_id references staff.id many to one
• encounters.department_id references departments.id many to one
• encounters.patient_id references patients.id many to one
• imaging_studies.encounter_id references encounters.id many to one
• imaging_studies.ordered_by_staff_id references staff.id many to one
• imaging_studies.performed_by_staff_id references staff.id many to one
• insurance_claims.invoice_id references invoices.id many to one
• insurance_claims.policy_id references patient_insurance_policies.id many to one
• invoice_lines.encounter_procedure_id references encounter_procedures.id many to one
• invoice_lines.invoice_id references invoices.id many to one
• invoices.encounter_id references encounters.id many to one
• invoices.patient_id references patients.id many to one
• lab_order_items.lab_order_id references lab_orders.id many to one
• lab_order_items.lab_test_id references lab_tests.id many to one
• lab_orders.encounter_id references encounters.id many to one
• lab_orders.ordered_by_staff_id references staff.id many to one
• lab_results.lab_order_item_id references lab_order_items.id many to one
• lab_results.supersedes_result_id references lab_results.id many to one
• lab_results.verified_by_staff_id references staff.id many to one
• medication_administrations.administered_by_staff_id references staff.id many to one
• medication_administrations.admission_id references admissions.id many to one
• medication_administrations.prescription_item_id references prescription_items.id many to one
• medication_administrations.witnessed_by_staff_id references staff.id many to one
• patient_addresses.patient_id references patients.id many to one
• patient_allergies.allergen_id references allergens.id many to one
• patient_allergies.patient_id references patients.id many to one
• patient_allergies.recorded_by_staff_id references staff.id many to one
• patient_insurance_policies.patient_id references patients.id many to one
• patient_insurance_policies.provider_id references insurance_providers.id many to one
• payments.claim_id references insurance_claims.id many to one
• payments.invoice_id references invoices.id many to one
• prescription_items.medication_id references medications.id many to one
• prescription_items.prescription_id references prescriptions.id many to one
• prescriptions.encounter_id references encounters.id many to one
• prescriptions.patient_id references patients.id many to one
• prescriptions.prescriber_staff_id references staff.id many to one
• referrals.encounter_id references encounters.id many to one
• referrals.referred_to_staff_id references staff.id many to one
• referrals.referring_staff_id references staff.id many to one
• referrals.specialty_id references specialties.id many to one
• rooms.department_id references departments.id many to one
• staff.department_id references departments.id many to one
• vital_signs.encounter_id references encounters.id many to one
• vital_signs.recorded_by_staff_id references staff.id many to one

How to use this template

1. Open the diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy.
3. Export it as PostgreSQL, MySQL, or MSSQL DDL, or as C# models.

Frequently asked questions

What is in the Hospital Management System Database Schema schema?

38 tables and 67 relationships, across 272 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, PostgreSQL, MySQL, or MSSQL DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.