Normalization in practice
On This Lesson 11 topics
Lesson Overview
So far we designed tables from scratch. Often you start the other way round: someone hands you a spreadsheet with everything in one big sheet, and you must turn it into proper tables. The step-by-step method for that is normalization: reorganize the data so that each fact is stored once, in the place where it belongs.
This lesson teaches the first three normal forms (1NF, 2NF, 3NF) as three practical questions you ask of a table, and the three kinds of damage ("anomalies") that a badly organized table suffers from: update, insert, and delete anomalies. Normalization is where the rules you have used informally (one table per kind of thing, one value per cell, point instead of copy) get their names.
You need the previous four lessons: tables, primary keys, foreign keys, and junction tables. The next lesson applies all of this to a library spreadsheet.
Learning Objectives
By the end of this lesson you will be able to:
- Explain what normalization is for.
- Spot and fix a first-normal-form violation (a list or repeating group in a cell).
- Find a second-normal-form problem in a table with a composite key and fix it.
- Find a third-normal-form problem (a column that depends on another non-key column) and fix it.
- Name and demonstrate update, insert, and delete anomalies.
- Explain why 3NF is usually enough, and that it is a guideline with occasional, deliberate exceptions.
Written Explanation
Why normalize?
Microsoft's introductory guide describes normalization as organizing data into tables and relationships according to rules that protect the data and remove redundancy and inconsistent dependency. Redundant data wastes space and creates maintenance problems: if the same fact lives in several places, every place must be changed identically.
Our goal in one sentence: every fact is stored once, and depends on the key of its table. A memory aid widely used in teaching: each non-key column should depend on "the key, the whole key, and nothing but the key". The three normal forms are checkpoints along the way.
The three anomalies
A flat table, where unrelated facts share rows, suffers from three problems. We use this table of employees:
| emp_id | emp_name | dept_id | dept_name | dept_phone |
|---|---|---|---|---|
| 1 | Hana | 10 | Sales | 555-0100 |
| 2 | Karim | 10 | Sales | 555-0100 |
| 3 | Dina | 20 | Support | 555-0200 |
- Update anomaly. The Sales phone changes. You must edit every Sales row. If you miss one, the table says Sales has two phone numbers: the same fact is stored twice, so it can disagree with itself.
- Insert anomaly. The company creates a new department, "Legal", with no employees yet. There is no row to put it in without inventing a fake employee.
- Delete anomaly. Dina is the only Support employee and leaves. Deleting her row also erases the fact that a Support department exists, and its phone number.
Normalization removes all three by giving the department its own table (Example 3).
First normal form (1NF): one value per cell
A table is in 1NF when every cell holds a single value and there are no repeating groups: no lists in a cell, and no columns like class1, class2, class3.
| student_id | name | courses |
|---|---|---|
| 1 | Layla | Math, Physics |
| 2 | Omar | Math |
The courses cell holds a list. You cannot reliably count Math students, rename Physics, or catch a typo like "Phyisics". Fix: split into tables, a many-to-many relationship as in the previous lesson. Microsoft gives the same advice: remove repeating groups, give each set of related data its own table, and identify it with a primary key.
Second normal form (2NF): the whole key
2NF only matters when the primary key is composite. A table is in 2NF when it is in 1NF and every non-key column depends on the whole key, not just part of it.
order_lines(order_id, product_id, product_name, quantity, PK(order_id, product_id))quantity depends on both ids: it is the quantity of this product in this order. But product_name depends only on product_id: it is a fact about the product, repeated for every order containing it. That is a partial dependency. Fix: move the fact to a table keyed by what it depends on.
products(id PK, name)
order_lines(order_id FK, product_id FK, quantity, PK(order_id, product_id))If the key is a single column, 2NF is automatic, which is one more reason to use single-column surrogate keys for entity tables.
Third normal form (3NF): nothing but the key
A table is in 3NF when it is in 2NF and every non-key column depends directly on the key, not on another non-key column. A chain key to X to Y is called a transitive dependency.
In the employee table the key is emp_id. dept_id depends on the employee (each employee works in one department). But dept_name and dept_phone depend on dept_id, not directly on the employee. Fix: give departments their own table.
departments(id PK, name, phone)
employees(id PK, name, dept_id FK -> departments.id)Microsoft states the 3NF rule as eliminating fields that do not depend on the key, and gives the example of a candidates table that includes a university's name and address: a separate table lets you list universities with no candidates, which is the insert anomaly again.
A simplified check for any column: if I change this value, which other columns would have to change with it? If the answer is other non-key columns, you have found something to split off.
A procedure
- 1NF: remove lists and repeating columns (new rows or new tables).
- 2NF: for composite keys, move columns that depend on only part of the key to their own table.
- 3NF: move columns that depend on another non-key column to their own table.
- Give every new table a primary key and link tables with foreign keys.
- Check: can each fact be changed in exactly one row? Can a new entity exist before it has relatives? Can you delete a row without losing unrelated facts?
How far to go
Higher forms exist (Boyce-Codd and beyond), but Microsoft's guide says third normal form is generally considered the level needed for most applications, and warns that real projects do not always allow perfect compliance: normalizing creates more tables, and in extreme cases many tiny tables may hurt performance. Denormalizing (deliberately copying data, usually to speed up reads) is sometimes done by experienced teams, after measuring a real problem and with a plan to keep copies consistent. In this course, default to 3NF. The definitions above are intentionally simplified; strict textbook definitions talk about "candidate keys" and are a little more general.
Examples
Example 1: fixing 1NF (simple)
The flat table with a list is the student table above. Fixed with three tables:
students(id PK, name)
courses(id PK, title)
enrollments(student_id FK -> students.id, course_id FK -> courses.id, PK(student_id, course_id))| students.id | name |
|---|---|
| 1 | Layla |
| 2 | Omar |
| courses.id | title |
|---|---|
| 10 | Math |
| 11 | Physics |
| enrollments.student_id | enrollments.course_id |
|---|---|
| 1 | 10 |
| 1 | 11 |
| 2 | 10 |
Each course name is stored once, a typo is fixed in one place, and counting Math students means counting rows with course_id = 10.
Example 2: fixing 2NF in order lines (practical)
A shop's flat table has the composite key (order_id, product_id):
| order_id | product_id | product_name | product_price | quantity |
|---|---|---|---|---|
| 100 | 5 | Notebook | 3.50 | 2 |
| 100 | 6 | Pen | 1.20 | 10 |
| 101 | 5 | Notebook | 3.50 | 1 |
quantity depends on the whole key. product_name and product_price depend only on product_id: the Notebook is described twice, so renaming it needs two edits (update anomaly), and a product no order includes yet cannot be recorded (insert anomaly). After splitting:
products(id PK, name, current_price)
order_lines(order_id FK, product_id FK -> products.id, quantity, PK(order_id, product_id))Now the notebook's name and price appear once. If the shop must remember the price charged at the time, add unit_price to order_lines: that is a different fact from the current price.
Example 3: fixing 3NF in employees (realistic)
The employees table above is in 1NF (one value per cell) and 2NF (the key emp_id is a single column), but not in 3NF, because dept_name and dept_phone depend on dept_id. After the split:
| departments.id | name | phone |
|---|---|---|
| 10 | Sales | 555-0100 |
| 20 | Support | 555-0200 |
| employees.id | name | dept_id |
|---|---|---|
| 1 | Hana | 10 |
| 2 | Karim | 10 |
| 3 | Dina | 20 |
Re-check the anomalies:
- Update: the Sales phone is in one row of
departments; change it once. - Insert: a new "Legal" department is one new row in
departments; no employee is needed. - Delete: if Dina leaves, her
employeesrow goes, and the Support department with its phone remains.
The relationship is one-to-many with the foreign key on the many side (employees.dept_id), as in lesson L03.
Backend Insight
A normalized schema helps a backend stay correct while many requests change data at the same time. When a fact lives in one row, one update changes it for everyone; when it lives in forty rows, a crash halfway through can leave the database contradicting itself. Normalization is a correctness tool before it is a tidiness tool.
The trade-off is that answering a question may mean combining several tables, which is the job of SQL joins in a later module. Beginners sometimes skip normalization because "one table is easier to query". That is easier on day one and more expensive every day after.
Common Mistakes
Mistake 1: Comma-separated values in a column
- Incorrect:
tags = "java,sql,web". - Correct: one value per cell, with a
tagstable and a junction table.
Mistake 2: Duplicated data across tables
- Incorrect: the department name stored in both
departmentsandemployees. - Correct: store it once and keep only
dept_idinemployees.
Mistake 3: Hunting for 2NF problems with a single-column key
- Incorrect: searching for partial dependencies in
employees(id PK, ...). - Correct: with a single-column key 2NF is automatic; go straight to 3NF.
Mistake 4: Mistaking a different fact for a duplicate
- Incorrect: removing
unit_pricefromorder_linesbecauseproductsalready has a price. - Correct: current price and price charged at purchase are two facts; without the second, old orders would change when prices change.
Exercises
Level A: Foundation
A1. State the one-sentence goal of normalization.
Show solution A1
A1. Store every fact once, in the table whose key it depends on.
A2. Match each problem to an anomaly: (a) a changed phone number must be edited in 40 rows; (b) a new product cannot be saved until someone orders it; (c) deleting a customer's last order also removes the customer's address.
Show solution A2
A2. (a) Update anomaly. (b) Insert anomaly. (c) Delete anomaly.
A3. Which normal form does the cell value red, blue in a colors column violate?
Show solution A3
A3. First normal form: a cell holds more than one value.
Level B: Application
B1. Normalize this table to 1NF. Give the tables and keys.
| movie_id | title | genres |
|---|---|---|
| 1 | Alpha | Drama, Comedy |
| 2 | Beta | Drama |
Show solution B1
B1.
movies(id PK, title)
genres(id PK, name)
movie_genres(movie_id FK -> movies.id, genre_id FK -> genres.id, PK(movie_id, genre_id))Movie 1 has two movie_genres rows (Drama, Comedy); movie 2 has one (Drama).
B2. grades(student_id, course_id, student_name, grade, PK(student_id, course_id)). Which column breaks 2NF and why? Show the corrected tables.
Show solution B2
B2. student_name depends only on student_id, which is part of the composite key. Fix: students(id PK, name) and grades(student_id FK, course_id FK, grade, PK(student_id, course_id)).
B3. books(id PK, title, publisher_name, publisher_city), where the city is a fact about the publisher. Which form is violated? Show the fix and name one anomaly that existed before.
Show solution B3
B3. 3NF: publisher_city depends on publisher_name, not directly on the book. Fix: publishers(id PK, name, city) and books(id PK, title, publisher_id FK -> publishers.id). Before, an update anomaly existed: moving a publisher meant editing every one of its books' rows.
Level C: Challenge
C1. A flat table rentals(rental_id, car_plate, car_model, car_color, customer_id, customer_name, customer_phone, rented_on) records car rentals, with rental_id as key. List the dependencies you find, then give a 3NF design with PKs and FKs. Name an update, an insert, and a delete anomaly of the flat design.
Show solution C1
C1. car_model and car_color depend on car_plate; customer_name and customer_phone depend on customer_id; only rented_on belongs to the rental itself. Design:
cars(id PK, plate, model, color)
customers(id PK, name, phone)
rentals(id PK, car_id FK -> cars.id, customer_id FK -> customers.id, rented_on)Anomalies of the flat design: update, a changed phone must be edited on every rental of that customer; insert, a new car cannot be recorded before its first rental; delete, deleting a customer's only rental loses the customer's name and phone.
C2. A teammate says: "My orders table stores customer_city as well as customer_id, because it makes reports faster. That's fine." Evaluate the claim: when could it be acceptable, and what must the team do if they keep it?
Show solution C2
C2. The city depends on the customer, not the order, so it breaks 3NF and risks update anomalies. It could be acceptable as a deliberate denormalization, for example if reports are measurably too slow, or if the city should freeze as it was at the time of the order (then it is a different fact: "city at purchase"). The team must document the choice and either keep the copies in sync or treat them as historical.
Mini Challenge
Invent a flat table of at least six columns and four rows for something you know (a gym, a bakery, a school club). Make it contain one list in a cell, one partial dependency, and one transitive dependency. Put it aside for an hour, then normalize it to 3NF: list the final tables with PKs and FKs and name one anomaly of each kind in the flat version.
Check your work: no cell holds a list; each non-key column depends on the key, the whole key, and nothing but the key; every foreign key points at a primary key.
Summary & Next Lesson
Key takeaways
- Normalization stores each fact once, where it belongs, which removes update, insert, and delete anomalies.
- 1NF: one value per cell, no repeating groups.
- 2NF: with a composite key, every non-key column depends on the whole key.
- 3NF: every non-key column depends on the key and nothing else.
- 3NF is the default target; deliberate denormalization is an advanced, measured decision.
Next lesson
Next is "Demo: normalizing a library spreadsheet into tables", where you take a flat loans sheet through these steps and draw the ER diagram, which prepares you for the module lab.
Self-check
- What is an update anomaly?
- Why does 2NF only matter for composite keys?
- A table has
dept_id,dept_name, anddept_phonenext to employee data. Which form is broken? - What is an insert anomaly?
- Why might a team deliberately keep a duplicated column?
Show answers
- A fact stored in many rows must be changed in all of them; missing one leaves the data contradicting itself.
- A partial dependency means depending on part of a key, and a single-column key has no parts.
- 3NF:
dept_nameanddept_phonedepend ondept_id, not on the employee key. - You cannot record a fact (such as a new department) without also recording unrelated data (such as an employee).
- To speed up reads, or to preserve a value as it was at a moment in time, accepting the cost of keeping copies consistent.
References
- Microsoft Learn, Database normalization description. Supports the purpose of normalization, the first three normal forms in introductory wording, the university example for third normal form, the statement that third normal form is generally enough, and the note that perfect compliance is not always practical. The "key, the whole key, and nothing but the key" phrasing and the anomaly names are common teaching conventions that I did not verify against a specific source; the definitions here are simplified and omit candidate keys and higher forms.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.