Notesdata-models🌱 seedling

Normalization

Also: Database Normalization · Normal Forms · Denormalization · Update Anomaly

Store each fact in exactly one place and refer to it by a meaningless ID. Changes then touch one row and copies can't disagree; the price is joins on read.

Why it exists

If a company's name is copied into every row that mentions it, renaming the company (Facebook became Meta in October 2021) means updating every copy. Miss one and the data contradicts itself: an update anomaly.

How it works

  • Shared entities (companies, cities, products) get their own table; other rows hold a foreign-key ID.
  • The ID has no meaning to people, so it never needs to change, even when everything human-readable about the entity does.
  • The normal forms (1NF, 2NF, 3NF, BCNF) formalize "one fact, one place"; in practice the test is "if this fact changed, how many rows would I update?"
  • Denormalization deliberately copies data into the shape one read needs, e.g. a Materialized View, a search index or a precomputed timeline (Fan-Out on Write).

Trade-offs

Normalized Denormalized
Writes one row per fact, no anomalies update every copy or accept drift
Reads joins reassemble data already assembled
Fits OLTP with changing facts read-heavy paths, analytics, caches

In practice

  • Relational schemas normalize by default; document schemas denormalize by default and reference when data is shared or unbounded.
  • A safe denormalization is Derived Data: rebuilt from one source of truth, not edited independently.

Pitfalls

  • Denormalizing without a single owner and a way to rebuild the copies; drift is silent.
  • Over-normalizing read-hot paths until every page needs a dozen joins.

Recall

These cards are also in your review deck, scheduled with spaced repetition.