This chapter is about the shape of your data — how to arrange information in the database so a change is cheap and nothing gets lost. A bad layout takes revenge on every layer of the app.

Each piece of information in one place. A country, a category or a product type you keep in a separate lookup table, not as free text typed in each time. Otherwise you get five spellings of the same thing (“USA”, “U.S.A.”, “United States”) and can’t sensibly filter or count them.

Identify by a stable key (Slug — A readable, short part of a page address that describes its content in words instead of a mysterious number. Better for humans and SEO; a stable slug doesn’t break links when things change.), not by a number. Numbers (IDs) can change — for instance when you merge duplicates. If you tie a user’s review to a changing number, after a database rebuild it lands on the wrong product. A slug (a readable name in the address, e.g. mountain-bike) is stable, so it’s safe.

Instead of overwriting — retire. Changing a price? You don’t delete the old one, you mark it “inactive” and add a new one. The history stays, and you get an audit trail (“what changed and when”) for free.

Example: a display name (“brand + model + edition”) you compute on the fly, not store in a separate column — otherwise after a brand change that column lies until someone fixes it by hand.

11 — Data model and normalization

Commandments V and VII at the root: a backup is a Rollback — Reverting a change to the previous, working state — “Ctrl+Z” for a deployment. When a new version breaks production, a rollback restores the previous one in seconds instead of fixing in a panic., user data inviolable — but before you safeguard anything, it must have a shape in which change is cheap and predictable.

The schema is the contract of the whole system. Scrapers, web, pipelines, Migration (database) — A controlled change to the database layout — adding a column, a table or moving data — step by step. Like a renovation to plan: rebuilding data in a set order so nothing “collapses”. — everything rests on it. A poorly normalized model takes its revenge on every layer: duplicate data drifts, mappings on an unstable key lose records, computed values diverge from one another. Normalize first; denormalize deliberately and with a named source of truth.

Normalize first

No repeating groups. A value once, in one place.
Vocabularies in lookup tables. In the reference project: countries / regions / product_types / tag_types — not free-text in a column. Plus a CHECK on products.type (type_a | type_b | type_c | …) — the database rejects garbage before it gets in.
Junction tables for M:N. A product has many tags/attributes → product_tags (junction), not primary_tag + secondary_tag as two free-text fields (those were dropped by migration 090 — the junction is the sole store of attributes).

Stable keys — slug, not ID

IDs drift. After duplicate merges product_id changes (the canonical row absorbs the rows, the duplicate one disappears). That is why the stable identifier is the Slug — A readable, short part of a page address that describes its content in words instead of a mysterious number. Better for humans and SEO; a stable slug doesn’t break links when things change., not the numeric key. The hardest lesson from the reference project — the prod-database swap: you map user data by slug → new ID, never by the old ID (a review would land on the wrong product). A missing slug you skip and log, you don’t push it through blindly. → 05

Active-row instead of overwriting

A pattern from prices, worth carrying everywhere history has value:

An expired_at column (NULL = active). A price change → you expire the old row, insert a new one.
A partial unique Index (database) — A lookup in the database that makes searching instant instead of scanning everything in order. The first move for slow queries — like an index at the back of a book instead of reading 400 pages. WHERE expired_at IS NULL — enforces “one active per key” (one active price per (product, retailer)).
A history table (price_history) records every price ever seen.
The effect: audit for free — you know what changed and when, without triggers.

When to denormalize (deliberately)

Denormalization is legal for reads — but always name the source of truth and guard consistency:

Displayed/computed fields → compute in helpers, don’t store. display_name (brand + variant + edition name) computed dynamically in web/src/helpers.js. Stored, it would drift after every change to a component.
A denormalized Cache — A temporarily remembered result, so the same thing isn’t computed again on every request. Speeds the app up, but can be a trap: a stale cache shows old data. with a clear source. ext_profile_cache holds data from an external source; the ext_* columns were removed from products (migration 051) — the cache is the sole source, no two truths.
A snapshot with a live fallback. site_stats.json is a dump of numbers (products/prices/retailers); read by the home page, but with a fallback to the live database when the snapshot is stale.

Migrations and integrity

Forward-only + additive — ADD COLUMN is backward-compatible; never DROP/RENAME a column used by working old code (→ 04).
FK integrity check after every operation (PRAGMA foreign_key_check).
Gating on column existence — the script checks whether a column/table exists before it operates on it (survives different schema states between environments).

The schema as a documented contract

ERD + controlled vocabulary in docs (e.g. db_schema.md with a Mermaid ERD, data_model_reference.md with the allowed values for type/region/tag_types). A schema nobody documented is a schema the next session guesses. → 01

Anti-patterns

🚫 Free-text where there should be a lookup (country as a string → 5 spellings of “USA”/“U.S.A.”/“United States”).
🚫 A duplicated source of truth without synchronization (external data in products and in the cache → divergence).
🚫 Mapping user data by a mutable ID instead of the slug → a review on the wrong product.
🚫 Storing a computed value that drifts (display_name as a column).
🚫 A destructive migration under working old code (DROP of a column the web still reads).
🚫 Two free-text fields instead of a junction table for an M:N relation.

In practice

When you declare privacy, the data model must enforce it. Separate the paths: user-facing data and a separate, Idempotency — A property of an operation you can run many times with the same result — no duplication. Key for scripts and events: a re-run doesn’t break data. Like an “ON” switch. processing pipeline, operating exclusively on anonymized data (PII removed before processing). The claim “we don’t read the data” must follow from the schema and the script’s contract, not from copy — and have a re-identification test that proves identity cannot be reconstructed. → 04, 09