Database Normalization Explained: From 1NF to 3NF with a Simple Example

Normalization is the process of organizing data in a database to reduce redundancy, avoid data anomalies, and maintain data integrity.

Instead of keeping all related data in one large table, we divide the data into smaller, well-structured tables.

Each table should represent a specific entity or relationship, and foreign keys are used to connect these tables.

Let's understand normalization step by step using a simple example of users purchasing courses.

---

Before Normalization

Suppose we initially store users and their purchased courses like this:

user_id	name	email	postal_code	city	course_ids	course_names	course_prices
1	Omar Hassan	[e-mail]	34511	Damietta	1, 3	Intro to Python, Data Science	99.99, 199.99
2	Yusuf Ahmed	[e-mail]	35511	Mansoura	2, 1	Web Development, Intro to Python	149.99, 99.99
3	Ibrahim Khalid	[e-mail]	31512	Tanta	3, 2	Data Science, Web Development	199.99, 149.99

At first, this may look convenient.

However, there is an important problem.

Some columns contain multiple values inside a single cell.

For example:

course_ids = 1, 3

and:

course_names = Intro to Python, Data Science

A single database field should not contain a list of separate values.

This means our current table does not satisfy First Normal Form.

---

1. First Normal Form (1NF)

A table is in First Normal Form when:

• Each column contains atomic values.
• Each cell contains only one value.
• There are no repeating groups.
• Each row represents a single record.
• Each row can be uniquely identified.

The main problem in our original table is that course information is stored as multiple values inside individual cells.

To fix this, we create a separate row for every user-course combination.

Table After 1NF

user_id	name	email	postal_code	city	course_id	course_name	course_price	purchase_price
1	Omar Hassan	[e-mail]	34511	Damietta	1	Intro to Python	99.99	99.99
1	Omar Hassan	[e-mail]	34511	Damietta	3	Data Science	199.99	199.99
2	Yusuf Ahmed	[e-mail]	35511	Mansoura	2	Web Development	149.99	149.99
2	Yusuf Ahmed	[e-mail]	35511	Mansoura	1	Intro to Python	99.99	99.99
3	Ibrahim Khalid	[e-mail]	31512	Tanta	3	Data Science	199.99	199.99
3	Ibrahim Khalid	[e-mail]	31512	Tanta	2	Web Development	149.99	149.99

Now every cell contains a single value.

For example:

course_id = 1

instead of:

course_ids = 1, 3

For this simplified example, we assume that a user purchases a specific course only once.

Therefore, we can use the following composite key to uniquely identify each row:

(user_id, course_id)

The table now satisfies 1NF.

However, we still have a lot of duplicated data.

For example, Omar's name, email, postal code, and city appear once for every course he purchases.

Course information is also repeated every time another user purchases the same course.

This leads us to Second Normal Form.

---

2. Second Normal Form (2NF)

A table must first satisfy 1NF before it can satisfy 2NF.

2NF deals with partial dependencies.

When a table has a composite key, every non-key column should depend on the entire key, not only part of it.

Our composite key is:

(user_id, course_id)

Now let's examine the dependencies.

User information:

user_id → name, email, postal_code, city

These columns depend only on user_id, not on the entire composite key.

Course information:

course_id → course_name, course_price

These columns depend only on course_id.

The purchase itself depends on the relationship between the user and course.

So we should separate these responsibilities into different tables.

---

users

id	name	email	postal_code	city
1	Omar Hassan	[e-mail]	34511	Damietta
2	Yusuf Ahmed	[e-mail]	35511	Mansoura
3	Ibrahim Khalid	[e-mail]	31512	Tanta

User information is now stored only once.

---

courses

id	name	price
1	Intro to Python	99.99
2	Web Development	149.99
3	Data Science	199.99

Course information is also stored only once.

---

purchases

id	user_id	course_id	purchase_price
1	1	1	99.99
2	1	3	199.99
3	2	2	149.99
4	2	1	99.99
5	3	3	199.99
6	3	2	149.99

The purchases table now represents the relationship between users and courses.

At this point:

• User information belongs to users.
• Course information belongs to courses.
• Purchase information belongs to purchases.

---

Why Store purchase_price?

You may notice that we have:

courses.price

and:

purchases.purchase_price

This is intentional.

They represent two different pieces of information.

courses.price represents the current price of the course.

purchases.purchase_price represents the price paid by the user at the time of purchase.

For example, suppose:

Intro to Python = 99.99

Omar purchases the course for:

99.99

Later, the course price changes to:

129.99

The current course record becomes:

courses.price = 129.99

But Omar's old purchase should still contain:

purchases.purchase_price = 99.99

Historical transaction data should not change just because the current product price changes.

---

3. Third Normal Form (3NF)

A table must first satisfy 2NF before it can satisfy 3NF.

