Hospital Management
Entities, relationships, and ORM mappings - ownership, keys, cardinality, and loading strategies made concrete.
intermediate2-3 hours
What you will produce
- Entity relationship class diagram
- ORM mapping example
- Mapping decisions report
Riverbend Family Clinic runs on a spreadsheet: one tab of patients, one of doctors, a shared calendar for appointments, and a Word document per patient for visit notes. The clinic has hired you to replace this, and your first deliverable is the persistence design: the entity model, its relationships, and how those relationships map onto a relational database through an ORM.
The domain rules are short but load-bearing. Patients book appointments with doctors. Each appointment produces medical record entries — a diagnosis note, a prescription, a referral. Every doctor belongs to exactly one department. And medical records are legally retained: nothing in your design may delete one as a side effect.
You will practice what separates a diagram that looks right from a mapping that behaves: surrogate vs natural keys, relationship ownership, cascade rules, and lazy vs eager loading — including the N+1 query problem that catches almost every first ORM project.
Before you start
You should be comfortable with:
- Objects and classes — identity vs attribute equality matters constantly here.
- Class diagrams and associations — multiplicity notation and roles.
- ER modeling — entities, keys, and foreign keys.
Open DiaGO Studio's class editor with a new class diagram for Steps 1 and 2. Steps 3-6 combine Studio annotations with a plain text file that becomes your mapping decisions report.
Step 1: Identify entities
Work on paper or in a Studio note first. An entity is a domain object with identity that persists over time: two patients named Anna Novak with the same birth date are still two patients. Extract candidate nouns from the domain rules, then apply two tests — does it have identity independent of its attribute values, and does the clinic need to store and retrieve it?
You should end with five: Patient, Doctor, Department, Appointment, MedicalRecordEntry. Record each in a table like this, and finish the empty rows yourself:
| Entity | Why it is an entity | Candidate natural key? |
|---|---|---|
| Patient | Identity over time; attributes (name, address) change without changing who it is | National health ID — but not every patient has one |
| Appointment | The booking itself has state (time, status) and a lifecycle independent of patient and doctor | Patient + doctor + time — fragile once rescheduling exists |
| Doctor | ... | ... |
| Department | ... | ... |
| MedicalRecordEntry | ... | ... |
The third column already foreshadows Step 4: natural keys in this domain are absent, non-universal, or mutable.
Step 2: Model relationships with multiplicities in Studio
In Studio, draw the five classes and connect them with associations:
- Department 1 to 0..* Doctor.
- Patient 1 to 0..* Appointment, and Doctor 1 to 0..* Appointment.
- Appointment 1 to 0..* MedicalRecordEntry.
Notice what is missing: no direct Patient-Doctor edge. Conceptually that relationship is many-to-many — a patient sees many doctors, a doctor sees many patients — but Appointment resolves it. Formal UML offers the association class for exactly this shape; common practice is to promote it to a full entity as soon as it carries its own state and lifecycle, which Appointment clearly does (scheduled time, status, cancellation). This is the same move as introducing a join entity in ER modeling.
One nuance worth writing on the diagram: Appointment to MedicalRecordEntry is 0.., not 1.., even though every completed visit produces entries. Multiplicity states what must hold at every moment, and a freshly booked appointment has no entries yet. "A completed appointment has at least one entry" is a business invariant you enforce in code, not in notation.
Hint: whenever an association needs attributes of its own, it is trying to become a class.
Step 3: Decide relationship ownership
Text-file step. In the database, a relationship is a foreign key column, and a column lives in exactly one table. The near-universal convention: the many side holds it. So the appointments table carries patient_id and doctor_id, and doctors carries department_id. This is the same one-fact-one-place force that drives normalization — copying the department name onto each doctor row would be the alternative, and it is worse.
In ORM terms, the owning side is the entity whose field the ORM actually watches to write that column. Assigning appointment.patient changes the database; pushing onto an inverse patient.appointments collection alone changes nothing. Bidirectional navigation is therefore a choice, not free: an inverse collection costs loading discipline, keeping both ends consistent in memory, and breaking cycles during serialization. The heuristic — not a rule — is to add the inverse side only when you can name a real query that starts from it.
Write an ownership line for each association: which entity owns it, the FK column name, and whether the inverse side exists and why.
Step 4: Design the ORM mappings
Map two entities in representative TypeScript decorator style (do this in your text file; Studio holds the diagram):
@Entity()
class Appointment {
@PrimaryGeneratedColumn()
id: number; // surrogate key: stable, meaningless on purpose
@ManyToOne(() => Patient, (p) => p.appointments)
@JoinColumn({ name: "patient_id" })
patient: Patient; // owning side of Patient 1..* Appointment
@ManyToOne(() => Doctor)
@JoinColumn({ name: "doctor_id" })
doctor: Doctor; // unidirectional: no query starts from Doctor yet
@OneToMany(() => MedicalRecordEntry, (e) => e.appointment, {
cascade: ["insert", "update"], // never cascade delete: records are retained
})
entries: MedicalRecordEntry[];
}
@Entity()
class MedicalRecordEntry {
@PrimaryGeneratedColumn()
id: number;
@ManyToOne(() => Appointment, (a) => a.entries, { nullable: false })
@JoinColumn({ name: "appointment_id" })
appointment: Appointment;
@Column("text")
note: string;
}The cascade choice is the interesting decision. Cascading insert and update means saving an appointment saves its new entries in one call — convenient and safe. Cascading delete would mean removing an appointment silently destroys medical records, which violates the retention rule. Deletion of records must be an explicit, audited operation, so it gets no cascade at all.
Step 5: Choose loading strategies
Lazy loading fetches related rows only when the code first touches them; eager loading fetches them together with the owner, ideally in one joined query.
The trap is N+1. The clinic's daily schedule screen loads 20 appointments — one query. The template then renders each patient's name, and each access of appointment.patient lazily fires its own query: 20 more, 21 total. At 200 appointments it is 201. The fix is not "make everything eager" but to fetch eagerly for that query — a join or relation-include on the schedule endpoint.
Reach for it
- The related data is needed every time the owner is loaded
- The relation is a single row, such as many-to-one from Appointment to Patient on the schedule screen
- You are fixing a measured N+1 problem on a hot path
Avoid it
- The collection can grow without bound, such as the full appointment history of a patient
- You are setting eager globally in the mapping instead of per query
- You are guessing at access patterns instead of measuring them
Heuristic: default everything to lazy in the mapping, then opt into eager per use case. Annotate each association on your diagram with its default and note which screens override it.
Step 6: Write the mapping decisions report
Consolidate the previous steps into a short report — this is the artifact a reviewer reads. One row per decision, with the reason:
| Decision | Choice | Why |
|---|---|---|
| Primary keys | Surrogate auto-generated id on every entity | Natural candidates are absent, non-universal, or mutable |
| Patient-Appointment ownership | Appointment owns via patient_id; inverse side kept on Patient | The many side holds the FK; patient history is a named query |
| Appointment to MedicalRecordEntry cascade | Insert and update only, never delete | Records are legally retained; deletion must be explicit and audited |
| Doctor-Department ownership | ... | ... |
| Loading defaults and overrides | ... | ... |
| Delete behavior of Appointment | ... | ... |
Check your work
- For every association, can you name the table that holds the FK column and the column's name?
- Is Patient-Doctor represented only through Appointment, with no direct edge?
- Does every entity use a surrogate key, and did you record why each natural candidate was rejected?
- Could deleting an Appointment ever delete a MedicalRecordEntry in your mapping? The answer must be no.
- Does every bidirectional association have a named query that starts from the inverse side?
- Can you predict exactly how many queries the daily schedule screen issues under your loading choices?
- Is the 0..* on Appointment-MedicalRecordEntry documented as notation, with the at-least-one rule noted as a code invariant?
Going further
- Add a Room entity: appointments happen in a room, rooms belong to a department. Extend the diagram, ownership lines, and report.
- Sketch the REST resources for booking an appointment and reading a record — see REST resource design — and notice how ownership decisions shape URL nesting.
- Decide where you would cut this model into services and which foreign keys stop being enforceable — microservices trade-offs makes the costs explicit.
Practice
Without looking at your notes, write the SQL DDL (tables, PK and FK columns only) that your Step 4 mappings imply, then diff it against what your ORM would generate. Every mismatch is a mapping decision you made implicitly — add each one as a row to your report.