Hospital Management System Database Design: ER Diagram, Tables and SQL

Dr. Kwame Mensah is the medical superintendent of a 60-bed hospital where the front desk keeps a register of outpatient visits, the wards keep a bed book and billing keeps its own file, and the three never agree. Twice, two patients have ended up on the same bed in the book and nobody noticed until the morning round. This post designs the inpatient side of the hospital from a conversation with him: thirteen tables on SQL Server and PostgreSQL, a bed that cannot hold two patients, a prescription and a bill that belong to either a visit or an admission but never both, part payments with a computed balance, and the register's repeated address shown going wrong when a patient moves.

The conversation

Kwame: Every doctor belongs to one department: General Medicine, Surgery, Paediatrics, Orthopaedics, Cardiology. Nobody sits in two. Most patients come to the outpatient clinic and book an appointment with a particular doctor for a date and a time. Some come, some cancel, some never turn up.

Designer: An appointment is one patient, one doctor, one start time and a status: booked, completed, cancelled or no-show. Is the visit something separate from the appointment?

Kwame: For us the visit is the appointment that happened. The doctor sees the patient, may write a prescription, and the desk charges a consultation. When a patient has to stay, we admit them: a bed in one of the wards from the moment they arrive until they are discharged. A day in the ICU costs far more than a day in a general ward.

Designer: So a completed appointment is the visit. Wards contain beds, the price of a bed-day belongs to the ward, and an admission records the patient, the bed, the admitting doctor, when they came in and, later, when they left.

Kwame: And a bed holds one patient at a time. In the bed book two patients have ended up on the same bed before. And one patient has one admission at a time.

Designer: The database can refuse both: at most one admission per bed that has no discharge time yet, and the same for a patient. The same bed is used again after a discharge, so the rule is about open admissions, not every admission the bed ever had. Where are prescriptions written?

Kwame: In both places: in the clinic during a visit and on the ward during an admission. A prescription lists several medicines, each with a dose and a number of days. A bill is also for an admission or for a visit, with lines for the consultation, the bed days, the medicines and the tests. And patients pay in parts: a deposit on admission, then the insurance pays some, the rest at discharge.

Designer: A prescription points at either an appointment or an admission, never both, and its lines go in a table of their own with the medicine, the dose and the days. One bill per admission or per visit, with items marked consultation, bed days, medicine or test. Payments are their own table against a bill, and the balance is the total minus the payments, worked out when you ask.

Kwame: One more thing. Our visits register has the patient's name, phone and address on every line. When Fatima Al-Sayed moved, the clerk changed the address on her latest line only.

Designer: So the register now says she lives at two addresses. In the new design the address is stored once, in patients, and every appointment points to the patient. That is the mistake we repair at the end.

From sentences to tables

A bed that cannot hold two patients

The admission is a span of time with an open end, and the rule Kwame asked for is about the open ones. Both engines spell it the same way: a unique index that only looks at rows where the discharge time is still empty.

CREATE TABLE admissions (
    admission_id  integer      GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_admissions PRIMARY KEY,
    patient_id    integer      NOT NULL CONSTRAINT fk_admissions_patients REFERENCES patients (patient_id),
    bed_id        integer      NOT NULL CONSTRAINT fk_admissions_beds REFERENCES beds (bed_id),
    doctor_id     integer      NOT NULL CONSTRAINT fk_admissions_doctors REFERENCES doctors (doctor_id),
    admitted_at   timestamp(0) NOT NULL,
    discharged_at timestamp(0) NULL,
    reason        varchar(120) NOT NULL,
    CONSTRAINT ck_admissions_discharged_after_admitted
        CHECK (discharged_at IS NULL OR discharged_at > admitted_at)
);
-- A bed holds one patient at a time: at most one admission per bed that is not discharged yet
CREATE UNIQUE INDEX uq_admissions_bed_id_current ON admissions (bed_id) WHERE discharged_at IS NULL;
-- and a patient has at most one open admission
CREATE UNIQUE INDEX uq_admissions_patient_id_current ON admissions (patient_id) WHERE discharged_at IS NULL;

Section 3b admits a second patient to bed A-02 while Aisha Khan is in it:

ERROR:  duplicate key value violates unique constraint "uq_admissions_bed_id_current"
DETAIL:  Key (bed_id)=(2) already exists.

