Database normalization: 1NF, 2NF, and 3NF with examples
Published
Normalization is the process of arranging a database so that each fact is stored in exactly one place. That is the whole idea. The normal forms, 1NF, 2NF, and 3NF, are three checks that find the places where a fact is stored twice, or stored somewhere it does not belong.
It matters because a fact stored twice can be updated once. Then the database holds two answers to one question, and nothing in it can tell you which is right.
The table we will fix
A small shop records its orders in one table, the way a spreadsheet would:
Table orders {
id integer [pk]
customer_name varchar
customer_email varchar
customer_city varchar
items varchar [note: 'e.g. "2x Mug, 1x Teapot"']
item_prices varchar [note: 'e.g. "9.00, 24.00"']
}It works on day one. Then three things go wrong, and they have names:
- Update anomaly. A customer changes their email. It has to be changed on every order they ever placed, and missing one leaves two emails for one person.
- Insert anomaly. There is nowhere to record a new product until somebody orders it.
- Delete anomaly. Delete a customer’s only order and the customer is gone too.
First normal form: one value per cell
The rule: every column holds a single value, and there are no repeating groups.
The items column breaks it. "2x Mug, 1x Teapot" is a list crammed
into a string, so the database cannot count mugs sold or find orders containing a teapot
without parsing text. The fix is one row per item:
Table order_lines {
order_id integer
product_name varchar
quantity integer [not null]
unit_price numeric(10,2) [not null]
customer_name varchar
customer_email varchar
customer_city varchar
Indexes {
(order_id, product_name) [pk]
}
}Each row is now one product on one order, identified by the pair (order_id, product_name). Quantities and prices are real numbers the database can
add up. But the customer’s details now repeat on every line of an order, which is the next
problem.
Second normal form: depend on the whole key
The rule: the table is in 1NF, and every non-key column depends on the whole primary key, not on part of it. It only comes up when the key has more than one column.
Here the key is (order_id, product_name). quantity depends on both:
it is how many of this product are on this order. But customer_email depends on order_id alone. It would be the same on every line of the order, so it is stored
once per line when it should be stored once per order. Move what depends on the order into a
table about orders:
Table orders {
id integer [pk]
customer_name varchar
customer_email varchar
customer_city varchar
}
Table order_lines {
order_id integer [ref: > orders.id]
product_name varchar
quantity integer [not null]
unit_price numeric(10,2) [not null]
Indexes {
(order_id, product_name) [pk]
}
}Third normal form: depend on nothing but the key
The rule: the table is in 2NF, and no non-key column depends on another non-key column.
In orders, the customer’s name and city are not facts about the order. They are
facts about the customer, who is identified by the email. They depend on the key only through another column, which is called a transitive dependency. The same is true of
products, which so far exist only as a name typed onto a line. Give each its own table:
Table customers {
id integer [pk]
name varchar [not null]
email varchar [not null, unique]
city varchar
}
Table products {
id integer [pk]
name varchar [not null, unique]
price numeric(10,2) [not null]
}
Table orders {
id integer [pk]
customer_id integer [not null, ref: > customers.id]
placed_at timestamp [not null]
}
Table order_lines {
order_id integer [ref: > orders.id]
product_id integer [ref: > products.id]
quantity integer [not null]
unit_price numeric(10,2) [not null, note: 'The price paid, copied from products.price at order time']
Indexes {
(order_id, product_id) [pk]
}
}Now the three anomalies are gone. An email changes in one row. A product can exist before it is ordered. Deleting an order leaves the customer where they were.
The old summary of all three forms is still the best one: every non-key column depends on the key, the whole key, and nothing but the key.
The column that looks like a mistake and is not
order_lines.unit_price sits beside products.price, and it looks like
the same fact stored twice. It is not. One is what the product costs today; the other is what
this customer paid on the day. They start equal and are supposed to drift apart, and an order
history that changed whenever a price did would be wrong.
That is the real skill in normalization: telling two copies of one fact from two facts that happen to match. The forms cannot do it for you, because it depends on what the column means.
When to stop, and when to go back
Third normal form is where most application schemas should sit. Higher forms exist (BCNF, 4NF, 5NF) and deal with rarer cases; a schema in 3NF with sensible keys is usually already in BCNF.
Denormalizing, storing a fact twice on purpose, is a legitimate optimization when a join is measurably too slow: a cached order total, a comment count on a post. Treat it as a cache. Decide which copy is the truth, write down how the other is kept in step, and do it after measuring, not before.
Check your own schema
Normal forms are about meaning, so no tool can verify them for you. What a tool can do is draw the schema so the repeated columns are visible, and catch the mechanical problems that travel with them. The schema checker flags tables with no primary key and foreign keys with no index, and the ER diagram tool draws the result. For the relationships that normalization creates, see one-to-many vs many-to-many.