The Professor
Index Course index

Module 2 · Databases

Lesson 08 — Data modeling

Normalization, relationships and when to denormalize: design as a consequence of the invariants.

Published
In this lesson
  1. Exercise 1 — Audit
  2. Exercise 2 — Snapshot vs derived
  3. Exercise 3 — Gift card
  4. Exercise 4 — Polymorphic
  5. Exercise 5 — Real audit
  6. Professor's summary

Exercise 1 — Audit

  1. There are no transitive dependencies: event.title depends on the id; reservation_item.price on the item. The classic suspicion would be an event.organizer_name, which doesn't exist — good.
  2. gateway_reference is a snapshot of the external identifier at payment time. If the gateway updates references, the right fix is a payment_event (callback history) or a new payment row — never a silent UPDATE that destroys the audit trail.
  3. event.slug with UNIQUE — business identity apart from the PK. Generation: title + short id, and NEVER reusable (old slug → 410 in Lesson 13).

Exercise 2 — Snapshot vs derived

  1. Reservation total: materializable derived. Pattern: written once on confirmation (or when adding an item) and read many times. I would materialize it when confirming the reservation with the items' SUM inside the same transaction (one place: the confirm() method), not on every read.
  2. If not materialized: on-the-fly SUM with Lesson 07's CTE technique (correct and simple). If materialized: recompute in the same transaction and an optional CHECK total >= 0. The unacceptable thing: three different places computing it differently.

Exercise 3 — Gift card

sql
CREATE TABLE gift_card (
  id BIGSERIAL PRIMARY KEY,
  code TEXT UNIQUE NOT NULL,          -- business identity
  initial_amount DECIMAL(10,2) NOT NULL CHECK (initial_amount > 0),
  state TEXT NOT NULL DEFAULT 'ACTIVE',  -- ACTIVE | REDEEMED | DISABLED
  created_by BIGINT NOT NULL REFERENCES "user"(id)
);

CREATE TABLE gift_card_ledger (
  id BIGSERIAL PRIMARY KEY,
  gift_card_id BIGINT NOT NULL REFERENCES gift_card(id),
  reservation_id BIGINT REFERENCES reservation(id),  -- NULL if it's the initial top-up
  amount DECIMAL(10,2) NOT NULL,      -- +top-up, -spend
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
  1. States: ACTIVE (has credit), REDEEMED (exhausted), DISABLED (cancelled/stolen).
  2. Yes, it can pay partially across several reservations: the ledger allows it; a mutable balance field gives no history and no safe concurrency.
  3. The balance is SUM(amount) FROM ledger (or materialized with a recompute-in-the-transaction rule). A lone balance loses "who spent what when" — irreplaceable for support and audits. The spend is inserted into the ledger inside the reservation's transaction (Lesson 10: with a lock on the card to prevent concurrent double-spending).

Exercise 4 — Polymorphic

  1. Problems: (a) no real FK: you can delete the event and the discount is orphaned; (b) queries with per-type ORs that kill indexes; (c) different rules per type hidden in IFs; (d) "global" has no target table — the design no longer knows what it is.
  2. Redesign: discount(code, pct, scope) with scope = 'GLOBAL' | an optional direct nullable event_id (real FK). Applying to a reservation: nullable reservation.discount_id + a discount_amount snapshot on the item or the reservation. If discounts apply per user: discount_user (N:M) — an honest bridge table.
  3. Single-use coupon per reservation: partial UNIQUE on reservation.discount_id (WHERE discount_id IS NOT NULL) — one coupon, one reservation — plus the coupon's use counter on its own table if it allows N uses.

Exercise 5 — Real audit

Expected answer (varies by project): TIMESTAMPTZ everywhere (not naive), DECIMAL for money, status with bounded choices, 00b's indexes present. Suspicious is_*: is_featured (ok, an attribute) vs is_cancelled (bad: state already exists). Typical improvements for Lesson 11: change some field to NOT NULL with a default, add a coherence CHECK (expires_at > created_at), add a unique slug to the event.

Professor's summary

  • Normalizing = one truth, one place; denormalizing = storing another truth (snapshot) or an expensive computation. Never store THE same truth twice.
  • Surrogate PK + unique business identity: both things, with different names.
  • Money: always DECIMAL; time: TIMESTAMPTZ; states: bounded choices.
  • Polymorphic and EAV: flexibility invoiced in integrity and performance. Honest tables > tricks.

After the correction: Lesson 09 — Indexes and EXPLAIN.

^ top