Normalization: Organizing Data for Integrity

Reducing Redundancy

Normalization is the process of structuring a relational database to eliminate data redundancy and prevent update anomalies. The goal is to ensure that each piece of information is stored in exactly one place, so that changing it requires updating exactly one row.

Consider a single flat table storing everything:

OrderID | CustomerName | CustomerEmail      | Product  | Price | OrderDate
--------|--------------|-------------------|----------|-------|----------
1       | Alice        | alice@example.com | Laptop   | 999   | 2024-01-01
2       | Alice        | alice@example.com | Mouse    | 29    | 2024-01-05
3       | Bob          | bob@example.com   | Keyboard | 79    | 2024-01-06

Problems: If Alice changes her email, you must update every row where she appears. If you delete order 3, you lose all information about Bob. If you want to add a new product before any orders exist, you can't — there's no order to attach it to. These are called update, deletion, and insertion anomalies.

The Three Normal Forms (Simplified)

1NF — First Normal Form: Atomic Values
Every column must contain a single, indivisible value. No comma-separated lists, no arrays, no repeating column groups.

• Violation: Tags: "sql, database, performance" in a single column.
• Fix: Create a separate Tags table where each tag is its own row, linked by a foreign key.

2NF — Second Normal Form: Full Functional Dependency
Every non-key column must depend on the entire primary key, not just part of it. This only applies when the primary key is composite (made of multiple columns).

• Violation: A table with primary key (OrderID, ProductID) that also stores ProductName. ProductName depends only on ProductID, not on the full composite key.
• Fix: Move ProductName to a separate Products table keyed by ProductID.

3NF — Third Normal Form: No Transitive Dependencies
Every non-key column must depend only on the primary key — not on another non-key column.

• Violation: A Users table with columns UserID, ZipCode, and City. City depends on ZipCode, not on UserID. If two users share a zip code, the city is duplicated.
• Fix: Move the ZipCode → City mapping to a separate Locations table.

Mnemonic: "The data depends on the Key, the Whole Key, and Nothing But the Key — so help me Codd." (Edgar F. Codd invented relational theory.)

When to Denormalize

Normalization is not always the right answer. Highly normalized schemas require many joins, which can be expensive at scale. Data warehouses and analytics systems often deliberately denormalize — storing redundant data in wide, flat tables — to eliminate joins and maximize read performance. The trade-off is write complexity and storage cost. A DBRE must understand both ends of this spectrum and choose the right model for the workload.