Normalization

Designing tables that don't lie to you

Normalization is the process of organizing a database's columns and tables to minimize redundancy and avoid update anomalies. The normal forms are a ladder — each level fixes a class of problems the previous one allowed.

First Normal Form (1NF) — atomic values

A table is in 1NF when every column holds a single, atomic value — no lists, no comma-separated tags stuffed into one cell:

-- Violates 1NF: a list crammed into one cell
| user_id | name | phones                    |
| 1       | Ana  | "555-1000, 555-2000"      |

-- 1NF-compliant: one row per phone
| user_id | name | phone      |
| 1       | Ana  | 555-1000   |
| 1       | Ana  | 555-2000   |

Second Normal Form (2NF) — no partial dependencies

A table is in 2NF when it is in 1NF and every non-key column depends on the whole primary key, not just part of it. This only matters for composite keys:

-- Violates 2NF: 'course_name' depends only on course_id, not the full key
PK = (student_id, course_id)
| student_id | course_id | course_name      | enrolled_at |
| 1          | CS101     | Intro to CS      | 2025-01-01  |
| 2          | CS101     | Intro to CS      | 2025-01-02  |   -- 'Intro to CS' repeated!

The fix is to split course_name into its own courses table keyed by course_id.

Third Normal Form (3NF) — no transitive dependencies

A table is in 3NF when non-key columns depend on nothing but the key — no transitive chains where A → B → C:

-- Violates 3NF: zip → city, so city depends on zip, not the user
| user_id | name | zip   | city        |
| 1       | Ana  | 10001 | New York    |
| 2       | Bob  | 10001 | New York    |   -- city duplicated; if 10001's
                                            -- city name changes, we must
                                            -- update many rows (update anomaly)

Split into users(user_id, name, zip) and zip_codes(zip, city). Now the city lives in exactly one place.

The slogan for 3NF, attributed to E.F. Codd: "every non-key attribute must provide a fact about the key, the whole key, and nothing but the key." If a column describes something other than the row's identity, move it to its own table.

When to denormalize

Normalization trades redundancy for consistency: every fact lives in one place, so updates can't disagree. But joins cost performance, and at scale (analytics, reporting, read-heavy workloads) you may deliberately denormalize — duplicate data to avoid joins. Denormalization is acceptable when:

• Reads vastly outnumber writes (the duplicated data rarely changes).
• Join cost dominates query latency (data warehouses, dashboards).
• You accept the complexity of keeping duplicates in sync (triggers, scheduled jobs, or eventual consistency).

Always start normalized; denormalize only with a measured reason.