Normalization and Intentional Denormalization

Normalization places each fact under the key that determines it, reducing contradictory updates, duplicate storage, and deletion anomalies. Denormalization deliberately duplicates or precomputes facts for a measured read need. It is safe only when one source remains authoritative and refresh, failure, staleness, backfill, and reconciliation behavior are explicitly owned.

What will you be able to do?

• Identify functional dependencies and update anomalies.
• Apply first, second, and third normal-form reasoning pragmatically.
• Distinguish facts from projections and caches.
• Design a reversible, observable denormalization.

Prerequisites and mental model

Ask: “If X stays the same, must Y always stay the same?” If customer ID determines customer email, repeating email on every ticket creates copies of one fact. A normalized model stores it with the customer; a snapshot stores it on the ticket only if historical meaning requires that copy.

How do anomalies appear?

Bad mixed relation:

Updating an organization name requires many rows; missing one creates contradiction. Deleting the last ticket may erase the only customer record. Normalization separates organizations, customers, and tickets and connects keys.

First normal form avoids repeating groups in one cell for relational facts. Second and third normal forms remove attributes dependent on only part of a composite key or on non-key attributes. Higher forms exist; use dependencies and anomalies rather than chanting numbers.

When is denormalization justified?

Examples:

• immutable customer_name_at_submission for historical/legal snapshot;
• cached last_message_at for a measured inbox query;
• materialized daily metrics for analytics;
• searchable document projection derived from canonical content.

For last_message_at, define:

• source: maximum committed message time per ticket;
• update path: same transaction or asynchronous projection;
• failure behavior: stale but not authoritative;
• reconciliation query: compare cached value to max(messages.created_at);
• backfill and removal plan.

Why does this matter in AI-native systems?

Embeddings, summaries, search vectors, and extracted metadata are denormalized projections. They need source IDs, checksums, parser/model versions, freshness state, and rebuild paths. Storing a summary without source lineage creates an unrepairable second truth.

Prove it worked

Take a proposed flat export and list dependencies. Decompose it, then show lossless reconstruction with joins and preserved uniqueness. For one denormalized field, deliberately make it stale and prove a reconciliation query detects it.

Production judgment

• Normalization is not “more joins at any cost”; choose boundaries around transactional invariants.
• Distributed duplicate truth has higher consistency cost than same-database projections.
• Synchronous projections increase write cost; asynchronous ones expose staleness.
• Schema simplicity for one query may create lifecycle complexity for every write.

Common mistakes

• Conflicting names: repeated canonical fact → store once under its determinant.
• JSON array for relationships: update/search/integrity pain → use relationship rows.
• Cached field silently wrong: no reconciliation → monitor source-versus-projection.
• Every join called slow: no measurement → plan representative workload first.

AI Pair-Programmer Prompt

Hands-on exercise

Design a ticket_statistics projection with message count and last-message time. Choose synchronous or asynchronous maintenance and write a reconciliation query.

Acceptance criterion: canonical sources remain clear, staleness is observable, and the projection can be rebuilt from scratch.

Knowledge check

1. What does normalization primarily prevent?
2. Is a historical snapshot always a mistake?
3. What makes denormalization operable?

---

Answers

1. Insert, update, and deletion anomalies caused by misplaced or repeated facts.
2. No; it can be the intended historical fact if clearly named and governed.
3. A canonical source, refresh contract, failure handling, reconciliation, and rebuild path.

Completion checklist

• Each canonical fact has one owner.
• Dependencies and candidate keys are explicit.
• Denormalizations have measured reasons.
• Every projection is detectable, rebuildable, and removable.

Primary references

• PostgreSQL data definition
• Materialized views