Hospital Management ER Diagram

A hospital schema connects people, resources and billing: departments group doctors, appointments pair a patient with a doctor, and prescriptions issued during an appointment carry their medication lines. Admissions record inpatient stays, and each admission produces one invoice.

The schema keeps clinical encounters and inpatient stays apart. An appointment is a scheduled outpatient contact, while admissions records a patient occupying a bed over a date range and carries its own daily_rate. A prescription belongs to an appointment through appointment_id, which is UNIQUE here: one encounter, one prescription sheet, with prescription_items holding the individual medication lines. That separation lets a single visit prescribe several drugs without duplicating the appointment, and keeps the header notes and issued_at timestamp in one place. It also means a prescription can be amended without touching the appointment schedule or status.

Read the diagram left to right: departments own doctors, patients connect to doctors through appointments, and prescriptions continue the encounter chain down to medications. prescription_items also references medications directly, so a medication appears in many prescriptions over time, and its quantity column carries a CHECK that rejects zero or negative values. On the inpatient side, admissions points at both patient_id and doctor_id, and invoices.admission_id is UNIQUE, turning the usual one-to-many into one invoice per stay. The room_number and bed_number columns keep the physical bed assignment on the admission. Each edge therefore answers a different operational question.

Two operational rules matter here. Deleting a patient with appointments should not erase clinical history, so appointments.patient_id has no cascade; deleting a doctor is equally blocked while appointments or admissions still reference them. Prescriptions and their items, by contrast, cascade from the appointment because they are detail records. The admitted_at and discharged_at pair also needs a CHECK that discharged_at is null or later than admission, otherwise a typo can produce a negative length of stay — and invoice totals that nobody can explain. None of these rules can be enforced from the application layer alone.

The most useful extension is a normalized rooms table (room_number, room_type, floor, capacity), referenced by admissions.room_id; it makes occupancy reports, ward assignments and daily cleaning schedules possible, which a free-text room number cannot support, and it lets housekeeping mark a bed out of service without editing an admission. A separate vitals or lab_results table keyed by appointment gives clinicians measurements without adding columns to the encounter. Finally, splitting invoices into invoice_items (description, amount) lets one bill cover bed days, procedures and medication charges, instead of storing a single opaque amount.

The diagram

Tables (9)

Relationships (9)

• 1:Ndepartments.id→doctors.department_id
• 1:Npatients.id→appointments.patient_id
• 1:Ndoctors.id→appointments.doctor_id
• 1:1appointments.id→prescriptions.appointment_idON DELETE CASCADE
• 1:Nprescriptions.id→prescription_items.prescription_idON DELETE CASCADE
• 1:Nmedications.id→prescription_items.medication_id
• 1:Npatients.id→admissions.patient_id
• 1:Ndoctors.id→admissions.doctor_id
• 1:1admissions.id→invoices.admission_idON DELETE CASCADE

The SQL

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  floor INTEGER
);

CREATE TABLE doctors (
  id SERIAL PRIMARY KEY,
  department_id INTEGER REFERENCES departments(id),
  full_name VARCHAR(120) NOT NULL,
  speciality VARCHAR(80) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE patients (
  id SERIAL PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  date_of_birth DATE NOT NULL,
  email VARCHAR(255) UNIQUE,
  phone VARCHAR(30)
);

CREATE TABLE appointments (
  id SERIAL PRIMARY KEY,
  patient_id INTEGER NOT NULL REFERENCES patients(id),
  doctor_id INTEGER NOT NULL REFERENCES doctors(id),
  scheduled_for TIMESTAMP NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'scheduled',
  notes TEXT
);

CREATE TABLE medications (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  strength VARCHAR(40)
);

CREATE TABLE prescriptions (
  id SERIAL PRIMARY KEY,
  appointment_id INTEGER NOT NULL UNIQUE REFERENCES appointments(id) ON DELETE CASCADE,
  issued_at TIMESTAMP NOT NULL DEFAULT now(),
  notes TEXT
);

CREATE TABLE prescription_items (
  id SERIAL PRIMARY KEY,
  prescription_id INTEGER NOT NULL REFERENCES prescriptions(id) ON DELETE CASCADE,
  medication_id INTEGER NOT NULL REFERENCES medications(id),
  dosage VARCHAR(80) NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  UNIQUE (prescription_id, medication_id)
);

CREATE TABLE admissions (
  id SERIAL PRIMARY KEY,
  patient_id INTEGER NOT NULL REFERENCES patients(id),
  doctor_id INTEGER NOT NULL REFERENCES doctors(id),
  room_number VARCHAR(10) NOT NULL,
  bed_number VARCHAR(10),
  admitted_at TIMESTAMP NOT NULL DEFAULT now(),
  discharged_at TIMESTAMP,
  daily_rate DECIMAL(10,2) NOT NULL CHECK (daily_rate > 0),
  CHECK (discharged_at IS NULL OR discharged_at >= admitted_at)
);

CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  admission_id INTEGER NOT NULL UNIQUE REFERENCES admissions(id) ON DELETE CASCADE,
  amount DECIMAL(12,2) NOT NULL CHECK (amount >= 0),
  status VARCHAR(20) NOT NULL DEFAULT 'open',
  issued_at TIMESTAMP NOT NULL DEFAULT now()
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides