Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 79 ยท Data Models and ER Modelling

Design Case Study: A Hospital Database

Design a hospital database from requirements to ER diagram to tables: modelling decisions, trade-offs, common mistakes and live queries on the finished schema.

Rules are easy to learn on small examples. Design skill comes from using them on a messy, realistic request. In this lesson you play the database designer for a small hospital. You start from plain-language requirements, pick out entities and relationships, draw the ER diagram, translate it into tables using the mapping rules, and then check the result by loading data and asking questions.

The point is not the final schema, which a real hospital would make far bigger. The point is the reasoning: where each decision came from, which alternatives were rejected, and which mistakes beginners make most often. A hospital is a good example because it has all the interesting shapes: many-to-many links, weak entities, history and multivalued data.

From words to tables

Requirements Underline nouns and verbs.
ER diagram Nouns become entities, verbs relationships.
Tables Apply the mapping rules.
Test Load data and ask real questions.
1 / 2

Step 1: the requirements

Here is what the hospital administrator tells us.

  1. The hospital has several departments (Cardiology, Pediatrics, ...). Each doctor works in exactly one department.
  2. We store each patient's name, birth date and phone number. A patient may have several known allergies.
  3. Patients book appointments with a doctor at a certain time. An appointment can be scheduled, completed or cancelled.
  4. During a completed appointment a doctor may prescribe medications, each with a dose and a number of days. A medication is in a catalog.
  5. Some patients are admitted to a ward bed. Each ward has numbered beds ("Ward A, bed 3"). Admission has a start date and, once the patient leaves, an end date. A bed holds at most one patient at a time, and a patient can only be in one bed at a time.

The usual technique: underline the nouns (candidate entities and attributes) and the verbs (candidate relationships). Then ask hard questions about each one.

Step 2: entities and relationships

Noun Decision Reason
Department, Doctor, Patient, Medication Strong entities Each has its own identity
Allergy Multivalued attribute of Patient Just a name; no other attributes
Ward Strong entity Has its own name
Bed Weak entity of Ward "Bed 3" means nothing without its ward
Appointment Entity (or relationship?) Has its own status and can have prescriptions attached
Prescription Entity Needs dose, days and links to two others
Admission Entity A stay has dates and history

Two decisions deserve a closer look.

Appointment: relationship or entity? At first sight "patient books doctor" is a plain M:N relationship. But the same patient can see the same doctor many times, and a prescription refers to a particular visit. A relationship table keyed by (patient, doctor) would allow only one visit per pair. So we promote the relationship to an entity with its own key, appointment_id. This is the same idea as the aggregation from the ER extensions lesson.

Admission: why not a column on the patient? A column bed on patient would describe only the current stay and overwrite the past. Making the admission an entity keeps the history, lets the same patient be admitted again and gives dates a home.

erDiagram
    DEPARTMENT ||--o{ DOCTOR : employs
    PATIENT ||--o{ APPOINTMENT : books
    DOCTOR ||--o{ APPOINTMENT : "sees patients in"
    APPOINTMENT ||--o{ PRESCRIPTION : includes
    MEDICATION ||--o{ PRESCRIPTION : "is prescribed in"
    PATIENT ||--o{ ADMISSION : has
    WARD ||--|{ BED : contains
    BED ||--o{ ADMISSION : hosts
    PATIENT ||--o{ ALLERGY : "is allergic to"

The hospital design: bed is weak (owned by ward), allergy comes from a multivalued attribute.

Step 3: tables and constraints

Applying the rules produces ten tables. Some constraints deserve attention:

  • A bed holds at most one patient at a time, and a patient occupies at most one bed. Both are "at most one open admission" rules. A normal UNIQUE cannot say that, because many closed admissions are fine. A partial unique index (a unique index with a WHERE clause, available in SQLite and PostgreSQL) enforces it: unique only among rows whose discharged_on is NULL.
  • Dates are stored as ISO text (2025-03-02), which sorts correctly in SQLite.
  • Status is a closed list, so a CHECK constraint guards it.
  • A prescription needs a dose and a duration, so those columns are NOT NULL.
SQL The hospital schema

Press Run (or Ctrl + Enter) to see the result here.

The list at the end confirms ten tables. Notice how many business rules are now enforced by the database itself instead of hoping every application remembers them.

Step 4: test the design with data

A schema is only a hypothesis until you ask real questions of it. Load some rows and try the queries the administrator will actually want. This script builds a compact version of the schema, loads data and runs three reports.

SQL Loading data and asking questions

Press Run (or Ctrl + Enter) to see the result here.

The first report shows Cardiology with 2 completed visits and Pediatrics with 1. The allergy check returns no rows: no medication name equals an allergy name. A real system would map medications to ingredient classes (penicillin covers amoxicillin), which would be a new entity, not a text comparison. That is a good example of a requirement the schema reveals as missing, which is one of the main reasons to test a design with data.

The safety rules also hold. The next script proves that the database blocks a double booking and a second open admission for the same bed. Both statements at the end fail on purpose, and that is the point.

SQL Rules the schema enforces for us

Press Run (or Ctrl + Enter) to see the result here.

The run stops at the third insert with a UNIQUE constraint failed message. Change that row to bed 2 and the fourth statement then fails with FOREIGN KEY constraint failed.

Common modelling mistakes

  • Storing a list in a column. allergies = 'penicillin, latex' breaks searching and counting. Use a table, as above.
  • Using a name as a key. Names change and repeat. Use a surrogate key (patient_id) and put UNIQUE on natural keys only where the business guarantees uniqueness.
  • Copying data between tables. If the appointment table stores the doctor's department name, a department rename must update thousands of rows, and any missed row is a lie. Store the key and join.
  • Modelling the present only. Overwriting a bed assignment destroys history. If the business cares about "what was true then", store dated rows.
  • Forgetting the constraint that is not a key. "One open admission per bed" is a business rule that an ordinary UNIQUE cannot express. Hunt for such rules in your requirements and decide where they will live.
  • One giant table. It looks easy and causes the update anomalies you will study in Redundancy and Update Anomalies.

This schema is intentionally simplified. A production hospital system also needs billing, insurance, staff schedules, audit trails and strict privacy controls (see Authentication, Authorization and Access Control). The method scales: requirements, entities, relationships, tables, constraints, test with data.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is Appointment modelled as an entity and not just a many-to-many relationship table keyed by (patient, doctor)?

    A key of (patient, doctor) allows one visit per pair. Repeat visits and the link from prescriptions to a specific visit need an identity of their own.
  2. 2. Which tool enforces 'a bed has at most one open admission' while still allowing many closed ones?

    A plain UNIQUE would also forbid several closed stays for the same bed. The partial index applies uniqueness only to rows that are still open.
  3. 3. Why is allergies TEXT containing 'penicillin, latex' a weak design?

    A multivalued attribute belongs in its own table with one row per value, which keeps the data atomic and queryable.
  4. 4. Why is Bed a weak entity of Ward?

    Bed 3 exists in many wards. The pair (ward_id, bed_no) is the full identity, which is the definition of a weak entity.

Summary

  • Start from requirements: nouns suggest entities and attributes, verbs suggest relationships.
  • Promote a relationship to an entity when it needs its own identity, history or links to other things (appointments, admissions).
  • Multivalued attributes and weak entities map to their own tables with composite keys.
  • Put as many business rules as possible into the schema: keys, CHECK, foreign keys and partial unique indexes.
  • Always test a design with sample data and realistic queries; they reveal missing entities.
  • Common mistakes: lists in columns, names as keys, copied data, missing history and overlooked constraints.

Next: Relations, Tuples and Domains, where the theory behind these tables begins.