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.

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

Hover a table to light up its relations, and click to pin them.

What each table does

  • patientsmrn is the working identifier; the surrogate id stays internal.
  • doctors / departmentsA plain one-to-many; specialty lives on the doctor, not the department.
  • appointmentsThe outpatient event: patient, doctor, time, outcome.
  • admissionsThe inpatient event: an open-ended stay, with discharged_at NULL while admitted.
  • medications / prescriptionsPrescriptions hang off the appointment, because 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.
  • A nullable discharged_at makes "still in the ward" 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. You are free to copy it.

Hospital Management System ER diagram: 7 tables and 6 relations, drawn by ErdDocs from the SQL below.Hospital Management System ER diagram: 7 tables and 6 relations, drawn by ErdDocs from the SQL below.
The diagram ErdDocs generates from this SQL.
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 references appointments, the way prescriptions does. A lab_results table then references 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, and split when a query forces it.

More example schemas