Modeling, ORM & Architecture · 03 Constraints & Normalization

One spreadsheet, three normal forms

The clinic sends you their existing booking sheet. Walk it with Next — each normal form removes one specific way the data can lie.

1NFOne value per cell. No comma-separated lists, no speciality_1 / speciality_2 columns. Repeating groups become rows in another table.

2NFNo partial dependencies. Every non-key column must depend on the whole key. If a column depends on only part of a composite key, it belongs elsewhere.

3NFNo transitive dependencies. A non-key column must not depend on another non-key column. doctor_name depends on doctor_id, not on the appointment — so it moves.

✓The practical test: if changing one real-world fact means editing more than one row, you are not in 3NF yet.

The three anomalies have names. Update — a doctor renames and you miss a row, so the database now holds two versions of one truth. Insert — a new speciality cannot be recorded until some doctor has it. Delete — cancelling the last appointment erases the only record of the doctor's phone number.

The four constraints that carry the weight

A constraint is a rule the database refuses to break, for every writer — your API, a migration script, a colleague at a psql prompt, a second service you have not built yet.

appointments — every rule the table enforces itself

CREATE TABLE appointments (
  id            integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id    integer     NOT NULL REFERENCES patients(id) ON DELETE RESTRICT,
  doctor_id     integer     NOT NULL REFERENCES doctors(id)  ON DELETE RESTRICT,
  starts_at     timestamptz NOT NULL,
  duration_min  integer     NOT NULL DEFAULT 15,
  status        text        NOT NULL DEFAULT 'booked',
  fee           numeric(10,2) NOT NULL,

  CONSTRAINT fee_not_negative      CHECK (fee >= 0),
  CONSTRAINT duration_sane        CHECK (duration_min BETWEEN 5 AND 240),
  CONSTRAINT status_known         CHECK (status IN ('booked','attended','cancelled','no_show')),
  CONSTRAINT one_slot_per_doctor  UNIQUE (doctor_id, starts_at)   -- the double-booking rule
);

NULLNOT NULL is a decision, not a default. For each column ask: is a row meaningful without this? An appointment without a starts_at is not an appointment.

UQUNIQUE encodes a real-world rule. (doctor_id, starts_at) says a doctor exists once per instant — which is physics, not policy.

CHKCHECK is the cheapest validation you will ever write and the only one that cannot be bypassed. Name your constraints — fee_not_negative is what your error handler will read in Session 09.

FKON DELETE is part of the model. RESTRICT refuses to delete a patient who has appointments; CASCADE would silently erase their history. For clinical records, refusing is correct.

Mistake

Adding NOT NULL or a CHECK to a table that already holds data. PostgreSQL validates every existing row first and rejects the whole statement if one fails. Fix the data, then add the rule — and expect that order to matter again in Session 06.

"The same slot must never be sold twice"

This is the one requirement from the brief that application code cannot keep. It is worth seeing exactly why.

the race that a check-then-insert always loses

-- Request A                          -- Request B  (2ms later)
SELECT count(*) FROM appointments     SELECT count(*) FROM appointments
 WHERE doctor_id = 7                    WHERE doctor_id = 7
   AND starts_at = '09:30';              AND starts_at = '09:30';
-- returns 0, so "the slot is free"   -- also returns 0. Also "free".

INSERT INTO appointments ...          INSERT INTO appointments ...
-- succeeds                           -- ALSO SUCCEEDS. Slot sold twice.

✗No amount of Go fixes this. Both requests read a true answer; it simply stopped being true between the SELECT and the INSERT. Adding a mutex fixes one process and breaks the moment you run two.

✓The UNIQUE constraint is checked at write time, atomically. The second INSERT fails, the database raises a unique-violation, and no slot is ever sold twice — regardless of how many servers you run.

→That failure is not an error to hide. Session 09 catches it by constraint name and Session 11 turns it into a 409 Conflict with a useful message.

+For overlapping ranges — a 30-minute appointment starting inside another — UNIQUE is not enough. PostgreSQL's EXCLUDE USING gist with a tstzrange handles it; MySQL has no equivalent and needs a locking read.

TASK

Normalize, then constrain

Take this sheet: booking_id, patient_name, patient_phone, doctor_name, doctor_licence, room, procedures, fee — where procedures holds "x-ray, blood test". (a) Normalize it to 3NF and list your final tables with their keys. (b) Name the update anomaly that exists before you start and say which normal form removes it. (c) Write three named CHECK constraints for the result, and one UNIQUE that encodes a rule the clinic would actually care about.

Recap

Session 03 in four lines

11NF removes repeating groups, 2NF removes partial dependencies, 3NF removes transitive ones — each kills a specific anomaly.

2If changing one real-world fact means editing more than one row, the schema is not normalized yet.

3NOT NULL, UNIQUE, CHECK and foreign-key actions are rules every writer obeys — name them, because your error handler will read those names.

4Double-booking is unfixable in application code; a UNIQUE (doctor_id, starts_at) settles it atomically.