9.11. Normalization

Why Normalization Matters

When you design a relational database, one of your main goals is to avoid data problems such as:

Duplicate data
Conflicting or inconsistent values
Hard to update records
Unnecessary storage usage

Normalization is a set of rules and techniques that help you design tables that store each piece of data in the right place, in a clean and consistent way.

In this chapter you will:

Learn what the common normal forms are (1NF, 2NF, 3NF)
See the typical problems they solve
Normalize example tables step by step
Understand when you might not want to normalize completely

You will not learn SQL syntax here, only how to structure tables. SQL is covered elsewhere in the course.

Key idea: Normalization is about structuring tables so that:

Each fact is stored once
Data is consistent
Changes are easy and safe

Types of Anomalies Normalization Solves

Before looking at normal forms, it helps to see the problems that normalization prevents. These are called anomalies.

Insertion Anomalies

An insertion anomaly happens when you cannot insert data because some other data is missing.

Example table: StudentCourse

student_id	student_name	course_id	course_name
1	Alice	101	Databases
2	Bob	102	Web Development

Problem: You want to add a new course that has no students yet.

To insert a new course row, you need to fill student_id and student_name. You might use a fake student id or leave it NULL. Both options are bad.

This is an insertion anomaly. You should be able to insert a course independently of any students.

Update Anomalies

An update anomaly happens when you must update the same piece of information in multiple rows, and you might miss some.

Continue with the same table. Suppose course 101 changes name from "Databases" to "Database Systems".

student_id	student_name	course_id	course_name
1	Alice	101	Databases
3	Carol	101	Databases

You must update course_name to "Database Systems" in all rows that have course_id = 101. If you miss one row, your data becomes inconsistent.

This is an update anomaly.

Deletion Anomalies

A deletion anomaly happens when deleting one fact accidentally removes other important data.

Using the same StudentCourse table, suppose the only student in course 102 is Bob:

student_id	student_name	course_id	course_name
2	Bob	102	Web Development

If Bob leaves the school and you delete his row, you also delete the only record of course 102. Now it looks like the course never existed.

This is a deletion anomaly.

First Normal Form (1NF)

First Normal Form is the most basic level of normalization. Almost all relational databases expect you to be in 1NF.

Definition of 1NF

A table is in First Normal Form (1NF) if:

Every cell contains a single value, not a list or set.
Each column contains values of the same type.
Each row is uniquely identifiable by a primary key.

1NF rule: No repeating groups or arrays inside a single column. One value per cell.

Example of a Table Not in 1NF

Imagine a Customer table:

customer_id	name	phones
1	Alice	`+1-111-1111,+1-2222`
2	Bob	`+1-333-3333`

Problems:

phones contains multiple values in a single cell (for Alice).
You cannot easily search for "customers with phone +1-2222" with clean SQL.
You cannot add another phone number without editing the string.

This violates 1NF.

Converting to 1NF Using a Separate Table

Correct design:

Customer

customer_id	name
1	Alice
2	Bob

CustomerPhone

customer_id	phone
1	+1-111-1111
1	+1-2222
2	+1-333-3333

Now:

Each cell has a single value.
You can search, filter, and aggregate phone numbers easily.
Customers can have any number of phones.

Repeating Groups Example

Another bad 1NF design:

order_id	customer_id	item1_name	item1_qty	item2_name	item2_qty
1	10	Apple	2	Banana	5

Issues:

Columns item1_name, item2_name are repeating groups.
What if an order has 3 or 10 items?

Correct 1NF design:

Order

order_id	customer_id
1	10

OrderItem

order_id	item_name	quantity
1	Apple	2
1	Banana	5

Functional Dependencies (Intuition)

To understand higher normal forms, you need the idea of a functional dependency. This sounds abstract, but the idea is simple.

A functional dependency written as:

$$ A \rightarrow B $$

means: if you know A, you can uniquely determine B.

For example, in a Student table:

student_id -> student_name
Once you know the student id, you know the student name.

In a Product table:

product_id -> price
Once you know the product id, you know the price.

In a CourseEnrollment table:

student_id	course_id	grade

The pair (student_id, course_id) might uniquely determine grade.

(student_id, course_id) -> grade

This pair can be the primary key. A primary key always functionally determines all other columns in its table.

You do not need to master the theory here. You only need to recognize when:

A non key column depends on part of a primary key
A non key column depends on another non key column

This is what 2NF and 3NF care about.

Second Normal Form (2NF)

2NF builds on 1NF. It only matters when your primary key is composite, meaning it uses multiple columns together as the key.

Definition of 2NF

A table is in Second Normal Form (2NF) if:

It is already in 1NF.
Every non key column depends on the whole primary key, not just part of it.

If a non key column depends only on part of a composite key, that column is in the wrong table.

2NF rule: No partial dependencies.
No non key column should depend on just a part of a composite primary key.

