What is normalization?

Normalization is the process of organising columns and tables so that every fact is stored exactly once. If a customer's city lives in one row of one table, there is precisely one place to change it and precisely one thing that can be wrong. If it lives in every order that customer ever placed, there are ten thousand places, and eventually some of them disagree.

It comes from Edgar Codd's work on the relational model in 1970, and it is expressed as a series of normal forms - rules a table either satisfies or does not. The forms are cumulative: a table in third normal form is already in first and second.

Every non-key column must depend on the key, the whole key, and nothing but the key.- the traditional one-line summary of 1NF through 3NF

That line is compact and genuinely useful once you know what it means, which is what the next four sections are for. But it is worth putting the purpose before the mechanics: normalization is not tidiness and it is not an academic exercise. It is how you make certain kinds of data corruption structurally impossible rather than merely discouraged.

The three anomalies

Codd defined the forms by the problems they eliminate. Learn these three and the rules stop being arbitrary.

The update anomaly. The same fact appears in many rows. Changing it means changing every copy, and any copy you miss is now a contradiction. This is the cover diagram: three rows carry Acme's city, one is misspelled, and the database has no basis for preferring one spelling over another. Worse, the bug is not in the write that introduced the typo - it is in the schema that allowed the fact to be stored three times.

The insert anomaly. You cannot record one fact without inventing another. If products only exist as columns on order rows, you cannot add a product to the catalogue until somebody orders it. The data you want to store has nowhere to go.

The delete anomaly. Removing one fact silently removes an unrelated one. Delete a customer's last order and, if the customer's details lived on the order, the customer is gone too. Nobody asked for that and nothing warned you.

Every normal form below is a specific structural rule that makes one of these impossible.

First normal form

1NF: each column holds a single, atomic value, there are no repeating groups, and every row is uniquely identifiable.

The violation in the cover is the Items column holding A1 x3, B2 x1. It is a list crammed into a string, and everything you would want to do with it is now painful. Find all orders containing SKU A1 and you are writing LIKE '%A1%', which also matches A10 and A1B, cannot use an index, and gets slower with every row. Sum the quantities and you are parsing text in SQL. Rename a SKU and you are doing string surgery across the table.

The other common 1NF violation is the numbered column: Phone1, Phone2, Phone3. It looks tidier than a list in a string and has all the same problems, plus a hard limit at three and a pile of NULLs for everyone with one phone.

T-SQL1NF: the list becomes rows

-- before: Orders.Items = 'A1 x3, B2 x1'
 
CREATE TABLE dbo.OrderLines (
    OrderId   int  NOT NULL REFERENCES dbo.Orders(OrderId),
    Sku       varchar(20) NOT NULL,
    Quantity  int  NOT NULL,
    CONSTRAINT PK_OrderLines PRIMARY KEY (OrderId, Sku)
);
 
-- now: one row per item. indexable, joinable, countable, constrainable.
          

A modern caveat worth stating plainly: a JSON column is not automatically a 1NF violation. If the document is genuinely one opaque value that your application treats as a unit - a stored API response, a settings blob, an audit payload - keeping it as JSON is a reasonable engineering decision. It becomes a violation the moment you find yourself querying inside it, filtering on its fields, or joining on values buried in it. At that point the structure is real data pretending not to be.

Second normal form

2NF: in 1NF, and every non-key column depends on the whole primary key.

This form only has anything to say when the primary key is composite - made of two or more columns. If your key is a single Id, you are in 2NF automatically and can move on.

Take the OrderLines table above, keyed on (OrderId, Sku), and add a ProductName column. The product's name depends on the SKU alone. It has nothing to do with which order this line belongs to, so it depends on half the key. The consequences are the familiar anomalies: the name is repeated on every line item that ever referenced that product, renaming the product means updating all of them, and a product that has never been ordered has nowhere to record its name.

The fix is to move the partially-dependent column to a table keyed on the part it actually depends on - a Products table keyed by Sku, with OrderLines holding only the reference. The line item keeps what genuinely belongs to it: which order, which product, how many.

Third normal form

3NF: in 2NF, and no non-key column depends on another non-key column.

This is the one that catches most real schemas, and it is exactly the cover diagram. Orders has OrderId as its key, plus Customer and CustomerCity. The city does not depend on the order - it depends on the customer, which is itself a non-key column. That indirect route is called a transitive dependency, and it is what puts three copies of "Chicago" in the table.

The fix is the one everybody already knows by instinct: give customers their own table and have orders reference it.

