Hospital Management
An appointment is an entity where four facts meet — patient, doctor, department and time — and not a ternary association between the first three.
Intermediate
The diagram
Opening an example creates a copy in your own projects. The original example is never modified.
The problem
A hospital group runs several departments, each on its own floor and each with its own doctors. Patients register once and are then seen in appointments: a patient telephones, a slot is found with a doctor, in a department, at a precise date and time, and a room may be assigned. A doctor belongs to one department but may hold a clinic in another one, and the appointment is booked in the department where it actually takes place. The same patient may see the same doctor many times over the years, and two patients may see the same doctor on the same day at different hours, but the same doctor cannot be in two appointments at the same time. A department may be created before it has any doctor or any appointment, and a patient may register and not be seen for months. The hospital wants to know, for any day, which appointments are booked, with whom, where and in which room.
Requirements
- The system must record patients with a unique patient number, a full name and a birth date.
- The system must record doctors with a unique doctor number, a full name and a specialisation.
- The system must record departments with a unique code, a name and a floor.
- The system must record appointments with the patient, the doctor, the department, the scheduled date and time, the room when one is assigned, and no two appointments of the same doctor may overlap.
- Every doctor works in exactly one department, and a department may employ zero, one or many doctors.
- A patient may have zero, one or many appointments, and every appointment is for exactly one patient.
- A doctor may have zero, one or many appointments, and every appointment is with exactly one doctor.
- Every appointment takes place in exactly one department, and a department may host zero, one or many appointments.
Entities and attributes
| Entity | Attributes | Identifier |
|---|---|---|
| Patient | patient_no, full_name, birth_date | patient_no |
| Doctor | doctor_no, full_name, specialisation | doctor_no |
| Department | code, name, floor | code |
| Appointment | scheduled_at, room | (patient_no, scheduled_at) |
Relations and cardinalities
| Relation | From | To | Cardinality (min, max) | Note |
|---|---|---|---|---|
| WorksIn | Doctor | Department | (1,1) – (0,N) |
One-to-many from department to doctor: every doctor works in exactly one department, so the minimum on the Doctor branch is 1 and department_code is a mandatory foreign key on Doctor. A department employs zero, one or many doctors, so the Department branch is (0,N): a department can be opened before the first doctor is hired. |
| Attends | Patient | Appointment | (0,N) – (1,1) |
A patient may have zero, one or many appointments over time, including none at all for someone who has just registered. Every appointment is for exactly one patient: the (1,1) on the Appointment branch is what makes the patient part of the appointment's identity. |
| WithDoctor | Doctor | Appointment | (0,N) – (1,1) |
A doctor may have zero, one or many appointments, and every appointment is with exactly one doctor. The pair of (1,1) tags on Attends and WithDoctor is what allows the same patient to see the same doctor on different dates: the date, not the pair of people, is what distinguishes two appointments. |
| InDepartment | Department | Appointment | (0,N) – (1,1) |
Every appointment takes place in exactly one department, so the minimum on the Appointment branch is 1 and department_code is mandatory on Appointment. The (0,N) on the Department branch says a department may host zero appointments: the minimum is 0 and not 1 because a newly opened unit must exist before its first booking, and a department that only does inpatient work may never appear here. |
Reading the diagram
- The diagram has four entities: Patient, Doctor, Department and Appointment. The first three describe stable things that exist whether or not anything happens; Appointment describes an event, and events are what most beginner diagrams forget to make into entities.
- WorksIn is one-to-many from Department to Doctor: (1,1) on the Doctor branch because every doctor works in exactly one department, (0,N) on the Department branch because a department employs zero, one or many doctors. The foreign key department_code therefore sits on Doctor, never on Department.
- Appointment is where four facts meet: who is seen (Patient), by whom (Doctor), where (Department) and when (scheduled_at). Only three of them are entities in their own right; the fourth, time, is an attribute, and this asymmetry is the reason the event cannot be reduced to an association.
- Read the three tags on the Appointment branch: (1,1) in Attends, (1,1) in WithDoctor, (1,1) in InDepartment. Together they say that one appointment involves exactly one patient, exactly one doctor and exactly one department. The other ends, all (0,N), say that a patient, a doctor and a department may each be involved many times.
- A ternary association between Patient, Doctor and Department would have no room for scheduled_at and room without becoming an entity in disguise, and its identity would be the triple of the three entities. That identity is wrong: it would forbid the same patient from seeing the same doctor twice in the same department, which happens all the time — a follow-up visit three months later. The date is what makes the second appointment different, and only an entity can carry it.
- The (0,N) on the Department branch of InDepartment deserves a comment. The minimum is 0, not 1, because the model must be able to record a department that has no booked appointment yet: a unit opened last week, or a department that only handles inpatients. Setting it to 1 would make the first row of the department impossible to insert without inventing an appointment.
- Finally, the diagram gives Appointment no key attribute, because scheduled_at and room are not identifiers. Its identity comes from the relations that reach it, and the relational schema uses (patient_no, scheduled_at) as primary key — a patient cannot be in two places at once — plus a uniqueness constraint on (doctor_no, scheduled_at), which is the rule that a doctor cannot be double-booked. This is a good illustration of a case where the conceptual diagram is complete but the logical schema still has to add a constraint the diagram can only suggest.
From the conceptual model to the logical schema
Patient(patient_no, full_name, birth_date) PRIMARY KEY (patient_no) Department(code, name, floor) PRIMARY KEY (code) Doctor(doctor_no, full_name, specialisation, department_code) PRIMARY KEY (doctor_no) FOREIGN KEY (department_code) REFERENCES Department(code) Appointment(patient_no, doctor_no, department_code, scheduled_at, room) PRIMARY KEY (patient_no, scheduled_at) FOREIGN KEY (patient_no) REFERENCES Patient(patient_no) FOREIGN KEY (doctor_no) REFERENCES Doctor(doctor_no) FOREIGN KEY (department_code) REFERENCES Department(code) UNIQUE (doctor_no, scheduled_at)