Mastering Database Normalization: Why and How to Apply 1NF, 2NF, and 3NF

Database Normalization

Why Use Normal Forms

Database normalization ensures reasonable design, affecting system architecture, development efficiency, and runtime performance. It involves structuring tables and their relationships.

Reasonable structure

Reduced redundancy

Avoids insert, delete, update anomalies

What Makes a Database Reasonable

Logical structure

Low redundancy

Minimized anomalies

Ensuring Design Quality

Follow established rules

In relational databases, these rules are the normal forms

What Is a Normal Form (NF)

A normal form is a set of design requirements. To build a well‑structured relational database, you must satisfy certain normal forms.

Classification of Normal Forms

First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) Boyce‑Codd Normal Form (BCNF) Fourth Normal Form (4NF) Fifth Normal Form (5NF)

Higher normal forms increase design quality but are harder to achieve. Generally, reaching 3NF avoids most anomalies.

Detailed Normal Forms

First Normal Form (1NF)

Definition

Each column must contain atomic (indivisible) values; a column cannot hold multiple values.

Example

Bad: User table (username, home address) Good: User table (username, province, city, detailed address)

Bad: Department (name, director, senior titles count) Good: Department (name, director, professor count, associate professor count)

Second Normal Form (2NF)

Definition

Every non‑key column must depend on the whole primary key, not just part of a composite key.

Example

Composite key: student_id + course_id Course name depends only on course_id, not on student_id.

Extract a Student table

Extract a Course table

Create an Enrollment table for selections

Student table illustration:

Course table illustration:

Enrollment table illustration:

Third Normal Form (3NF)

Definition

Every column must depend directly on the primary key, not indirectly.

No non‑key attribute depends on another non‑key attribute.

Example

Student‑Class table shows redundancy in class name and info.

Solution: separate Student and Class tables.

Advantages and Disadvantages of Normalization

Advantages:

Logical structure

Reduced redundancy

Minimizes insert, delete, update anomalies

Disadvantages:

Potential performance loss

Multi‑table queries can be slower than single‑table queries

Design should balance theory and practical needs; sometimes controlled redundancy improves performance, e.g., adding product name and price to a shopping‑cart item.

Relationships Between Tables

Three common relationships:

One‑to‑One – e.g., a team and its address. Implemented via foreign key or primary key.

One‑to‑Many / Many‑to‑One – e.g., a team has many players.

Many‑to‑Many – e.g., students and elective courses, handled via a junction table.

Summary of the Three Main Normal Forms

Normal forms guide data design to ensure quality:

1NF – fields are atomic.

2NF – no partial dependencies.

3NF – no transitive dependencies.

Applying normal forms reduces redundancy but may affect performance; selective denormalization can improve speed for specific cases.