Hospital management system data model

This example models the core records of a hospital: patients and their insurance, doctors and departments, outpatient appointments, inpatient admissions to wards and beds, diagnoses, prescriptions and invoices.

#Design notes

• Outpatient and inpatient care are separate. An appointment is a scheduled visit; an admission is a stay with a bed, an attending doctor and admission and discharge times.
• Diagnoses can come from either. diagnosis links to the patient and, optionally, to the admission or the appointment where it was made. Codes use ICD-10.
• Medications are a formulary. prescription links a patient, a prescribing doctor and a medication, with dosage and dates.
• Most patient data is PII or health data, and is flagged so it can be masked, encrypted or excluded from analytics. Real systems also need audit trails and consent records.
• Specialty and blood type are reference lists with their values.

This is a teaching example, not a clinical system: real hospital data follows standards such as HL7 FHIR and local regulations (for example HIPAA in the United States).

#Diagram

Hospital Management System: 12 entities, 2 reference lists and 17 relationships. Each box is an entity with its attributes (PK marks the primary key); solid lines are relationships in crow's foot notation, dashed lines point to reference lists.

Open this model in EntityModel.com to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or download model.json.

#Entities

#Attributes

#Admission

An inpatient stay, from admission to discharge.

#Appointment

A scheduled outpatient visit.

#Bed

A bed in a ward.

#Department

A clinical department, such as Cardiology.

#Diagnosis

A condition recorded for a patient during an admission or appointment.

#Doctor

A physician on staff.

#Insurance Provider

An insurance company that pays for care.

#Invoice

A bill for an admission or appointment.

#Medication

A drug in the hospital formulary.

#Patient

A person who receives care. Holds personal and health data, so most attributes are PII.

#Prescription

A medication prescribed to a patient by a doctor.

#Ward

An inpatient ward.

#Blood Type

ABO and Rh blood types.

#Specialty

Medical specialties.

#Relationships

#Reference data

#Blood Type

8 values · Source: Example values

#Specialty

6 values · Source: Example values

#SQL DDL (PostgreSQL)

Generated from the model. In the app, Export → Code also writes MySQL, Snowflake and Databricks DDL, DBML, dbt schema.yml and data contracts.

-- Hospital Management System — data model
-- PostgreSQL DDL, generated 2026-11-03
-- Made with EntityModel.com

-- Reference list: Specialty
CREATE TABLE specialty (
  specialty_code TEXT NOT NULL,
  specialty_name TEXT NOT NULL,
  PRIMARY KEY (specialty_code)
);
COMMENT ON TABLE specialty IS 'Medical specialties.';

-- Reference list: Blood Type
CREATE TABLE blood_type (
  blood_type_code TEXT NOT NULL,
  blood_type_name TEXT NOT NULL,
  PRIMARY KEY (blood_type_code)
);
COMMENT ON TABLE blood_type IS 'ABO and Rh blood types.';

CREATE TABLE patient (
  patient_id BIGINT NOT NULL,
  medical_record_number TEXT NOT NULL,
  full_name TEXT NOT NULL,
  date_of_birth DATE NOT NULL,
  sex TEXT,
  phone TEXT,
  blood_type_code TEXT,
  insurance_provider_id INTEGER,
  insurance_member_id TEXT,
  PRIMARY KEY (patient_id)
);
COMMENT ON TABLE patient IS 'A person who receives care. Holds personal and health data, so most attributes are PII.';
COMMENT ON COLUMN patient.medical_record_number IS 'MRN.';

CREATE TABLE department (
  department_id INTEGER NOT NULL,
  department_name TEXT NOT NULL,
  floor INTEGER,
  PRIMARY KEY (department_id)
);
COMMENT ON TABLE department IS 'A clinical department, such as Cardiology.';

CREATE TABLE doctor (
  doctor_id INTEGER NOT NULL,
  license_number TEXT NOT NULL,
  full_name TEXT NOT NULL,
  specialty_code TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  phone TEXT,
  PRIMARY KEY (doctor_id)
);
COMMENT ON TABLE doctor IS 'A physician on staff.';

