Database Normalization — 1NF, 2NF, 3NF, and When to Denormalize

Normalization removes redundancy — the goal is a schema where every fact lives in exactly one place, so a single update keeps everything consistent. The three normal forms each eliminate a different type of redundancy.

1NF — one atomic value per cell, no repeating groups

• Every column must hold a single indivisible value — storing tags as "java,spring,sql" violates 1NF because SQL can't filter or index individual tags without string functions; each value belongs in its own row
• No repeating column groups either — item1, item2, item3 on an order row violates 1NF because adding a fourth item requires a schema change instead of inserting a new row

2NF — every non-key column depends on the whole primary key

• 2NF only matters when the primary key is composite — if the key is a single column, 2NF is automatically satisfied
• order_items(order_id, product_id, product_name, quantity) violates 2NF because product_name depends only on product_id, not the full composite key — which means renaming a product requires updating every order line that included it, instead of one row in a products table
• Fix: extract products(product_id, product_name) and reference by FK

3NF — no non-key column depends on another non-key column

• Every non-key column must depend directly on the primary key, not transitively through another non-key column
• employees(emp_id, dept_id, dept_name, salary) violates 3NF because dept_name depends on dept_id, not emp_id — so renaming a department requires updating every employee row in that department instead of one row in a departments table

In practice: OLTP schemas target 3NF; reporting and caching layers deliberately denormalize — storing customer_name on order rows to avoid a join on every report query. Make that trade-off intentionally with a documented update strategy, not by accident.