Lesson 08 — Data modeling

Stack: PostgreSQL · Project: TicketFlow Status: Published Prerequisite: Lesson 07 — Advanced SQL

Objectives

1. Formalize the modeling process (00b was the practice; this is the theory).
2. Apply normalization with judgement: 1NF-3NF and when to break it on purpose.
3. Choose keys, types and relationships consciously (not "because Django puts them there").
4. Model hierarchies and states without tricks that bite later (polymorphic, EAV, type flags).

1. The process (a formal recap of 00b)

1. Use cases → which queries and transactions the schema must serve.
2. Invariants → what can never happen (I1-I4): the schema must prevent it.
3. Entities and relationships → explicit cardinalities (1:1, 1:N, N:M with attributes of their own).
4. Keys and types → identity, uniqueness, types with a real domain.
5. Normalization → remove redundancy where it hurts; break it where it pays (snapshots).

The sequence matters: the schema is the consequence of use cases and invariants, not a pretty diagram.

2. Normalization with judgement

The point of normalizing: one truth, one place. If the organizer's name lives in every event, changing it touches N rows (and someone will forget one).

When to break it on purpose (defensible denormalization):

• Historical snapshot: price_at_purchase on the item (the "current" price changes; what was billed doesn't). It is not redundancy: it is another truth (the past's).
• Expensive derived values materialized with cheap regeneration: event.total_sales updated by a trigger/job — acceptable when it is read a thousand times more than written.
• Analytical reports: separate aggregated tables (OLTP stays normalized; OLAP is denormalized).

3. Keys and types with a real domain

• PK: surrogate (id, integer or UUID) everywhere; business identity goes in separate UNIQUE constraints (seat: (event, sector, row, number)).
• UUID vs integer: integer = more compact and faster; UUID v4 = non-sequential (doesn't leak URL counts nor hot-spot one index) — pro pattern: internal integer PK + public UUID on the API (you will see it in 13).
• Types: DECIMAL for money (never float!), TIMESTAMPTZ for every instant (UTC inside), TEXT with CHECK or Django enums for bounded states, BOOLEAN without hidden ternaries (NULL ≠ false).
• Nulls: every NULL column must justify itself ("not yet known" vs "not applicable"). gateway_reference NULL until the gateway answers: correct.

4. Hierarchies, polymorphism and anti-patterns

• N:M with attributes of its own: reservation_item (reservation × seat + price). The bridge table is not a necessary evil: it is where the relationship's truth lives.
• Self-reference: user.referred_by → user.id (TicketFlow referrals): a FK to the same table and done.
• Polymorphic (avoid it): a related_type + related_id column pair pointing at any table — no real FK, no integrity. Alternatives: one table per type, or a shared base table.
• EAV (avoid it): key-value rows for flexible attributes. Flexible and unplayable: no types, no constraints, no decent plans. If the domain is "every event has different fields", prefer JSONB with GIN + app-side validation, not EAV.
• Type flags: is_vip, is_cancelled, type_v2 piling up as columns — smells like a hidden hierarchy. If there are N types with different behavior: child table, or enum + justified nullable fields.

5. From invariant to schema (closing I1-I4)

Self-assessment

1. Why is a "snapshot" (price_at_purchase) not a harmful 3NF violation? What separates it from an accidental redundancy?
2. Give two reasons to separate the surrogate PK from business identity.
3. Why DECIMAL and not FLOAT for money? Which concrete bug does float produce?
4. A teammate proposes comment(related_type, related_id) for comments on events or reservations. What do you propose instead and why?
5. When would you use JSONB instead of columns, and what do you lose by doing it?

Continue with the exercises. The solutions only after trying it yourself.