PrismitaLEARN · PRACTICE · GROW

What is database normalization (1NF, 2NF, 3NF)?

SQL Server Design Intermediate 1–2 Yrs2–3 Yrs3–5 Yrs Conceptual 1 min read

Quick Answer

Normalization organises tables to remove redundant data and update anomalies. 1NF — atomic values, no repeating groups. 2NF — 1NF plus every non-key column depends on the whole composite key. 3NF — 2NF plus no non-key column depends on another non-key column (no transitive dependencies).

Normalization is structuring tables so you don't store the same fact in two places, which is what causes update anomalies. First normal form is atomic columns and no repeating groups — no Phone1, Phone2. Second normal form only matters with composite keys: every non-key column has to depend on the whole key, not just part of it. Third normal form says a non-key column can't depend on another non-key column — if an order row stores the customer's city, that's really a fact about the customer. OLTP databases target 3NF; for reporting I'll happily denormalize to make reads fast, as long as I have a plan to keep the copies consistent.

  • 1NF: one value per cell, a primary key, no Phone1, Phone2, Phone3 columns — move to a related table.
  • 2NF: matters only with composite keys. If a table keyed on (OrderId, ProductId) has ProductName, that depends only on ProductId — a partial dependency; move it to Products.
  • 3NF: if Orders has CustomerId and CustomerCity, the city depends on the customer, not the order — a transitive dependency; it belongs in Customers.

Denormalization is the deliberate reverse — duplicating data (or adding computed columns / summary tables) to speed up reads, accepting the cost of keeping copies in sync. OLTP systems aim for 3NF; reporting/read models are often denormalized.

All SQL Server questions More on Design