Database Normalisation

1️⃣ First things first: what is a table?

A database stores information in tables. A table looks like a grid — just like a spreadsheet:

➡️ Each row is one thing (one student, one pizza, one film).
⬇️ Each column is one piece of information about it (name, price, date).

Table: Students
StudentID	FirstName	FormGroup
001	Amy	10B
002	Ben	10A
003	Cara	10B

💡 Big idea: a database is just a collection of tables that work together.

🔑 The Primary Key

Imagine two students both called Ben Smith. How does the school tell them apart?

Easy — every student gets a unique ID number. No two students can ever have the same one.

💡 A primary key is a column that gives every row its own unique value. It is like your student ID, a passport number, or a barcode. One row = one key. No repeats. Never empty.

Primary key column

Table: Students
🔑 StudentID	FirstName	LastName
001	Ben	Smith
002	Ben	Smith
003	Cara	Jones

👀 See? Two Ben Smiths — but the StudentID tells them apart. That is the whole job of a primary key.

⭐ Remember: every table should have a primary key. It's the one column you can trust to be different in every row.

🔗 The Foreign Key

Databases usually have more than one table. So how do tables talk to each other?

They use a foreign key — a column in one table that points to the primary key of another table. It's like writing someone's phone number in your contacts: you don't copy the whole person, you just keep a link to them!

Primary key Foreign key (a link ➡️)

Table: FormGroups
🔑 FormID	FormName	Teacher
F1	10A	Mr Khan
F2	10B	Ms Lopez

⬅️

Table: Students
🔑 StudentID	FirstName	🔗 FormID
001	Amy	F2
002	Ben	F1
003	Cara	F2

Amy's row says F2. Look up F2 in the FormGroups table… Amy is in 10B with Ms Lopez. 🎉

💡 Foreign key = a copy of another table's primary key, used as a link.
🔑 Primary key: "this is ME."
🔗 Foreign key: "I belong to THAT."

🧹 What is normalisation? (and why bother?)

Normalisation means tidying up a database step by step so that:

✅ Each fact is stored once (no copying and pasting the same data everywhere)
✅ Nothing gets out of date or contradicts itself
✅ Tables are small, neat and easy to update

Think of a messy wardrobe 🧦👕👖 with everything in one big pile. Normalisation is like sorting it into drawers: socks here, shirts there. Same stuff — much easier to find and manage.

😱 Why messy data is a problem

If "Ms Lopez" is typed 50 times and she changes her name, you must fix it 50 times. Miss one, and your database now lies to you.

The tidy-up happens in three steps, called normal forms: 1NF ➡️ 2NF ➡️ 3NF. Each step builds on the one before, like levels in a game. 🎮

🥇 First Normal Form (1NF)

💡 Rule: one value per box.
No lists squashed into a single cell, no repeating columns — and every row needs a primary key.

Problem cells

❌ NOT in 1NF

Table: Orders (messy!)
Customer	Snacks ordered
Amy	Crisps, Cola, Apple
Ben	Cola, Cookie

Three snacks crammed into one cell! The computer can't count them, sort them, or search them properly.

⬇️ fix it ⬇️

✅ In 1NF

Table: Orders (tidy)
🔑 OrderLineID	Customer	Snack
1	Amy	Crisps
2	Amy	Cola
3	Amy	Apple
4	Ben	Cola
5	Ben	Cookie

Every cell holds exactly one value, and every row has its own key. 🎉

⭐ 1NF in one sentence: one box, one value.

🥈 Second Normal Form (2NF)

First, be in 1NF. Then apply this rule:

💡 Rule: every fact must be about the WHOLE key — not just part of it.

This matters when the key is made of two columns together (a "composite key"). Look at this table — its key is OrderID + SnackID together:

❌ NOT in 2NF

Table: OrderLines
🔑 OrderID	🔑 SnackID	Qty	SnackName	SnackPrice
O1	S1	2	Crisps	£1.00
O1	S2	1	Cola	£1.50
O2	S1	3	Crisps	£1.00

🕵️ Qty depends on the whole key (this snack in this order) — fine.
But SnackName and SnackPrice only depend on SnackID — just part of the key. That's why "Crisps £1.00" is written twice!

⬇️ fix it: move the snack facts to their own table ⬇️

✅ In 2NF — two tables

Table: OrderLines
🔑 OrderID	🔑🔗 SnackID	Qty
O1	S1	2
O1	S2	1
O2	S1	3

➡️

Table: Snacks
🔑 SnackID	SnackName	SnackPrice
S1	Crisps	£1.00
S2	Cola	£1.50

Now "Crisps £1.00" is stored once. Change the price in one place and every order sees it. 🎉 Notice how SnackID became a 🔗 foreign key linking the tables!

⭐ 2NF in one sentence: if a fact only belongs to part of the key, move it out to its own table.

🥉 Third Normal Form (3NF)

First, be in 2NF. Then apply this rule:

💡 Rule: facts must be about the key — not about another ordinary column.
No column should depend on a column that isn't the key.

❌ NOT in 3NF

Table: Students
🔑 StudentID	Name	FormGroup	FormTeacher
001	Amy	10B	Ms Lopez
002	Ben	10A	Mr Khan
003	Cara	10B	Ms Lopez

🕵️ FormTeacher doesn't really depend on the student — it depends on FormGroup (every 10B student has Ms Lopez). A fact about an ordinary column, not the key! So "Ms Lopez" is repeated for every 10B student. 😬

⬇️ fix it: give form groups their own table ⬇️

✅ In 3NF — two tables

Table: Students
🔑 StudentID	Name	🔗 FormGroup
001	Amy	10B
002	Ben	10A
003	Cara	10B

➡️

Table: FormGroups
🔑 FormGroup	FormTeacher
10A	Mr Khan
10B	Ms Lopez

Each teacher is stored once. If Ms Lopez changes her name, we update one cell. 🎉

⭐ 3NF in one sentence: ordinary columns must describe the key — nothing else.

🏁 The whole story in one box

Level	The rule (simple version)	Fixes…
🥇 1NF	One box, one value	Lists squashed into cells
🥈 2NF	Facts about the whole key	Facts stuck to only part of a two-part key
🥉 3NF	Facts about the key, nothing else	Facts that describe another ordinary column

🧠 The famous memory trick:
Every fact must depend on "the key (1NF), the whole key (2NF), and nothing but the key (3NF)."

🔑 Primary key — a unique ID for each row.
🔗 Foreign key — a link to a primary key in another table.
🧹 Normalisation — splitting data into linked tables so every fact is stored exactly once.