Why duplicated data causes problems
🏠The everyday picture
A shop writes the customer's full address on every bill. The customer moves. Now there are thirty bills with the old address and three with the new one. Which is right? Where should the next parcel go?
📘What it really is
- Redundancy means the same fact is stored in more than one place. It causes anomalies:
- update anomaly: a change is made in one place but not the others, and the data now contradicts itself;
- insert anomaly: you can't record a fact without an unrelated one (you can't add a new course until a student enrols in it, because courses only exist inside enrolment rows);
-
delete anomaly: deleting one thing loses another (deleting the last enrolment loses the course's details).
-
Normalisation is the process of organising tables so each fact is stored once, in the right place. It follows a series of rules called normal forms.
-
You've been doing this already by instinct: "store ids, not copies" (Module 21), one fact per column (Module 52). Normal forms make it precise.
🖱️Try it
Look at this single "orders" spreadsheet design: order_id, order_date, customer_name, customer_phone, customer_city,
product_names (comma-separated), product_prices, total. Write one example each of an update, insert and delete anomaly it
would suffer.
✅Quick check
What's an update anomaly?
Show the answer
A fact stored in several places is changed in some but not all, so the data contradicts itself.
Why can't you add a new course in a design where courses only appear inside enrolment rows?
Show the answer
There's no row to put it in until someone enrols: an insert anomaly.