CREATE TABLE appointment (
  appointment_id BIGINT NOT NULL,
  patient_id BIGINT NOT NULL,
  doctor_id INTEGER NOT NULL,
  scheduled_at TIMESTAMP NOT NULL,
  status TEXT NOT NULL,
  reason TEXT,
  PRIMARY KEY (appointment_id)
);
COMMENT ON TABLE appointment IS 'A scheduled outpatient visit.';
COMMENT ON COLUMN appointment.status IS 'Scheduled, completed, cancelled or no-show.';

CREATE TABLE ward (
  ward_id INTEGER NOT NULL,
  ward_name TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  PRIMARY KEY (ward_id)
);
COMMENT ON TABLE ward IS 'An inpatient ward.';

CREATE TABLE bed (
  bed_id INTEGER NOT NULL,
  bed_number TEXT NOT NULL,
  ward_id INTEGER NOT NULL,
  is_occupied BOOLEAN NOT NULL,
  PRIMARY KEY (bed_id)
);
COMMENT ON TABLE bed IS 'A bed in a ward.';

CREATE TABLE admission (
  admission_id BIGINT NOT NULL,
  patient_id BIGINT NOT NULL,
  attending_doctor_id INTEGER NOT NULL,
  bed_id INTEGER NOT NULL,
  admitted_at TIMESTAMP NOT NULL,
  discharged_at TIMESTAMP,
  PRIMARY KEY (admission_id)
);
COMMENT ON TABLE admission IS 'An inpatient stay, from admission to discharge.';
COMMENT ON COLUMN admission.discharged_at IS 'Empty while admitted.';

CREATE TABLE diagnosis (
  diagnosis_id BIGINT NOT NULL,
  patient_id BIGINT NOT NULL,
  admission_id BIGINT,
  appointment_id BIGINT,
  icd10_code TEXT NOT NULL,
  description TEXT NOT NULL,
  diagnosed_at TIMESTAMP NOT NULL,
  PRIMARY KEY (diagnosis_id)
);
COMMENT ON TABLE diagnosis IS 'A condition recorded for a patient during an admission or appointment.';
COMMENT ON COLUMN diagnosis.icd10_code IS 'ICD-10 diagnosis code.';

CREATE TABLE medication (
  medication_id INTEGER NOT NULL,
  medication_name TEXT NOT NULL,
  form TEXT,
  PRIMARY KEY (medication_id)
);
COMMENT ON TABLE medication IS 'A drug in the hospital formulary.';

CREATE TABLE prescription (
  prescription_id BIGINT NOT NULL,
  patient_id BIGINT NOT NULL,
  doctor_id INTEGER NOT NULL,
  medication_id INTEGER NOT NULL,
  dosage TEXT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE,
  PRIMARY KEY (prescription_id)
);
COMMENT ON TABLE prescription IS 'A medication prescribed to a patient by a doctor.';

CREATE TABLE invoice (
  invoice_id BIGINT NOT NULL,
  invoice_number TEXT NOT NULL,
  patient_id BIGINT NOT NULL,
  admission_id BIGINT,
  total_amount NUMERIC(12,2) NOT NULL,
  insurance_covered_amount NUMERIC(12,2),
  issued_on DATE NOT NULL,
  paid_on DATE,
  PRIMARY KEY (invoice_id)
);
COMMENT ON TABLE invoice IS 'A bill for an admission or appointment.';

CREATE TABLE insurance_provider (
  insurance_provider_id INTEGER NOT NULL,
  provider_name TEXT NOT NULL,
  PRIMARY KEY (insurance_provider_id)
);
COMMENT ON TABLE insurance_provider IS 'An insurance company that pays for care.';

