Database normalization explained: 1NF 2NF 3NF with examples

Normalization

Normalization removes redundancy and prevents update anomalies. It is a design process, not a PostgreSQL feature, but it directly affects your schema quality.

1NF — Atomic values

Each column holds one value. No repeating groups, no arrays of values in a single cell.

-- Violates 1NF (multiple phones in one column)
phone_numbers TEXT  -- '555-1234, 555-5678'

-- 1NF compliant: separate phones table
CREATE TABLE phone_numbers (user_id INT, phone TEXT);

2NF — No partial dependencies

Applies to composite primary keys. Every non-key column must depend on the WHOLE key, not part of it.

-- order_items(order_id, product_id, qty, product_name)
-- product_name depends only on product_id → partial dependency
-- Fix: move product_name to a products table

3NF — No transitive dependencies

Non-key columns must depend on the primary key, not on other non-key columns.

-- employees(id, dept_id, dept_name)
-- dept_name depends on dept_id, not id → transitive
-- Fix: separate departments table with dept_id PK

Beyond 3NF you get BCNF and beyond — rarely needed in application schemas. Denormalize intentionally and explicitly when read performance demands it, never by accident.