T-SQL3NF: the fact moves to where it belongs

CREATE TABLE dbo.Customers (
    CustomerId  int IDENTITY PRIMARY KEY,
    Name        nvarchar(200) NOT NULL,
    City        nvarchar(100) NOT NULL      -- stored exactly once
);
 
CREATE TABLE dbo.Orders (
    OrderId     int IDENTITY PRIMARY KEY,
    CustomerId  int NOT NULL REFERENCES dbo.Customers(CustomerId),
    OrderDate   date NOT NULL
);
 
-- the typo is now impossible: there is only one row to spell wrong.
          

Notice what the foreign key buys beyond the deduplication. The database now enforces that every order points at a customer that exists. You cannot orphan an order, you cannot delete a customer out from under one without saying what should happen, and the relationship is documented in the schema rather than in someone's memory.

Also notice the cost, because it is real: reading an order with its city is now a join. That is the trade normalization makes - correctness on write, a join on read - and for transactional systems it is very nearly always the right one. Joins on indexed keys are what relational engines are built to do.

Beyond 3NF

The sequence continues - Boyce-Codd normal form, then fourth, fifth, and sixth - but the returns fall off sharply.

BCNF is a slightly stricter 3NF that resolves an edge case involving overlapping candidate keys, where a table can satisfy 3NF and still carry a redundancy. It is worth knowing the name, and in practice a schema designed sensibly to 3NF is usually already in BCNF without anyone trying.

4NF and 5NF address multi-valued and join dependencies. They come up in data modelling theory and rarely in application schemas.

The practical guidance is uncontroversial among people who do this for a living: design to 3NF, and stop. That is where essentially all of the protection against update, insert, and delete anomalies lives. Going further is usually a sign that a model has more complexity than the problem does.

Denormalizing on purpose

Deliberate, measured denormalization is a legitimate technique. Accidental denormalization is the bug in the cover diagram. The difference is whether you chose it and who is responsible for keeping the copies in agreement.

The cases that genuinely justify it:

Analytical models. A star schema is denormalized by design. Warehouses are written once by a controlled pipeline and read constantly by queries that would otherwise join eight tables, so the trade inverts.
Expensive derived values. An OrderTotal column that duplicates the sum of its lines, because recomputing it on every list view is genuinely too slow. Note that SQL Server can often do this for you with a computed column or an indexed view, keeping the value in sync automatically.
Read models. A separate, deliberately flattened projection maintained for one screen or one API, where the normalized tables remain the source of truth.

And then there is the case that looks like denormalization and is not:

Historical snapshots are not duplication - they are different facts. An invoice must record the price, the tax rate, and the shipping address as they were at the moment of sale. Joining to the product table to display the current price on a two-year-old invoice is not normalization, it is a bug that changes history every time somebody updates a price. The invoice line's price is a genuinely separate fact from the product's current price, and storing it is correct. When you find yourself copying a value, always ask this question first: is this the same fact, or is it a record of what that fact was at a point in time? If it is the latter, copy it and stop worrying.

When you do denormalize for performance, three rules keep it survivable: do it after measuring rather than in anticipation, make exactly one component responsible for keeping the copy in sync, and prefer a mechanism the engine maintains for you - a computed column, an indexed view - over application code that must remember. Every hand-maintained copy is a future incident where the two values disagree and nobody knows which one is right.

A practical approach

Model to 3NF first, always. It is far easier to denormalize a clean model under measured pressure than to normalize a messy one after two years of production data has accumulated in it.
Let the anomalies guide you. When a design feels wrong, ask which of the three it permits. If the answer is none, the design is probably fine even if you cannot name its normal form.
Declare your foreign keys. They are not overhead, they are the constraint that makes a normalized model actually hold. Index them too - the child side is unindexed by default and every join and cascading delete pays for it.
Distinguish "the same fact" from "what the fact was". The single most useful test for whether a copy is a duplication or a snapshot.
Do not fear the join. A join on an indexed key is cheap and the engine is exceptionally good at it. Denormalizing to avoid joins before you have measured a problem trades a certain correctness cost for a hypothetical performance gain.
Let the engine maintain derived data where it can. Computed columns and indexed views cannot drift; application code can.

Normalization has a reputation as theory, which is unfortunate, because the practice of it is one of the highest-leverage things you can do to a system. Every fact stored once is a class of bug that cannot happen, a constraint the database can actually enforce, and a question with exactly one answer. The schema is the only layer where you get to make a whole category of corruption impossible instead of merely unlikely.

What is normalization?