Example of a Table Not in 2NF

Consider this Enrollment table:

student_id	course_id	student_name	course_name	grade
1	101	Alice	Databases	A
2	101	Bob	Databases	B
1	102	Alice	Web Dev	A-

Primary key: (student_id, course_id)
Non key columns: student_name, course_name, grade

Look at the dependencies:

(student_id, course_id) -> grade
Grade depends on both student and course. Good.
student_id -> student_name
Student name depends only on student_id, not on the course. Problem.
course_id -> course_name
Course name depends only on course_id, not on the student. Problem.

So student_name and course_name depend only on part of the composite key, which violates 2NF.

This leads to:

Duplicated student names in many rows
Duplicated course names in many rows
Update anomalies if a name changes

Converting to 2NF

Split the table into three:

Student

student_id	student_name
1	Alice
2	Bob

Course

course_id	course_name
101	Databases
102	Web Dev

Enrollment

student_id	course_id	grade
1	101	A
2	101	B
1	102	A-

Now all non key columns in each table depend on the whole primary key of that table:

In Student, primary key is student_id and student_name depends on it.
In Course, primary key is course_id and course_name depends on it.
In Enrollment, key is (student_id, course_id) and grade depends on both.

Third Normal Form (3NF)

3NF builds on 2NF and focuses on removing indirect dependencies.

Definition of 3NF

A table is in Third Normal Form (3NF) if:

It is in 2NF.
Every non key column depends directly on the primary key, not on another non key column.

If a non key column depends on another non key column, you have a transitive dependency, which 3NF forbids.

3NF rule: No transitive dependencies.
Non key columns must depend only on the primary key, not on other non key columns.

Example of a Table Not in 3NF

Consider an Employee table:

employee_id	employee_name	department_id	department_name
1	Alice	10	Sales
2	Bob	20	IT
3	Carol	10	Sales

Primary key: employee_id
Non key columns: employee_name, department_id, department_name

Dependencies:

employee_id -> employee_name
employee_id -> department_id
department_id -> department_name

Here, department_name does not depend directly on employee_id.
Instead, you have an indirect chain:

$$ employee\_id \rightarrow department\_id \rightarrow department\_name $$

This is a transitive dependency. It causes:

Duplicate department names
Update anomalies if department names change

Converting to 3NF

Split the table:

Employee

employee_id	employee_name	department_id
1	Alice	10
2	Bob	20
3	Carol	10

Department

department_id	department_name
10	Sales
20	IT

Now:

In Employee, non key columns (employee_name, department_id) depend directly on employee_id.
In Department, department_name depends on department_id.

No non key column depends on another non key column in the same table.

Another 3NF Example: Address Data

Bad 3NF design for Customer:

customer_id	name	city	zip_code
1	Alice	London	12345
2	Bob	London	12345
3	Carol	Bristol	54321

Suppose zip_code uniquely identifies the city, so:

zip_code -> city

But in this table:

customer_id -> zip_code
zip_code -> city
So customer_id -> city through zip_code

city depends transitively on customer_id. To be strict 3NF, you might separate:

Customer

customer_id	name	zip_code
1	Alice	12345
2	Bob	12345
3	Carol	54321

ZipCode

zip_code	city
12345	London
54321	Bristol

In practice, many applications leave city and zip in the same table, because the practical benefit of further normalization is small compared to the extra joins. This leads to the next topic: trade offs.

Higher Normal Forms (Brief Overview)

There are more advanced normal forms:

BCNF (Boyce Codd Normal Form)
A stricter version of 3NF. It requires that every determinant is a candidate key. Useful when you have complex or unusual dependencies.
4NF (Fourth Normal Form)
Deals with multi valued dependencies. For example if a table mixes two independent multi valued facts about an entity.
5NF (Fifth Normal Form)
Deals with decomposing tables to remove every possible join dependency.

For most typical backend applications:

Up to 3NF or BCNF is usually enough.
Higher normal forms appear in specialized or complex designs.

For this course, you only need a basic awareness that they exist, not to apply them daily.

Step by Step Normalization Example

Let us normalize a realistic table step by step. Suppose we start with a single table:

OrderFull

order_id	order_date	customer_id	customer_name	customer_city	product_id	product_name	unit_price	quantity
1	2024-01-01	10	Alice	London	100	Keyboard	30.00	2
1	2024-01-01	10	Alice	London	101	Mouse	10.00	1
2	2024-01-02	11	Bob	Bristol	100	Keyboard	30.00	1

Assume the primary key is (order_id, product_id).

Step 1: Ensure 1NF

Is this in 1NF?

Each cell has a single value.
No repeating groups like item1_name, item2_name.
Data types per column are consistent.

So this table is already in 1NF.

Step 2: Check 2NF (Partial Dependencies)

