ErdDocs

Example schemas / Hospital Management System

Hospital Management System ER diagram

A hospital management system database revolves around two event tables: appointments (outpatient visits booked with a doctor) and admissions (inpatient stays in a ward). Both point at patients; prescriptions are written per appointment and reference a medication catalog.

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • patients — mrn is the working identifier; the surrogate id stays internal.
  • doctors / departments — A plain one-to-many; specialty lives on the doctor, not the department.
  • appointments — The outpatient event: patient, doctor, time, outcome.
  • admissions — The inpatient event: open-ended stay, discharged_at NULL while admitted.
  • medications / prescriptions — Prescriptions hang off the appointment — the visit is the clinical context for the order.

Design decisions worth copying

  • Appointments and admissions are separate tables, not one "visits" table with a type column — they share almost no columns, and merging them breeds NULLs with per-type meaning.
  • prescriptions references the appointment rather than patient+doctor directly: the visit is the audit context, and both other links are reachable through it.
  • discharged_at nullable = "still in the ward" is a queryable fact, not a status string to keep in sync.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

CREATE TABLE departments (
    id    bigserial   PRIMARY KEY,
    name  varchar(80) NOT NULL UNIQUE
);

CREATE TABLE doctors (
    id             bigserial    PRIMARY KEY,
    department_id  bigint       NOT NULL REFERENCES departments(id),
    full_name      varchar(120) NOT NULL,
    specialty      varchar(80)  NOT NULL
);

CREATE TABLE patients (
    id          bigserial    PRIMARY KEY,
    mrn         varchar(20)  NOT NULL UNIQUE,
    full_name   varchar(120) NOT NULL,
    born_on     date         NOT NULL,
    blood_type  varchar(3)
);
COMMENT ON COLUMN patients.mrn IS 'Medical record number — the ID staff actually use';

CREATE TABLE appointments (
    id          bigserial   PRIMARY KEY,
    patient_id  bigint      NOT NULL REFERENCES patients(id),
    doctor_id   bigint      NOT NULL REFERENCES doctors(id),
    at          timestamptz NOT NULL,
    status      varchar(20) NOT NULL DEFAULT 'booked',
    notes       text
);
COMMENT ON COLUMN appointments.status IS 'booked -> seen, or no_show / cancelled';

CREATE TABLE admissions (
    id           bigserial   PRIMARY KEY,
    patient_id   bigint      NOT NULL REFERENCES patients(id),
    ward         varchar(40) NOT NULL,
    admitted_at  timestamptz NOT NULL,
    discharged_at timestamptz
);
COMMENT ON COLUMN admissions.discharged_at IS 'NULL while the patient is in the ward';

CREATE TABLE medications (
    id    bigserial    PRIMARY KEY,
    name  varchar(120) NOT NULL,
    form  varchar(40)  NOT NULL
);
COMMENT ON COLUMN medications.form IS 'tablet / injection / syrup …';

CREATE TABLE prescriptions (
    id              bigserial  PRIMARY KEY,
    appointment_id  bigint     NOT NULL REFERENCES appointments(id),
    medication_id   bigint     NOT NULL REFERENCES medications(id),
    dosage          varchar(60) NOT NULL,
    days            int        NOT NULL
);

FAQ

Where would lab tests and results go?

A lab_orders table referencing appointments (like prescriptions does) and a lab_results table referencing lab_orders one-to-one or one-to-many, depending on whether panels report per-component rows.

Is one doctors table enough for surgeons, nurses and staff?

For a study schema, a staff table with a role column works. Real systems split clinical staff by capability because rostering, licensing and billing differ — start simple, split when a query forces it.

More example schemas