Modeling, ORM & Architecture · 04 ER Diagrams & Denormalization

The whole schema, drawn one relationship at a time

Six entities, three relationship shapes. Walk it with Next — the diagram assembles in the same order you designed it across sessions 01 to 03.

──|A single bar means "exactly one". The appointment end of the patient relationship: one appointment belongs to one patient, never zero.

──<The crow's foot means "many". Three lines fanning out at the table that holds the foreign key — which is why the foot is always on the child side.

──○A circle means "zero is allowed". A patient may have no medical record yet; that optionality is exactly what makes the foreign key nullable or the row absent.

✓Read a line as two sentences, one per end. "One patient has many appointments" and "one appointment has exactly one patient" — if either sentence sounds wrong, the diagram is wrong.

The diagram is a review tool, not decoration. Show this to the clinic manager and they will spot in ten seconds that a patient can be seen by two doctors at once, or that nothing records which room was used — corrections that cost days once there is Go code on top.

From diagram to DDL, mechanically

A finished ERD translates into CREATE TABLE statements with no judgement left to exercise — which is the point of doing it first.

reading the diagram back out as SQL

-- crow's foot at appointments  →  FK lives on appointments
patients  1 ──< N  appointments
  ⇒  appointments.patient_id integer NOT NULL REFERENCES patients(id)

-- bar + circle on both ends  →  FK on the optional side, plus UNIQUE
patients  1 ──○ 1  medical_records
  ⇒  medical_records.patient_id integer NOT NULL UNIQUE REFERENCES patients(id)

-- crow's foot at BOTH ends  →  the line itself becomes a table
doctors  N >── ──< M  specialities
  ⇒  doctor_specialities(doctor_id, speciality_id) PRIMARY KEY (both)

1One crow's foot → one foreign key, on the table the foot touches.

2No crow's foot → add UNIQUE to the foreign key you would otherwise have written.

3Two crow's feet → a new table with a composite primary key. There is no other legal reading.

4A circle → the row may be absent, so either the column is nullable or the child row simply does not exist. Decide which, and say so on the diagram.

Tip

Keep the ERD in the repository as text, not as an image someone exported once and lost the source for. Mermaid's erDiagram renders on GitHub, diffs like code, and can be edited in a pull request — which means it stands a chance of staying true.

Denormalization: paying for reads with writes

3NF is the default, not a moral position. Denormalizing means storing a fact twice on purpose — and accepting that you now own the job of keeping both copies true.

Situation

Normalized

Denormalized

Doctor's name on an invoice

join at read time

copy — now two truths

Fee charged on a past appointment

join to a price that has since changed

copy — it is a historical fact

Appointment count per doctor

count(*) on every dashboard load

maintained counter or view

Patient's current phone

join — one place to correct it

copy — stale the moment it changes

✓Copy a value when it is a historical record, not a reference. The fee on a completed appointment is what was charged that day — it must not follow the price list when that changes. This is not denormalization at all; it is modelling time correctly.

✓Denormalize for a measured read problem. A dashboard doing count(*) over millions of rows on every load is a real reason. "Joins feel slow" without a query plan is not.

✗Never denormalize a value that must stay correct. A copied phone number is wrong the moment the original changes, and nothing in the schema will tell you which copy to believe.

→Reach for the cheaper tools first: an index, or a materialized view that the database refreshes for you. Both give you read speed without a second source of truth to maintain by hand.

TASK

Draw it, then argue with it

(a) Draw the full clinic ERD — six entities, correct crow's feet — using Mermaid erDiagram syntax, and commit it next to your schema. (b) The clinic wants a screen listing every doctor with their appointment count for today. Write the normalized query first. (c) Now justify, in three sentences, either keeping it normalized or denormalizing — and if you denormalize, name exactly what keeps the copy correct and what happens when that mechanism fails.

Recap

Session 04 in four lines

1Crow's foot = many, bar = exactly one, circle = zero allowed — and the foot always lands on the table holding the foreign key.

2Read every line as two sentences, one per end; if either sounds wrong, the model is wrong.

3A finished ERD converts to DDL mechanically: one foot → a foreign key, no foot → add UNIQUE, two feet → a join table.

4Denormalize only for a measured read problem or a genuine historical fact — never for a value that has to stay current.