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 — Normalization audit
  2. Exercise 2 — Snapshot vs derived
  3. Exercise 3 — Gift card (new entity)
  4. Exercise 4 — Polymorphic on trial
  5. Exercise 5 — Audit on your real DB
  6. Submit

Paper and SQL; only the last exercise touches your real TicketFlow. Do not look at solutions.md before submitting.

Exercise 1 — Normalization audit

Review 00b's v1 model field by field and answer:

  1. Is there any transitive dependency (broken 3NF)? Justify each suspicion.
  2. Payment.gateway_reference (what the gateway returns): data, snapshot or derived? What if the gateway allows updating the reference later?
  3. Where would you put event.slug (for URLs) and which constraint does it demand?

Exercise 2 — Snapshot vs derived

  1. Adding "reservation total" (reservation.total) — snapshot, derived, or computed on the fly? Decide and justify with TicketFlow's read/write pattern.
  2. Propose how to maintain it if you materialize it (trigger, the model's save(), or never) and which invariant it protects.

Exercise 3 — Gift card (new entity)

TicketFlow wants to sell gift cards: a redeemable code holding credit, applicable to a reservation as a partial payment method.

  1. Model the entity (fields, keys, states) and its relationship with Payment.
  2. Can one card pay for two reservations (partial credit)? Which constraint allows/forbids it?
  3. Where do you record the credit movements, and why NOT in a single balance field?

Exercise 4 — Polymorphic on trial

A teammate proposes adding discounts with a table discount(code, related_type, related_id, pct), where related_* points to an event, a user or "global".

  1. List the concrete problems of that design (integrity, queries, indexes).
  2. Redesign: which tables would you create and how would you apply a discount to a reservation?
  3. What if the discount applies to ONE specific reservation (single-use coupon)? Does the design change?

Exercise 5 — Audit on your real DB

On your TicketFlow:

  1. \d+ events_seat (or Django introspection): are the types consciously chosen or inherited?
  2. Are there NULL columns that are actually never NULL? Any is_* flags that are hidden states?
  3. Write a "for the model review" list of 3 concrete improvements (don't apply them yet: Lesson 11 turns them into migrations).

Submit

Paste the gift card design (SQL or description) and the improvements list. Next: Lesson 09 — Indexes and EXPLAIN.

^ top