Lesson 08 · Design

Normalisation

A table that stores the same fact in many rows can end up disagreeing with itself. Follow a small shop's orders table as it is normalised: one value per cell (1NF), every column depending on the whole key (2NF), and nothing but the key (3NF). Then see one UPDATE fix what used to take three, and a join put the rows back with nothing lost.

14 min10 steps5 questionsIntermediate

Fig 8 · Interactive

Loading figure…

or ← →

Check yourself

5 quick questions

Pick an answer to see why. Your score saves with your progress.

01In the flat table, Ana's city is stored in three rows. An UPDATE changes only the row for order 1 to Hull. What is this problem called?

The same fact was stored more than once, and only one copy was changed. Now the table gives two answers to 'where does Ana live?'. That is an update anomaly, and redundancy is its cause.

02Which table is NOT in first normal form (1NF)?

1NF says each cell holds one value. A list such as 'mug,tea' in one cell breaks it. The fix is one row per item.

03The 1NF table's key is (ord, item). price depends only on item. Which normal form does that break, and where does price go?

2NF says every non-key column must depend on the whole key. price depends on only part of it (item), so it moves to products(item, price), where 'tea costs 4' is stored once.

04orders(ord, cust, city) has ord → cust and cust → city. Why is it not in 3NF?

city reaches the key only through cust: a transitive dependency. 3NF moves city to customers(cust, city), so each customer's city is stored once.

05orders.cust is a foreign key to customers. Someone inserts an order for Cat, who is not in customers. What happens, and how do you then get the flat rows back?

A foreign key says every orders.cust must match a cust in customers, so the order for Cat is turned away. Each split kept the linking column (cust, ord, item), so joining the tables rebuilds exactly the original rows: a lossless decomposition.

Key terms

Glossary

Redundancy: Storing the same fact more than once, such as Ana's city in every row of her orders.
Update anomaly: When changing one copy of a repeated fact leaves the other copies wrong, so the data disagrees with itself.
Functional dependency: X → Y: rows that agree on X always agree on Y. For example, ord → cust: one order has one customer.
Key: A column, or set of columns, whose values pick out exactly one row, such as (ord, item) in the 1NF table.
First normal form (1NF): Every cell holds one value: no lists or repeating groups.
Second normal form (2NF): 1NF, and every non-key column depends on the whole key, not just part of it.
Third normal form (3NF): 2NF, and no non-key column depends on another non-key column.
Foreign key: A column whose values must match a key in another table, such as orders.cust matching customers.cust.

Fine print

Notes and sources

Real tables give each customer a generated id as the key, because two customers can share a name. Names keep this example short.
The column is called ord because ORDER is a reserved word in SQL.
NATURAL JOIN matches every pair of columns with the same name. That is tidy here but fragile in real schemas, where JOIN ... USING (cust) or JOIN ... ON names the columns explicitly.
Stricter forms exist beyond 3NF: Boyce–Codd normal form (BCNF), 4NF and 5NF. Most designs aim for 3NF or BCNF; the four tables here already meet BCNF.
Normalising makes changes simpler and safer, but reads now need joins. A primary key gets an index automatically in PostgreSQL; foreign key columns do not, so they are often indexed by hand. Some systems copy data on purpose (denormalise) for speed and accept the extra care.
SQLite checks foreign keys only after PRAGMA foreign_keys = ON; PostgreSQL always checks them.
Without ORDER BY, SQL does not promise any order for the rows of an answer. The figure shows them in order number order.
The Python example uses the sqlite3 module that ships with Python. It prints the four joined rows, each with Hull for Ana.

Quiz Unlocks after the last step.