One customer, forty addresses
A small firm keeps every order on one big sheet. Each line repeats the customer's name and address.
A customer moves house. Somebody edits the address on thirty-eight of their forty lines and misses two.
Now the firm has two addresses for one person, and no way to know which is right.
By the end of this lesson
Note
I can split a flat table into a design that avoids repetition.
Key words
| Word | What it means |
|---|---|
| Redundancy | the same fact stored in more than one place |
| Anomaly | a fault caused by redundancy when data changes |
| Dependency | a column whose value is fixed by another column |
| Atomic | holding one value only, never a list |
| Normal form | a named level of tidy design, first to third |
| Normalisation | splitting tables until redundancy is gone |
The cells students fill in are left blank in this preview.
Repetition causes three faults
In a flat table the same fact is written many times. That causes three faults, called anomalies.
An update anomaly: you change one copy and miss the others.
An insert anomaly: you cannot record a customer until they order something.
A delete anomaly: deleting the last order also deletes the only record of the customer.
Worked example: a flat table
| order_id | customer | city | country | product | qty |
|---|---|---|---|---|---|
| 1 | Ana | Leeds | UK | Pen | 2 |
| 1 | Ana | Leeds | UK | Ruler | 1 |
| 2 | Ben | Lyon | France | Pen | 5 |
| 3 | Ana | Leeds | UK | Pad | 1 |
The cells students fill in are left blank in this preview.
See what a design saves
The shop database is already split into tables. You will build a flat view of one customer and count what it would repeat.
Run the two setup lines first.
Accept the server's certificate
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the shop
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
One customer as a flat table
sql "SELECT customers.first_name, customers.city, orders.id AS order_no, products.name AS product FROM customers JOIN orders ON orders.customer_id = customers.id JOIN order_items ON order_items.order_id = orders.id JOIN products ON products.id = order_items.product_id WHERE customers.id = 33 ORDER BY orders.id, products.name LIMIT 5"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Lines against customers
sql "SELECT COUNT(*) AS flat_rows, COUNT(DISTINCT orders.customer_id) AS customers FROM orders JOIN order_items ON order_items.order_id = orders.id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
First and second normal form
First normal form (1NF): every cell holds one value. No list such as "pen, ruler" in a cell, and no columns item1, item2, item3.
Second normal form (2NF): every column depends on the whole key. If the key is order_id plus product, a column about the customer depends on order_id alone. Move it out.
Worked example: splitting the flat table
| New table | Columns | Key | Holds |
|---|---|---|---|
| orders | order_id, customer_id | order_id | One row for each order |
| order_items | order_id, product_id, qty | order_id and product_id | One row for each line |
| customers | customer_id, name, city_id | customer_id | One row for each person |
| cities | city_id, city, country | city_id | One row for each city |
The cells students fill in are left blank in this preview.
Third normal form removes a hidden dependency
Third normal form (3NF): no column depends on another column that is not the key.
A customer's id fixes their city. The city fixes the country. So the country depends on the key only through the city. That is a hidden dependency.
Move city and country to their own table, and the customer holds just a city number.
Does a city ever have two countries?
sql "SELECT COUNT(*) AS cities_with_two_countries FROM (SELECT city FROM customers GROUP BY city HAVING COUNT(DISTINCT country) > 1) AS x"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Cities against countries
sql "SELECT COUNT(DISTINCT city) AS cities, COUNT(DISTINCT country) AS countries FROM customers"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The cities written most often
sql "SELECT city, country, COUNT(*) AS customers FROM customers GROUP BY city, country ORDER BY customers DESC, city LIMIT 3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think more tables are always better. Splitting removes repetition, but every split needs a join to read the data again. Stop at third normal form unless you have a measured reason to go on.
Try it: write each customer once
-- The customers, written once each.
SELECT DISTINCT customer, city
FROM flat_orders
ORDER BY customer;
-- Change it to list each city and its country once.
Going further
Build the four tables of the worked example in your own database, student_depot. Add a small products table too, because order_items points at one. Give each table its primary key, and a foreign key wherever one table points at another.
Then load the six rows of the in-page flat table into them. Check your design by writing the join that prints the flat table again.