Normalization Basics
Organise tables to avoid duplicated, inconsistent data — the foundation of good design.
What you will learn
- Understand why we split data
- Recognise good vs bad table design
Don’t repeat data
Normalization means structuring tables so each fact is stored once. Repeating a customer’s name in every order row is bad — if they change their name, you would have to update many rows (and might miss some).
Instead, store the customer once in users and reference them by user_id in orders. Change the name in one place; everything stays consistent.
Here is the problem with real rows. This bad orders table copies the customer’s name and email into every single order:
| id | customer_name | customer_email | total |
|---|---|---|---|
| 101 | Asha | asha@x.com | 500 |
| 102 | Ravi | ravi@x.com | 250 |
| 103 | Asha | asha@x.com | 900 |
See how Asha and asha@x.com appear twice (rows 101 and 103)? If Asha changes her email, you must edit it in two places — and if you miss one, the data disagrees with itself. Now the good, normalized version splits it into two tables. The customer is stored once, and each order just points to her with a user_id:
| users.id | users.name | users.email |
|---|---|---|
| 1 | Asha | asha@x.com |
| 2 | Ravi | ravi@x.com |
| orders.id | orders.user_id | orders.total |
|---|---|---|
| 101 | 1 | 500 |
| 102 | 2 | 250 |
| 103 | 1 | 900 |
Now Asha’s email lives in exactly one row of users. Change it there and every order linked to her instantly shows the new value — nothing to miss, nothing to keep in sync. That is normalization in action.
| Bad (repeated data) | Good (normalized) |
|---|---|
| Each order stores the full customer name & email | Orders store user_id; users stores the details once |
| Update name → fix many rows | Update name → fix one row |
Tip: Rule of thumb: if you find yourself copying the same value into many rows, it probably belongs in its own table, referenced by a key.
Q. Why normalize a database?
✍️ Practice
- Spot the duplicated data in a poorly designed table and split it.
- Redesign it into two linked tables.
🏠 Homework
- Take a messy single-table design and normalize it into related tables.