Msg 2601: Cannot insert duplicate key row in object 'dbo.admissions' with unique index 'uq_admissions_bed_id_current'. The duplicate key value is (2).

On SQL Server a filtered index needs QUOTED_IDENTIFIER on, which sqlcmd -I gives; without it the CREATE INDEX fails with error 1934 and the bed book is unguarded again, which is why the run command in the steps box has that flag. Two more rules in the schema are about belonging to one of two things. A prescription and a bill each point at an appointment or an admission, and a check constraint insists on exactly one:

    CONSTRAINT ck_bills_visit_or_admission CHECK (
        (appointment_id IS NOT NULL AND admission_id IS NULL) OR
        (appointment_id IS NULL AND admission_id IS NOT NULL))

Here the engines part company on a detail worth a viva answer. "One bill per visit" is a unique rule on a column that is NULL for every admission bill. PostgreSQL's UNIQUE treats NULLs as distinct and accepts any number of them; SQL Server's treats them as equal and refused the second NULL in a scratch test, so its script uses two filtered unique indexes instead, each with a WHERE ... IS NOT NULL.

The ER diagram

Five questions the superintendent asks

The sample has seven doctors, eleven patients, eight admissions and seven bills, with today fixed at 16 October 2026. Who is in the hospital now, in which ward and bed, under whom, and for how long so far:

   patient    |      ward       | bed_no |    doctor    |     admitted_at     | days_so_far
--------------+-----------------+--------+--------------+---------------------+-------------
 Liam O'Brien | General Ward A  | A-01   | Elena Rossi  | 2026-10-15 15:00:00 |           1
 Aisha Khan   | General Ward A  | A-02   | Chloe Dubois | 2026-10-14 14:00:00 |           2
 Tomas Silva  | ICU             | ICU-01 | Arjun Mehta  | 2026-10-15 02:15:00 |           1
 Wei Zhang    | Paediatric Ward | P-01   | Omar Haddad  | 2026-10-15 19:40:00 |           1
 David Chen   | Surgical Ward   | S-01   | Hannah Weber | 2026-10-13 08:30:00 |           3
(5 rows)

Dr. Elena Rossi's appointments for the day, the visit being the one that says completed:

 at_time  |     patient     |    phone     |  status
----------+-----------------+--------------+-----------
 09:00:00 | Fatima Al-Sayed | +00-555-0106 | completed
 09:20:00 | Mateo Alvarez   | +00-555-0109 | completed
 09:40:00 | Ngozi Okafor    | +00-555-0110 | completed
 10:00:00 | Priya Nair      | +00-555-0108 | booked
(4 rows)

The bill total and balance per admission, the total summed from the lines and the balance after every payment row. Joining items and payments to the bill in one go would multiply both sums, which the script's comment warns about and a scratch query confirmed:

 admission_id |   patient    | bill_id |  total   |   paid   | balance
--------------+--------------+---------+----------+----------+---------
            1 | Priya Nair   |       1 |  6310.00 |  6310.00 |    0.00
            2 | Yuki Tanaka  |       2 | 13725.00 | 11000.00 | 2725.00
            3 | Maria Garcia |       3 |  6753.00 |  6753.00 |    0.00
            4 | David Chen   |       4 |  9744.00 |  5000.00 | 4744.00
            6 | Tomas Silva  |       5 |  8303.00 |  5000.00 | 3303.00
(5 rows)

The five most prescribed medicines, counted from the prescription lines:

    name     | strength | times_prescribed | total_days
-------------+----------+------------------+------------
 Paracetamol | 500 mg   |                8 |         28
 Omeprazole  | 20 mg    |                3 |         47
 Aspirin     | 75 mg    |                2 |         60
 Ceftriaxone | 1 g      |                2 |          6
 Ibuprofen   | 400 mg   |                2 |         10
(5 rows)

And bed occupancy per ward. The "not discharged" test sits in the ON clause of the outer join on purpose: in the WHERE it would also drop every free bed whose earlier admissions are all discharged, and two wards would lose a bed each.

SELECT w.name AS ward, COUNT(b.bed_id) AS beds, COUNT(a.admission_id) AS occupied,
       COUNT(b.bed_id) - COUNT(a.admission_id) AS free_beds,
       round(100.0 * COUNT(a.admission_id) / COUNT(b.bed_id), 1) AS occupancy_pct
