Back to roadmap
Module 6 · Relational Data at ScaleDay 05025 min

Schema Design and Normalization

Normalize until it hurts; denormalize until it works.

← PreviousNext →
Day 050

Schema Design and Normalization

Users
datastore
Orders (total derived)
datastore
OrderItems
datastore
Signal path
Normalized vs derived columns
Users
datastore
flow
Orders (total derived)
datastore
Orders (total derived)
datastore
flow
OrderItems
datastore
Memory hook

Schema Design and Normalization: normalize until it hurts

Mental model

shape data so reads and writes stay honest

Design lens

Triggers vs app code: pick one source of truth.

Recall anchors
NormalizeDenormalize

Why it matters

Good schemas balance normalization (no redundancy, integrity easy) with read performance (denormalization, materialized views). Most production schemas land in 3NF with deliberate, documented exceptions.

Deep dive

1NF: atomic columns. 2NF: no partial keys. 3NF: no transitive deps.

Denormalize hot read paths: cache user counts, store derived totals.

Pick primary key based on physical access (clustered) and joins.

Demo / scenario

Order line items.

  1. Orders + OrderItems tables (normalized).
  2. Add precomputed total to Orders for fast list views.
  3. Use trigger or app code to keep total in sync.
  4. Index (user_id, created_at) for user-scoped reads.

Tradeoffs

  • Triggers vs app code: pick one source of truth.
  • Materialized views amortize aggregation.
  • Denormalization adds write cost; measure both sides.

Diagram

Users
Orders (total derived)
OrderItems
Normalized vs derived columns.

Mind map

Check yourself

Loading quiz…

Sources & further reading

← PreviousNext →