Database Normalization: 1NF, 2NF and 3NF with Examples

Normalization is the process of arranging columns into tables so that each fact is stored exactly once. The forms — 1NF, 2NF, 3NF — are checkpoints along the way, and the reason to care is not tidiness: a fact stored twice is a fact that can disagree with itself.

Rather than reciting definitions, this page takes one badly designed table and fixes it in three steps. Every stage runs, so you can query the before and the after.

The table we are going to fix

One table holding orders, the customer who placed them, and the items on them:

CREATE TABLE OrdersFlat (
  order_id      INT,
  customer      STRING,
  customer_city STRING,
  items         STRING
);

INSERT INTO OrdersFlat VALUES
  (1, 'Acme Robotics', 'Austin', 'Laptop, Mouse'),
  (2, 'Acme Robotics', 'Austin', 'Monitor');

SELECT * FROM OrdersFlat;

It works, in the sense that the data is in there. Three problems are already visible:

• The items column holds a list. There is no way to ask “how many laptops did we sell?” without splitting a string, and no way to stop someone typing “Laptop,Mouse” with no space.
• The city is repeated. Acme’s city is stored once per order. Two orders, two copies; a thousand orders, a thousand copies.
• The copies can disagree. Update the city on one row and forget the others, and the database now holds two different answers to the same question with nothing to say which is right.

Those three names are worth remembering: they are the standard answer to “why normalize?” and each one maps to a real production incident.

First normal form: one value per cell

1NF requires each column to hold a single, indivisible value — no lists, no repeating groups like item1, item2, item3. The items column breaks it, so the items become rows:

CREATE TABLE OrderItems (
  order_id INT,
  item     STRING,
  PRIMARY KEY (order_id, item)
);

INSERT INTO OrderItems VALUES
  (1, 'Laptop'),
  (1, 'Mouse'),
  (2, 'Monitor');

SELECT * FROM OrderItems;

One row per item, and a composite primary key saying the same item cannot appear twice on the same order. Now counting items is a COUNT(*) and filtering them is a WHERE, instead of string surgery.

Second normal form: no partial dependencies

2NF applies to tables with a composite key, and it says every non-key column must depend on the whole key, not part of it. Suppose we had put the item price on the order-items table:

CREATE TABLE OrderItemsBad (
  order_id   INT,
  item       STRING,
  quantity   INT,
  list_price NUMBER,          -- depends on item only, not on (order_id, item)
  PRIMARY KEY (order_id, item)
);

quantity genuinely depends on both — it is how many of this item on this order. list_price depends only on the item. So the laptop’s list price is repeated on every order containing a laptop, and we are back to the same disagreement problem one level down. The fix is to move the column to the table whose key it actually depends on:

CREATE TABLE ItemCatalogue (
  item       STRING PRIMARY KEY,
  list_price NUMBER NOT NULL
);

INSERT INTO ItemCatalogue VALUES ('Laptop', 1299), ('Mouse', 45.5), ('Monitor', 449.99);

SELECT * FROM ItemCatalogue;

A table with a single-column primary key is automatically in 2NF, because there is no partial key to depend on. That is why 2NF violations only ever appear where a composite key does.

Third normal form: no transitive dependencies

3NF says no non-key column may depend on another non-key column. Our original table breaks it: customer_city does not depend on order_id — it depends on customer, which is itself not a key. So the city rides along with the customer name, duplicated once per order.

Split the customer into its own table and reference it by key:

CREATE TABLE Customers2 (
  customer_id INT PRIMARY KEY,
  name        STRING NOT NULL,
  city        STRING
);

CREATE TABLE Orders2 (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES Customers2 (customer_id)
);

INSERT INTO Customers2 VALUES (1, 'Acme Robotics', 'Austin');
INSERT INTO Orders2 VALUES (1, 1), (2, 1);

SELECT o.order_id, c.name, c.city
FROM Orders2 o
JOIN Customers2 c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Acme’s city is now stored exactly once. Changing it is a one-row update that cannot leave the database contradicting itself, and the join reproduces the original wide view whenever you want it. This is what the sample database on this site looks like — five small tables that join into any shape you need.

A useful summary that interviewers like: every non-key column must depend on the key, the whole key, and nothing but the key. The three clauses are 1NF, 2NF and 3NF in order.

BCNF and beyond

Boyce-Codd normal form is a stricter 3NF: every determinant must be a candidate key. In practice a table in 3NF is nearly always in BCNF too, and the cases where it is not require a table with overlapping candidate keys — uncommon enough that most teams never meet one. 4NF and 5NF deal with multi-valued and join dependencies and are essentially academic for everyday application schemas. If you can reach 3NF and explain why, you are past the point of diminishing returns.

When to denormalize, and what it costs

Normalization optimises for correctness on write. Sometimes you need speed on read instead, and you deliberately store a fact twice:

• Reporting tables and warehouses. A star schema is denormalized on purpose: the data is loaded once, read constantly, and never updated in place, so the update anomaly cannot occur.
• Cached aggregates. An order_total column beside the order lines it sums, so the total does not have to be recomputed per page view.
• Point-in-time copies. An invoice should keep the price as it was when the invoice was raised, even after the catalogue price changes. That is not duplication — it is a different fact that happens to look the same.

The price is always the same: you now own the job of keeping the copies in step, usually with a trigger, a scheduled job or careful application code. Denormalize when you have measured a read problem, and know exactly what will resynchronise the duplicate. Denormalizing first, because joins “feel slow”, is how the anomalies at the top of this page get reintroduced.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Put a name to the departmentMedium
• Every department, even the quiet onesHard
• Name the managerHard
• Who reports to whomMedium

See all 42 exercises →

Frequently asked questions

Related reading

• Primary key vs foreign key
• SQL constraints: what the database refuses
• SQL JOINs: putting normalized tables back together
• SQL views: the wide table without the duplication
• 25 SQL interview questions and answers