Normalization & Design Patterns

why normalize at all

An unnormalized table stores redundant data, which creates update anomalies: change a customer's address in one row and forget the other three rows referencing the same customer, and your data is now silently inconsistent with no error to tell you. Normalization is the systematic process of splitting tables so each fact is stored exactly once.

1NF: atomic values

-- violates 1NF: a column holding multiple values
-- id | name  | phone_numbers
-- 1  | Alice | '555-1111, 555-2222'

-- 1NF: one value per cell, extra values become extra rows in a child table
-- customers: id | name
-- phones: customer_id | phone_number

First normal form just means: no repeating groups, no comma-separated lists crammed into one column. (PostgreSQL's native array/JSONB types blur this line deliberately — see the note at the end of this page.)

2NF: no partial dependency on a composite key

Applies when a table's primary key is composite (more than one column). Every non-key column must depend on the whole key, not just part of it.

-- violates 2NF: key is (order_id, product_id), but product_name only depends on product_id
-- order_items: order_id | product_id | product_name | quantity

-- 2NF: split out what depends on product_id alone
-- order_items: order_id | product_id | quantity
-- products:    product_id | product_name

3NF: no transitive dependency

Every non-key column must depend on the key, the whole key, and nothing but the key. A column that depends on another non-key column is a transitive dependency.

-- violates 3NF: zip_code determines city, but the key is customer id — city is transitively dependent
-- customers: id | name | zip_code | city

-- 3NF: city belongs with zip_code, not with the customer
-- customers: id | name | zip_code
-- zip_codes: zip_code | city

In practice, most well-designed application schemas naturally land around 3NF without anyone consciously running through the rules — the rules are most useful as a diagnostic when something already feels awkward to update.

when to denormalize on purpose

Normalization optimizes for update correctness at the cost of read performance (more joins). Deliberate denormalization inverts that tradeoff, and is legitimate when:

Situation	Example
A value is expensive to recompute and rarely changes	storing `order_total` on the order row instead of summing `order_items` every read
Read volume vastly exceeds write volume	a product's average rating, recalculated on write, read constantly
Historical accuracy matters more than current truth	storing the product's price at time of purchase on the order line, not a live reference to the current price

The failure mode to avoid is denormalizing by accident, out of not knowing normal form, rather than as a deliberate, documented tradeoff with a plan for keeping the redundant copy in sync (a trigger, or an application-level invariant).

where to go from here

Data Types & Schema Design — the constraints that enforce these relationships.
Migrations & Schema Evolution — changing a schema after normalization decisions turn out wrong.