Primary key: (order_id, product_id)
Non key columns: order_date, customer_id, customer_name, customer_city, product_name, unit_price, quantity

Check dependencies:

(order_id, product_id) -> quantity
order_id -> order_date
order_id -> customer_id
customer_id -> customer_name, customer_city
product_id -> product_name, unit_price

Partial dependencies:

order_date and customer_id depend only on order_id.
product_name and unit_price depend only on product_id.

So OrderFull is not in 2NF.

Split into separate tables

We can identify at least three entities:

Customer
Product
Order (with items)

Create these tables:

Customer

customer_id	customer_name	customer_city
10	Alice	London
11	Bob	Bristol

Product

product_id	product_name	unit_price
100	Keyboard	30.00
101	Mouse	10.00

Order

order_id	order_date	customer_id
1	2024-01-01	10
2	2024-01-02	11

OrderItem

order_id	product_id	quantity
1	100	2
1	101	1
2	100	1

Now, in each table:

Non key columns depend on the whole primary key.
We have removed partial dependencies.

So these tables are in 2NF.

Step 3: Check 3NF (Transitive Dependencies)

Check each table:

Customer

Primary key: customer_id
Non key: customer_name, customer_city
Do any non key columns depend on another non key column?
Likely no obvious rule like "customer_city determines customer_name". So 3NF is fine.

Product

Primary key: product_id
Non key: product_name, unit_price
No non key determines another non key. 3NF is fine.

Order

Primary key: order_id
Non key: order_date, customer_id
customer_id is a foreign key, but does not depend on order_date. 3NF is fine.

OrderItem

Primary key: (order_id, product_id)
Non key: quantity
Quantity depends on the full key. 3NF is fine.

So our final design is in 3NF and avoids the anomalies:

To change a product price, update one row in Product.
To change a customer city, update one row in Customer.
Deleting an OrderItem does not delete information about the Product or Customer.
You can insert a new Product that has not been ordered yet.

Normalization vs Performance: Trade Offs

Normalization improves data quality but can increase the number of joins in queries.

For example, to get an order summary, you might need:

Join Order to Customer
Join Order to OrderItem
Join OrderItem to Product

This is fine in many systems, but in very high traffic scenarios you might:

Use denormalization: store some redundant data in a table to speed up reads.
Cache results in Redis or another cache.
Use materialized views.

Examples of intentional denormalization:

Storing customer_name on the Order for quick reporting, even though you could join to Customer.
Storing product_name and unit_price snapshot on OrderItem so that you know what the customer saw at the time, even if the product price changes later.

Practical rule:

Use normalization (up to 3NF) as your default when designing a schema.
Denormalize only when you have a clear reason, such as performance, and you understand how to keep data consistent.

How To Use Normalization When Designing Schemas

When you design a new set of tables, you can follow this checklist:

List the facts you need to store.
Example: "Order has a customer, date, items, quantities, and prices."
Find the primary key for each table.
Example: order_id for order, (order_id, product_id) for order items.
Check 1NF

Any columns with multiple values in one cell?
Any repeating groups like phone1, phone2?

Check 2NF

If a table has a composite primary key, do any non key columns depend on only part of the key?

Check 3NF

In each table, does any non key column depend on another non key column?

If yes, split the table to remove the dependency.

Summary

Normalization is about structuring data to reduce duplication and prevent anomalies.
1NF: No repeating groups, one value per cell, consistent types, and a primary key.
2NF: No partial dependencies on a composite primary key. Non key columns must depend on the whole key.
3NF: No transitive dependencies. Non key columns must depend only on the primary key, not on other non key columns.
Higher normal forms (BCNF, 4NF, 5NF) exist but are less common in everyday backend work.
In practice, you usually normalize up to 3NF, then sometimes denormalize for performance or historical reasons.

With this understanding, you can design safer, more maintainable database schemas for your backend applications.

9.11. Normalization

Why Normalization Matters

Types of Anomalies Normalization Solves

Insertion Anomalies

Update Anomalies

Deletion Anomalies

First Normal Form (1NF)

Definition of 1NF

Example of a Table Not in 1NF

Converting to 1NF Using a Separate Table

Repeating Groups Example

Functional Dependencies (Intuition)

Second Normal Form (2NF)

Definition of 2NF

Example of a Table Not in 2NF

Converting to 2NF

Third Normal Form (3NF)

Definition of 3NF

Example of a Table Not in 3NF

Converting to 3NF

Another 3NF Example: Address Data

Higher Normal Forms (Brief Overview)

Step by Step Normalization Example

Step 1: Ensure 1NF

Step 2: Check 2NF (Partial Dependencies)

Split into separate tables

Step 3: Check 3NF (Transitive Dependencies)

Normalization vs Performance: Trade Offs

How To Use Normalization When Designing Schemas

Summary

Comments

Where to Move