3NF deals with transitive dependencies.

In simple terms:

A non-key column should not depend on another non-key column.

Consider our current users table:

id	name	email	postal_code	city
1	Omar Hassan	[e-mail]	34511	Damietta
2	Yusuf Ahmed	[e-mail]	35511	Mansoura
3	Ibrahim Khalid	[e-mail]	31512	Tanta

For this example, we assume that every postal code identifies one city.

Therefore:

postal_code → city

And we also have:

user_id → postal_code

This gives us:

user_id → postal_code → city

The city value indirectly depends on the user through postal_code.

This is a transitive dependency.

We can move this information into a separate table.

---

cities

id	postal_code	name
1	34511	Damietta
2	35511	Mansoura
3	31512	Tanta

Now the users table only needs to reference the appropriate city.

---

users After 3NF

id	name	email	city_id
1	Omar Hassan	[e-mail]	1
2	Yusuf Ahmed	[e-mail]	2
3	Ibrahim Khalid	[e-mail]	3

Now city-related information is stored in one place.

Instead of repeating:

34511 | Damietta

for every user from Damietta, the user simply references:

city_id = 1

---

Final Schema

After applying 1NF, 2NF, and 3NF, our final database contains four main tables.

users

users
-----
id
name
email
city_id

cities

cities
------
id
postal_code
name

courses

courses
-------
id
name
price

purchases

purchases
---------
id
user_id
course_id
purchase_price

---

Foreign Keys

The relationships between the tables are created using foreign keys.

users.city_id → cities.id

purchases.user_id → users.id

purchases.course_id → courses.id

These foreign keys help maintain referential integrity.

For example, a purchase should not reference a user that does not exist.

---

Relationships

cities → users

cities
  |
  | 1
  |
  | *
users

One city can have many users.

---

users → purchases

users
  |
  | 1
  |
  | *
purchases

One user can have many purchases.

---

courses → purchases

courses
  |
  | 1
  |
  | *
purchases

One course can appear in many purchases.

---

Complete Relationship

cities
  |
  | 1
  |
  | *
users
  |
  | 1
  |
  | *
purchases
  | *
  |
  | 1
courses

We can also think of the purchases table as the bridge between users and courses:

users
  |
  | 1
  |
  | *
purchases
  | *
  |
  | 1
courses

This means users and courses effectively have a many-to-many relationship through purchases.

---

Normalization Flow

The complete process looks like this:

Unnormalized Data
Multiple values inside cells
        |
        v
       1NF
One atomic value per cell
One user-course combination per row
        |
        v
       2NF
Remove partial dependencies
        |
        +---- users
        |
        +---- courses
        |
        +---- purchases
        |
        v
       3NF
Remove transitive dependencies
        |
        +---- cities
        |
        v
Final Normalized Schema

---

What Did Each Normal Form Solve?

Before 1NF

We had multiple values inside a single cell:

course_ids = 1, 3

This makes querying, updating, and maintaining the data difficult.

---

After 1NF

We converted those values into separate rows:

user_id | course_id
1       | 1
1       | 3

Now every field contains one atomic value.

---

After 2NF

We removed partial dependencies.

Instead of repeating:

Omar Hassan
[e-mail]

for every course Omar purchases, we store that information once in:

users

Similarly, course information is stored once in:

courses

And the relationship is stored in:

purchases

---

After 3NF

We removed the transitive dependency:

user_id → postal_code → city

City-related information is now stored separately in:

cities

and users reference it using:

city_id

---

Why Normalization Matters

Without normalization, databases can suffer from several common problems.

Update Anomaly

Suppose Data Science is stored in 100 purchase rows.

If its name changes, we may need to update 100 rows.

If one row is missed, the database becomes inconsistent.

With normalization, the course name exists once in courses.

---

Insert Anomaly

Suppose we want to add a new course before anyone purchases it.

If course information exists only inside a purchase table, we may not be able to store the course until someone buys it.

With a separate courses table, we can create the course independently.

---

Delete Anomaly

Suppose the last user who purchased a course deletes their purchase.

If course information is stored only in that purchase row, deleting the purchase may accidentally delete all information about the course.

With normalization, deleting a purchase does not delete the course itself.

---

Summary

Normalization helps structure database data in a way that reduces duplication and improves consistency.

In our example:

• 1NF removed multiple values from individual cells.
• 2NF removed partial dependencies by separating users, courses, and purchases.
• 3NF removed the transitive dependency between postal codes and cities.

Our final schema contains:

users
cities
courses
purchases

Each table has a clear responsibility:

• users stores user information.
• cities stores city and postal code information.
• courses stores course information.
• purchases stores user-course transactions.

The goal of normalization is not simply to create more tables.

The goal is to make sure each piece of information is stored in the correct place, reducing redundancy and preventing update, insert, and delete anomalies.