Normal forms, denormalization, and why schemas stick

A junior engineer stores the customer’s city on every order row — “it’s convenient for reports.” Two years later, the customer moves cities. Now half the historical orders show the old city and half show the new one. The same fact lives in two places and they disagree. Normalization is the discipline that prevents this.

Normal forms in plain terms

Normalization is a series of progressively stricter rules for splitting tables so no fact is stored twice.

1NF — atomic cells. Every cell must hold a single value. No comma-separated lists inside a column, no repeating groups, no arrays disguised as column names (tag1, tag2, tag3). If you can parse a cell with split(','), the table violates 1NF.

2NF — no partial key dependency. Every non-key column must depend on the whole primary key, not part of it. Only matters for composite primary keys. Example violation: a table order_items(order_id, product_id, product_name) — product_name depends on product_id alone, not on the composite key. Fix: move product_name to a products table.

3NF — no transitive dependency. Every non-key column must depend only on the primary key, not on another non-key column. Example violation: orders(order_id, customer_id, customer_city) — customer_city depends on customer_id, not on order_id. Fix: move customer_city to a customers (or addresses) table.

BCNF — every determinant is a candidate key. Stricter than 3NF; matters when a table has multiple overlapping candidate keys. Rare in production; common in academic examples. Violation example: two candidate keys share an attribute and one is not a superkey. In practice, target 3NF; go to BCNF only if you can state the specific overlapping-candidate-key scenario.

The point is not following rules — it is eliminating the failure mode where two rows can disagree about the same fact. If you can update Anna’s email in three tables, two will be wrong within a year.

Denormalization: when and why

Normalization optimises for write correctness. It can be expensive on the read side because answering a question requires joining many tables. Denormalization deliberately duplicates data to avoid joins on hot read paths.

Stored aggregates. An order_total_cents column on orders that caches SUM(order_items.unit_price_cents * quantity). Refreshed by a trigger or application code on each line-item write. Reporting hits a single column instead of aggregating millions of rows.

Wide read models. A user_profile table that combines name, email, current address, latest order count — fed by triggers or application code from the normalised source tables. Dashboard queries hit one row.

Materialised paths. Storing the full ancestry path of a tree node as a text column (/root/section/subsection/) to avoid recursive joins when reading hierarchies. Classic pattern for comment trees or org charts.

The discipline: denormalize on the read side, keep the authoritative facts normalised, and accept that the duplicates must be reconciled by something — a trigger, a scheduled job, application code. The failure mode is denormalize-everywhere with no reconciliation plan. Every column eventually disagrees with every other.

ORM traps that quietly break schema discipline

ORMs abstract away SQL but can introduce schema-level mistakes.

N+1 queries. An ORM loads a list of 100 orders, then for each order fetches the customer separately. Result: 101 queries where 1 join would do. In many ORMs this is the default unless you declare an eager-load (include, with, select_related). The schema implication: every FK relation must be annotated with the correct default loading strategy; leaving it to implicit lazy-load accumulates invisible N+1s.

Implicit schema generation. Many ORMs can generate the schema from the model definition. The generated schema often lacks: CHECK constraints, composite indexes, index on the FK column, named constraints. The result is a syntactically valid schema that violates the discipline of this lesson — no constraints, no format enforcement, generic index strategy. Treat ORM-generated schemas as a starting point, not the finished artifact; add constraints and indexes manually or via migration files.

Missing updated_at refresh. ORMs frequently auto-manage created_at but not updated_at. A missing updated_at makes incremental replication and auditing impossible. Enforce via a trigger (BEFORE UPDATE ... SET updated_at = now()) or ORM lifecycle hook; never trust the application to remember.

Naming conventions that pay back

These look pedantic; they pay back across every dashboard, every migration, every cross-team query.

• Plural table names (users, orders, order_items) — singular is also defensible; pick one and enforce it everywhere.
• snake_case — Postgres folds unquoted identifiers to lower case; snake_case is the production default.
• id as the primary key name on every table; foreign keys named <referenced_table_singular>_id (e.g., user_id, order_id).
• created_at, updated_at, deleted_at for temporal columns. deleted_at IS NOT NULL is the soft-delete pattern.
• Boolean columns prefixed is_, has_, can_ or named as a clear property (active, not flag).
• CHECK constraints naming — name every constraint: CONSTRAINT chk_orders_status CHECK (status IN (...)). Unnamed constraints produce cryptic error messages on violation.

Mature platforms CI-lint these conventions so they are enforced across teams, not remembered by individuals.