ER Design Project: Hospital Database

Requirement

A hospital employs doctors and nurses. Every doctor has a registration number, a name and one specialisation, and is attached to exactly one department. Nurses have a staff number, a name and a shift, and are assigned to one ward.

Patients have a hospital number, a name, a date of birth, a gender, an address and a contact number. A patient may be admitted more than once. Each admission records an admission date, a discharge date, a ward and the doctor responsible.

Wards have a number, a name and a bed count, and belong to a department.

Appointments are booked between a patient and a doctor for a date and time, with a reason. A patient may see many doctors and a doctor sees many patients.

During an admission a doctor may prescribe medicines. Each prescription has a date and a set of lines, and each line names one medicine with a dose, a frequency and a duration. A prescription line has no meaning without its prescription.

Medicines have a code, a name and a manufacturer.

Step 1 — Entities and attributes

ADMISSION is promoted from a relationship to an entity. The requirement says a patient may be admitted more than once and each admission carries several attributes and further relationships — the three signals from the relationships note. Leaving it as a relationship loses the second admission.

Step 2 — Relationships

Step 3 — The ER diagram

  +------------+  N        1 +--------------+ 1        N +--------+
  |   DOCTOR   |---< WORKS >-|  DEPARTMENT  |-< PART_OF >-|  WARD  |
  +-----+------+              +--------------+             +---+----+
    |   |  M                                                   | 1
    |   +------< APPOINTMENT >------+                          |
    |            (date,time,reason) | N                        |
    |                        +------v------+                   |
    |                        |   PATIENT   |                   |
    |                        +------+------+                   |
    | 1                             | 1                        |
    |                               |                          |
    |        N   +==================v==============+  N        |
    +------------|          ADMISSION              |-----------+
                 +==============+==================+
                                | 1
                          < ISSUED_DURING >
                                | N
                      +---------v----------+
                      |   PRESCRIPTION     |
                      +---------+----------+
                                | 1
                          == HAS_LINE ==            (double diamond)
                                | N
                     +==========v===========+   N    +----------+
                     ||  PRESCRIPTION_LINE ||--------|  MEDICINE|
                     +======================+   1    +----------+
                       line_no (dashed underline)

Step 4 — The relational schema

  departments ( dept_id PK, dept_name NOT NULL )

  doctors     ( reg_no PK, doctor_name NOT NULL, specialisation
              , dept_id NOT NULL -> departments )

  wards       ( ward_no PK, ward_name NOT NULL, bed_count
              , dept_id NOT NULL -> departments )

  nurses      ( staff_no PK, nurse_name NOT NULL, shift
              , ward_no NOT NULL -> wards )

  patients    ( hosp_no PK, patient_name NOT NULL, dob, gender
              , street, city, pincode, contact )

  admissions  ( admission_id PK
              , hosp_no  NOT NULL -> patients
              , ward_no  NOT NULL -> wards
              , reg_no   NOT NULL -> doctors
              , admitted_on   NOT NULL
              , discharged_on NULL )        -- null while still admitted

  appointments( hosp_no -> patients
              , reg_no  -> doctors
              , on_date, on_time, reason
              , PK ( hosp_no, reg_no, on_date, on_time ) )

  medicines   ( med_code PK, med_name NOT NULL, manufacturer )

  prescriptions ( prescription_id PK
                , admission_id NOT NULL -> admissions
                , reg_no       NOT NULL -> doctors
                , issued_on    NOT NULL )

  prescription_lines ( prescription_id -> prescriptions
                                          ON DELETE CASCADE
                     , line_no
                     , med_code NOT NULL -> medicines
                     , dose, frequency, duration
                     , PK ( prescription_id, line_no ) )

  10 tables.

Step 5 — Design decisions worth defending

Privacy belongs in the design

• Medical data is sensitive. The design should keep clinical detail in tables that can be granted separately from administrative detail, so a receptionist can read patients without reading prescriptions.
• Do not use a national identifier as a primary key. A primary key is copied into every referencing table, spreading sensitive data across the schema.
• Deletion is rarely correct for medical records. Prefer a discharge date and retention rules over removing rows.
• Auditing who read a record is often a legal requirement, so plan for an audit table from the start. Phase 17 covers this.

Verification questions

1. Which patients are currently admitted, and in which ward?
2. What was prescribed during one admission, and by whom?
3. Which doctors in Cardiology have appointments tomorrow?
4. How many times has one patient been admitted this year?
5. Which nurses are on the night shift in a given ward?

Common mistakes

• Modelling admission as a relationship, which silently allows only one admission per patient.
• Giving prescription lines their own independent key and losing the parent link.
• Storing the ward name on the admission instead of a foreign key.
• Using a single address column, making "all patients in one city" unreliable.
• Forgetting that a discharged patient still needs the row kept.

Practice

1. Add laboratory tests, where an admission may have many tests and each test has a type, a date and a result.
2. Add a rule that a doctor may not have two appointments at the same time, and say whether the key already enforces it.
3. Model bed occupancy so that the system can refuse an admission when a ward is full.

Conclusion

Ten tables, one weak entity, one promoted relationship and one deliberate null. The two lessons that generalise beyond hospitals: promote a relationship to an entity as soon as it can repeat, and let privacy shape the schema rather than only the application.