Normalization

Normalization is the discipline of storing each fact in exactly one place. The normal forms are a checklist for finding the places where a design stores a fact twice, or ties two unrelated facts together.

What goes wrong in a flat table

Picture one wide table of order lines that also carries the customer's city on every line. Three kinds of anomaly follow:

• Update anomaly — the city is stored on every line, so changing it means changing every line. Miss one and the database now holds two answers.
• Insert anomaly — you cannot record a customer who has not ordered yet, because a row needs an order.
• Delete anomaly — delete a customer's only order and the customer's city is gone with it.

1CREATE TABLE order_lines (2  order_id      int  NOT NULL,3  line          int  NOT NULL,4  customer      text NOT NULL,5  customer_city text NOT NULL,6  product       text NOT NULL,7  PRIMARY KEY (order_id, line)8);9INSERT INTO order_lines VALUES10  (1, 1, 'Nino', 'Tbilisi', 'Pen'),11  (1, 2, 'Nino', 'Tbilisi', 'Ink'),12  (2, 1, 'Nino', 'Tbilisi', 'Pad'),13  (3, 1, 'Ana', 'Batumi', 'Pen');

1-- Nino moved. Someone updates "her latest order".2UPDATE order_lines3SET customer_city = 'Kutaisi'4WHERE customer = 'Nino' AND order_id = 2;5 6SELECT DISTINCT customer, customer_city7FROM order_lines8ORDER BY customer, customer_city;

One UPDATE with a slightly too narrow WHERE, and Nino lives in two cities. Nothing in the schema says that is impossible, so nothing stopped it.

The normal forms, briefly

A functional dependency A → B means: rows that agree on A must agree on B. The customer determines the city; the order determines the customer.

• First normal form (1NF) — every column holds one value: no lists in a string, no phone1, phone2, phone3 columns.
• Second normal form (2NF) — 1NF, and no column depends on only part of a composite key. In order_lines(order_id, line, customer, product) keyed on (order_id, line), customer depends on order_id alone, so it belongs in orders.
• Third normal form (3NF) — 2NF, and no column depends on another non-key column. customer_city depends on customer, not on the key, so it belongs in customers.

1CREATE TABLE developers (2  id     int PRIMARY KEY,3  name   text NOT NULL,4  skills text NOT NULL5);6INSERT INTO developers VALUES7  (1, 'Nino', 'java,sql'),8  (2, 'Ana', 'javascript,css'),9  (3, 'Luka', 'go, java');

1SELECT name, skills2FROM developers3WHERE skills LIKE '%java%'4ORDER BY id;

LIKE '%java%' cannot tell java from javascript, gives an ordinary index nothing to seek to because of the leading %, and a stray space after a comma defeats any exact match. A developer_skills(developer_id, skill) table answers the question with an ordinary equality and lets a foreign key keep the skill names honest.

Denormalising on purpose

A normalised schema optimises for correct writes. Reporting tables, caches and read-heavy pages sometimes store a fact twice — an order's total next to its lines, a customer's name copied into an invoice — to avoid joins or to freeze a value as it was. That is fine when it is a decision: the copy has one owner that updates it, or it is meant never to change. A copy kept in sync by hope is just an update anomaly waiting.

Common mistakes

• Storing lists in a delimited string. Searches match the wrong rows, as the skills example shows, and nothing can be indexed or constrained.
• Copying a value "for convenience" with no owner. The copies drift, as in the update anomaly above.
• Normalising history away. An invoice should keep the price that was charged, not join to today's price; that copy is correct.

What interviewers ask

• Explain 1NF, 2NF and 3NF. Atomic values; no dependency on part of a composite key; no dependency between non-key columns. One example each.
• What problems does normalization solve? Update, insert and delete anomalies — the same fact stored in several places, or facts that cannot exist without each other.
• When would you denormalise? For read performance or to snapshot a value, with one clear owner of the copy; never by accident.