-- Keys between tables
ALTER TABLE patient ADD CONSTRAINT uq_patient_medical_record_number UNIQUE (medical_record_number);
ALTER TABLE doctor ADD CONSTRAINT uq_doctor_license_number UNIQUE (license_number);
ALTER TABLE invoice ADD CONSTRAINT uq_invoice_invoice_number UNIQUE (invoice_number);
ALTER TABLE patient ADD CONSTRAINT fk_patient_blood_type_code FOREIGN KEY (blood_type_code) REFERENCES blood_type (blood_type_code);
ALTER TABLE doctor ADD CONSTRAINT fk_doctor_specialty_code FOREIGN KEY (specialty_code) REFERENCES specialty (specialty_code);
ALTER TABLE patient ADD CONSTRAINT fk_insured_by FOREIGN KEY (insurance_provider_id) REFERENCES insurance_provider (insurance_provider_id);
ALTER TABLE doctor ADD CONSTRAINT fk_doctor_in FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER TABLE appointment ADD CONSTRAINT fk_appointment_for FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE appointment ADD CONSTRAINT fk_appointment_with FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id);
ALTER TABLE ward ADD CONSTRAINT fk_ward_of FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER TABLE bed ADD CONSTRAINT fk_bed_in FOREIGN KEY (ward_id) REFERENCES ward (ward_id);
ALTER TABLE admission ADD CONSTRAINT fk_admits FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE admission ADD CONSTRAINT fk_attended_by FOREIGN KEY (attending_doctor_id) REFERENCES doctor (doctor_id);
ALTER TABLE admission ADD CONSTRAINT fk_assigned_bed FOREIGN KEY (bed_id) REFERENCES bed (bed_id);
ALTER TABLE diagnosis ADD CONSTRAINT fk_diagnosis_of FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE diagnosis ADD CONSTRAINT fk_made_during_admission FOREIGN KEY (admission_id) REFERENCES admission (admission_id);
ALTER TABLE diagnosis ADD CONSTRAINT fk_made_during_appointment FOREIGN KEY (appointment_id) REFERENCES appointment (appointment_id);
ALTER TABLE prescription ADD CONSTRAINT fk_prescribed_to FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE prescription ADD CONSTRAINT fk_prescribed_by FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id);
ALTER TABLE prescription ADD CONSTRAINT fk_prescribes FOREIGN KEY (medication_id) REFERENCES medication (medication_id);
ALTER TABLE invoice ADD CONSTRAINT fk_billed_to FOREIGN KEY (patient_id) REFERENCES patient (patient_id);
ALTER TABLE invoice ADD CONSTRAINT fk_bills_admission FOREIGN KEY (admission_id) REFERENCES admission (admission_id);

-- Reference values
INSERT INTO specialty (specialty_code, specialty_name) VALUES
  ('CARD', 'Cardiology'),
  ('NEUR', 'Neurology'),
  ('ORTH', 'Orthopedics'),
  ('PED', 'Pediatrics'),
  ('ONC', 'Oncology'),
  ('EM', 'Emergency medicine');

INSERT INTO blood_type (blood_type_code, blood_type_name) VALUES
  ('A+', 'A positive'),
  ('A-', 'A negative'),
  ('B+', 'B positive'),
  ('B-', 'B negative'),
  ('AB+', 'AB positive'),
  ('AB-', 'AB negative'),
  ('O+', 'O positive'),
  ('O-', 'O negative');

#Mermaid

Paste into a mermaid code block on GitHub, GitLab or Notion to show the diagram there.

