Master Database Normalization: Understanding 1NF, 2NF, 3NF and Their Trade‑offs

1 Database Normalization

1.1 Why Normalization?

Normalization is required to ensure reasonable database design, which impacts the overall system architecture, development efficiency, and runtime performance.

Database design influences the whole system's structure and subsequent development and operation efficiency.

Design includes table structures and the relationships between tables.

1.2 What Makes a Database Reasonable?

Logical structure

Low redundancy

Minimize insert, delete, and update anomalies

1.3 How to Guarantee Database Design Quality?

Follow certain rules

In relational databases these rules are called normal forms

1.4 What Is a Normal Form (NF = NormalForm)?

Normal forms are design requirements that a database must satisfy to be considered well‑structured.

A normal form summarizes a specific design constraint.

To design a reasonable relational database, certain normal forms must be met.

1.5 Types of Normal Forms

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Boyce‑Codd

Normal Form (BCNF) is a refinement of 3NF.

Fourth Normal Form (4NF)

Fifth Normal Form (5NF)

Higher normal forms are increasingly difficult to achieve, but achieving up to 3NF generally avoids most anomalies.

2 Detailed Normal Forms

2.1 First Normal Form (1NF)

2.1.1 Definition

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

Each column must be atomic

2.1.2 Example

Invalid: User table (username, home address).

Valid: User table (username, province, city, detailed address).

Invalid: Department table (department name, department head, number of senior titles).

Valid: Department table (department name, department head, number of professors, number of associate professors).

2.2 Second Normal Form (2NF)

2.2.1 Definition

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

2.2.2 Example

Composite key: student ID + course ID.

Course name depends only on course ID, not on student ID.

Solution: extract separate tables for students, courses, and enrollment records.

Student table

Course table

Enrollment table

2.3 Third Normal Form (3NF)

2.3.1 Definition

All columns must depend directly on the primary key; no transitive (indirect) dependencies.

2.3.1 Example

Student‑class table shows redundancy in class name and class information.

Solution: separate student table and class table.

2.4 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 normalization with performance needs; sometimes controlled redundancy is acceptable.

2.5 Relationships Between Database Tables

Three types of relationships:

One‑to‑one – e.g., a team and its address.

One‑to‑many / many‑to‑one – e.g., a team and its players.

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

2.6 Summary of the Three Main Normal Forms

Normalization guides data design to ensure quality:

First Normal Form: columns must be atomic.

Second Normal Form: no partial dependencies on a composite key.

Third Normal Form: no transitive dependencies.

Using normal forms reduces redundancy but may affect performance; in some cases, violating 3NF for controlled redundancy can improve speed.