DiagramDB
DiagramDB / Hospital Management System ERD

ER Diagram for Hospital Management System

Patients are admitted to wards, seen by doctors who belong to departments, book appointments, receive prescriptions and are billed. Eight tables cover the core of most course assignments and small clinic systems.

All examples
Drawing 8 tables…

Why each relationship is drawn this way

  • department → doctorsA department employs many doctors; each doctor belongs to exactly one department (NOT NULL foreign key).
  • patient → admissionsA patient can be admitted many times over the years, so admissions is its own table instead of columns on patients.
  • ward → admissionsEach stay is in one ward. Bed availability is bed_count minus the admissions with no discharged_at.
  • doctor → appointmentsAppointments resolve the many-to-many between patients and doctors, and carry the time and status of each visit.
  • appointment → prescriptionsA visit can produce several prescription lines; each line belongs to one visit.
  • admission → billsadmission_id is nullable: outpatient bills exist without a hospital stay, so the line shows a zero-or-one circle.

The schema

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  floor INT
);
CREATE TABLE doctors (
  id INT PRIMARY KEY,
  department_id INT NOT NULL REFERENCES departments(id),
  full_name VARCHAR(120) NOT NULL,
  specialty VARCHAR(80),
  phone VARCHAR(30)
);
CREATE TABLE patients (
  id INT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  date_of_birth DATE NOT NULL,
  gender VARCHAR(20),
  phone VARCHAR(30),
  address TEXT
);
CREATE TABLE wards (
  id INT PRIMARY KEY,
  department_id INT NOT NULL REFERENCES departments(id),
  name VARCHAR(60) NOT NULL,
  bed_count INT NOT NULL
);
CREATE TABLE admissions (
  id INT PRIMARY KEY,
  patient_id INT NOT NULL REFERENCES patients(id),
  ward_id INT NOT NULL REFERENCES wards(id),
  attending_doctor_id INT REFERENCES doctors(id),
  admitted_at TIMESTAMP NOT NULL,
  discharged_at TIMESTAMP
);
CREATE TABLE appointments (
  id INT PRIMARY KEY,
  patient_id INT NOT NULL REFERENCES patients(id),
  doctor_id INT NOT NULL REFERENCES doctors(id),
  starts_at TIMESTAMP NOT NULL,
  status VARCHAR(20) NOT NULL,
  reason TEXT
);
CREATE TABLE prescriptions (
  id INT PRIMARY KEY,
  appointment_id INT NOT NULL REFERENCES appointments(id),
  medicine VARCHAR(120) NOT NULL,
  dosage VARCHAR(60) NOT NULL,
  days INT
);
CREATE TABLE bills (
  id INT PRIMARY KEY,
  patient_id INT NOT NULL REFERENCES patients(id),
  admission_id INT REFERENCES admissions(id),
  amount_cents INT NOT NULL,
  issued_at DATE NOT NULL,
  paid BOOLEAN NOT NULL DEFAULT FALSE
);

Design notes

  • Store money as integer cents to avoid floating-point rounding.
  • Keep discharged_at nullable instead of a separate “current patients” table; a NULL means still admitted.

Tables at a glance

  • departments: id, name, floor
  • doctors: id, department_id, full_name, specialty, phone
  • patients: id, full_name, date_of_birth, gender, phone, address
  • wards: id, department_id, name, bed_count
  • admissions: id, patient_id, ward_id, attending_doctor_id, admitted_at, discharged_at
  • appointments: id, patient_id, doctor_id, starts_at, status, reason
  • prescriptions: id, appointment_id, medicine, dosage, days
  • bills: id, patient_id, admission_id, amount_cents, issued_at, paid

Questions

What are the entities in a hospital management system ER diagram?

This design uses 8 tables: departments, doctors, patients, wards, admissions, appointments, prescriptions, bills. A department employs many doctors; each doctor belongs to exactly one department (NOT NULL foreign key).

How many relationships does the hospital management system ER diagram have?

10 foreign-key relationships. The many-to-many ones are resolved with junction tables, which is how they must be stored in a relational database.

Can I edit this ER diagram and export it?

Yes. “Edit in the ER diagram generator” opens the same SQL in the editor, where you can add tables, drag boxes and export SVG, PNG, Mermaid, DBML or SQL for PostgreSQL, MySQL, SQL Server or SQLite.

Which notation is used?

Crow’s foot: a fork means many, a bar means exactly one and a circle means optional (zero). A nullable foreign key gets the circle on the parent end.

More ER diagram examples