---
title: Hospital Management System
---
%% Made with EntityModel.com
erDiagram
  patient {
    bigint patient_id PK "Patient ID"
    string medical_record_number "MRN."
    string full_name "Full Name"
    date date_of_birth "Date Of Birth"
    string sex "Sex"
    string phone "Phone"
    string blood_type_code FK "Blood Type Code"
    int insurance_provider_id FK "Insurance Provider ID"
    string insurance_member_id "Insurance Member ID"
  }
  department {
    int department_id PK "Department ID"
    string department_name "Department Name"
    int floor "Floor"
  }
  doctor {
    int doctor_id PK "Doctor ID"
    string license_number "License Number"
    string full_name "Full Name"
    string specialty_code FK "Specialty Code"
    int department_id FK "Department ID"
    string phone "Phone"
  }
  appointment {
    bigint appointment_id PK "Appointment ID"
    bigint patient_id FK "Patient ID"
    int doctor_id FK "Doctor ID"
    timestamp scheduled_at "Scheduled At"
    string status "Scheduled, completed, cancelled or no-show."
    string reason "Reason"
  }
  ward {
    int ward_id PK "Ward ID"
    string ward_name "Ward Name"
    int department_id FK "Department ID"
  }
  bed {
    int bed_id PK "Bed ID"
    string bed_number "Bed Number"
    int ward_id FK "Ward ID"
    boolean is_occupied "Is Occupied"
  }
  admission {
    bigint admission_id PK "Admission ID"
    bigint patient_id FK "Patient ID"
    int attending_doctor_id FK "Attending Doctor ID"
    int bed_id FK "Bed ID"
    timestamp admitted_at "Admitted At"
    timestamp discharged_at "Empty while admitted."
  }
  diagnosis {
    bigint diagnosis_id PK "Diagnosis ID"
    bigint patient_id FK "Patient ID"
    bigint admission_id FK "Admission ID"
    bigint appointment_id FK "Appointment ID"
    string icd10_code "ICD-10 diagnosis code."
    string description "Description"
    timestamp diagnosed_at "Diagnosed At"
  }
  medication {
    int medication_id PK "Medication ID"
    string medication_name "Medication Name"
    string form "Form"
  }
  prescription {
    bigint prescription_id PK "Prescription ID"
    bigint patient_id FK "Patient ID"
    int doctor_id FK "Doctor ID"
    int medication_id FK "Medication ID"
    string dosage "Dosage"
    date start_date "Start Date"
    date end_date "End Date"
  }
  invoice {
    bigint invoice_id PK "Invoice ID"
    string invoice_number "Invoice Number"
    bigint patient_id FK "Patient ID"
    bigint admission_id FK "Admission ID"
    decimal total_amount "Total Amount"
    decimal insurance_covered_amount "Insurance Covered Amount"
    date issued_on "Issued On"
    date paid_on "Paid On"
  }
  insurance_provider {
    int insurance_provider_id PK "Insurance Provider ID"
    string provider_name "Provider Name"
  }
  specialty {
    string specialty_code PK "Specialty Code"
    string specialty_name "Specialty Name"
  }
  blood_type {
    string blood_type_code PK "Blood Type Code"
    string blood_type_name "Blood Type Name"
  }
  patient }o--|| insurance_provider : "Insured By"
  doctor }o--|| department : "Works In"
  appointment }o--|| patient : "For"
  appointment }o--|| doctor : "With"
  ward }o--|| department : "Run By"
  bed }o--|| ward : "In"
  admission }o--|| patient : "Admits"
  admission }o--|| doctor : "Attended By"
  admission }o--|| bed : "Assigned"
  diagnosis }o--|| patient : "Of"
  diagnosis }o--|| admission : "During"
  diagnosis }o--|| appointment : "During"
  prescription }o--|| patient : "Prescribed To"
  prescription }o--|| doctor : "Prescribed By"
  prescription }o--|| medication : "Of"
  invoice }o--|| patient : "Billed To"
  invoice }o--|| admission : "Bills"
  patient }o..|| blood_type : "Blood Type Code"
  doctor }o..|| specialty : "Specialty Code"

#Frequently asked questions

#What tables are in a hospital management system database?

Typically patient, doctor, department, appointment, admission, ward, bed, diagnosis, medication, prescription, invoice and insurance provider, with lookup tables such as specialty and blood type.

#How do you model inpatient admissions and beds?

An admission links a patient to a bed and an attending doctor, with admitted and discharged timestamps. Beds belong to wards, and wards to departments, so occupancy can be reported at each level.