Why Normalize? Avoiding Data Duplication

Each fact lives in one place

Normalization is the practice of splitting your data across tables so that each fact is stored exactly once. The opposite — denormalized data — duplicates facts across rows, which means every update has to find and change every copy.

Compare two designs for blog posts and authors:

Denormalized: a posts table with columns author_name, author_email, author_bio. If Alice writes 100 posts, her name/email/bio is stored 100 times. Update her email — touch 100 rows. Forget one — corrupted state.
Normalized: a posts table with author_id (foreign key) and a separate authors table with one row per author. Update Alice's email — one row.

The cost of normalization is joins (next lessons). The cost of denormalization is update anomalies and storage waste. For most application data, normalize first; denormalize selectively when you measure a real read-side bottleneck.

Principle: Normalize for writes; denormalize for reads — but only after you've measured. Premature denormalization in a SQLite app usually costs more in update bugs than it ever saves in query speed.

Code

Denormalized — works, but bad·sql

CREATE TABLE posts_bad (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_name  TEXT NOT NULL,    -- repeated for every post
  author_email TEXT NOT NULL,    -- repeated again
  author_bio   TEXT              -- and again
) STRICT;

Normalized — joinable, single source of truth·sql

CREATE TABLE authors (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  bio   TEXT
) STRICT;

CREATE TABLE posts (
  id        INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  title     TEXT NOT NULL,
  body      TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

Exercise

Take a denormalized CSV you have lying around (a contact export, an order export, anything with repeated fields). Sketch a normalized two- or three-table schema for it. Identify the entities, their primary keys, and the foreign-key relationships. Then ask: where would you accept controlled denormalization (e.g., snapshotting a price at order time)?

Why Normalize? Avoiding Data Duplication

Each fact lives in one place

Code

External links

Exercise

Progress

Comments 0