Schema Design and ConstraintsLesson 4.1

Database normalization — 1NF, 2NF, 3NF without the textbook jargon

1NF atomic values, 2NF partial dependency, 3NF transitive dependency, denormalization tradeoff, when to normalize

Normalization Is About Removing Redundancy

Normalization is a process for organizing tables so that data isn't duplicated unnecessarily. Each normal form fixes a specific type of anomaly.

1NF: One Value Per Cell

A table violates 1NF when a column holds multiple values — like storing comma-separated tags in a single column. Fix it by creating a separate table:

-- Violates 1NF
-- products: id=1, tags='sql,database,backend'

-- 1NF compliant
CREATE TABLE product_tags (
  product_id INTEGER REFERENCES products(id),
  tag        VARCHAR(50)
);

2NF: No Partial Dependencies

2NF applies to tables with composite primary keys. Every non-key column must depend on the whole key, not part of it. If a table has (order_id, product_id) as primary key but stores product_name — product_name depends only on product_id, not the full key. Move product_name to the products table.

3NF: No Transitive Dependencies

A column should depend on the primary key, not on another non-key column. If orders stores both customer_id and customer_email, email depends on customer_id — it's transitive. Move email to the customers table.

Rule of thumb: each table should describe exactly one thing. Orders table describes orders. Customers table describes customers. Don't mix.