FROM wards AS w
JOIN beds AS b ON b.ward_id = w.ward_id
LEFT JOIN admissions AS a ON a.bed_id = b.bed_id AND a.discharged_at IS NULL
GROUP BY w.ward_id, w.name
ORDER BY w.ward_id;
      ward       | beds | occupied | free_beds | occupancy_pct
-----------------+------+----------+-----------+---------------
 General Ward A  |    5 |        2 |         3 |          40.0
 Surgical Ward   |    4 |        1 |         3 |          25.0
 Paediatric Ward |    3 |        1 |         2 |          33.3
 ICU             |    2 |        1 |         1 |          50.0
(4 rows)

The register, and the patient with two addresses

Section 5 builds the front desk's register as a table, visits, with the patient's name, phone and address and the doctor's department repeated on every row, loaded with the same ten completed visits. Then Fatima Al-Sayed moves, and the clerk corrects today's row:

UPDATE visits SET patient_address = '5 Lighthouse Lane' WHERE visit_id = 8;
UPDATE 1
SELECT DISTINCT patient_name, patient_address
FROM visits
WHERE patient_name = 'Fatima Al-Sayed';
  patient_name   |  patient_address
-----------------+-------------------
 Fatima Al-Sayed | 3 River Walk
 Fatima Al-Sayed | 5 Lighthouse Lane
(2 rows)

Keeping the copies equal means finding every one of them by name and changing all three. In the proper design the address lives once, and the same correction is one row:

UPDATE patients SET address = '5 Lighthouse Lane' WHERE patient_id = 6;
UPDATE 1

 appointment_id | visit_date |     patient     |    phone     |      address      |   doctor    |  status
----------------+------------+-----------------+--------------+-------------------+-------------+-----------
              1 | 2026-09-18 | Fatima Al-Sayed | +00-555-0106 | 5 Lighthouse Lane | Elena Rossi | completed
              2 | 2026-10-02 | Fatima Al-Sayed | +00-555-0106 | 5 Lighthouse Lane | Arjun Mehta | completed
              9 | 2026-10-16 | Fatima Al-Sayed | +00-555-0106 | 5 Lighthouse Lane | Elena Rossi | completed
             13 | 2026-10-16 | Fatima Al-Sayed | +00-555-0106 | 5 Lighthouse Lane | Arjun Mehta | booked
(4 rows)

What this design does not do

The bed rule is "one open admission per bed", not "stays never overlap": a back-dated admission that is already discharged and overlaps another stay was accepted by both engines in a scratch test. Refusing that needs the hotel's exclusion constraint or trigger from Part 7. The refused insert also used up an identity value on both engines, so the next admission got id 10 rather than 9; identity values are not a count. And this hospital is deliberately not the outpatient clinic of the Database Design series: that one has specialties, rooms, visits and invoices with a status; this one has departments, wards, beds, admissions and bills with payments, and the balance is computed rather than flagged.

Frequently asked

What tables does a hospital management system database need?

Thirteen in this inpatient design: departments, doctors, patients, appointments (a completed one is the visit), wards (with the bed-day rate), beds, admissions (patient, bed, doctor, admitted and discharged times), medicines, prescriptions and prescription_items, bills and bill_items (consultation, bed days, medicine, test), and payments. Totals, balances and occupancy are computed in queries.

How do you stop two patients being admitted to the same bed?

With a filtered unique index on the admissions table: CREATE UNIQUE INDEX uq_admissions_bed_id_current ON admissions (bed_id) WHERE discharged_at IS NULL, which both SQL Server and PostgreSQL accept with the same words. It allows a bed any number of past admissions and only one open one; the second admission is refused with an error naming the index. On SQL Server run the script with sqlcmd -I, or the filtered index is not created.

How do you model a prescription that belongs to either a visit or an admission?

Give the prescription two optional foreign keys, appointment_id and admission_id, and a CHECK constraint that exactly one of them is set. Bills work the same way. For one bill per visit, PostgreSQL can use a plain UNIQUE on appointment_id because it treats NULLs as distinct; SQL Server treats NULLs as equal, so it needs a filtered unique index with WHERE appointment_id IS NOT NULL.

Next: Part 6, an inventory system for a hardware shop: products, suppliers with their own prices, purchases and sales as movements, and the stock figure nobody should ever type.