Normalising to third normal form and choosing when to denormalise

What it is

The cited Microsoft description states the three classic rules. First normal form: eliminate repeating groups (no phone1, phone2, phone3 columns; one row per value in a separate table with a key). Second normal form: move sets of values that apply to multiple records into their own table, related by a foreign key. Third normal form: eliminate fields that do not depend on the key. The usual shorthand is that every non-key column depends on the key, the whole key and nothing but the key. The same source notes that third normal form is considered the highest level necessary for most applications.

Why it matters

A fact stored in two places drifts: a customer's address copied onto every order is wrong after the customer moves unless every copy is updated. Normalised tables make each fact updatable in one place and let the database enforce consistency with foreign keys. The price is joins at read time and a shape that is less convenient for reporting.

How to apply

• Model entities and relationships first; give each table a stable primary key and store each fact where it depends on that key alone.
• Columns with numeric suffixes, comma-separated lists in one column, and "type" columns that change the meaning of neighbouring columns are the usual first-normal-form violations; extract them into rows.
• Denormalise only for a demonstrated read problem: a counter, a cached total, a copied display name. Name the copy as derived (cached_total, denormalised_customer_name) and document what maintains it.
• Keep the normalised data as the source of truth; a job that recomputes derived columns from it and reports differences is the test that the denormalisation is still correct.
• Snapshots are not denormalisation: an invoice line must keep the price and description as they were at sale time even if the product row changes later.

Pitfalls

Over-normalising reference data into single-column tables that add joins without protecting anything. Treating a JSON column as an escape from modelling. Denormalising before a query plan shows that the join is the problem. Confusing history (snapshots that must not change) with derived copies (which must follow their source).