When to Break the Rules: Database Normalization in Practice

The three normal forms are the most fundamental design principles in database modeling. So, what exactly are the three normal forms? And in real-world development, must we strictly adhere to them? In this article, let’s discuss this topic in depth.

The Three Normal Forms

1. First Normal Form (1NF: Ensuring Atomicity in Each Column)

The First Normal Form requires that each field (column) in every table must be atomic, meaning the values in each field cannot be further divided. In other words, each field can only store a single value — it cannot contain sets, arrays, or repeating groups.

For example, consider the following student table:

Student ID	Name	Phone Number
1	Alice	123456789, 987654321
2	Bob	555555555

In this table, the Phone Number field contains multiple numbers, which violates 1NF's requirement for atomicity. To comply with 1NF, phone numbers should be separated into individual records or moved to a new table.

Design after conforming to 1NF:

Student Table

Student ID	Name
1	Alice
2	Bob

Phone Table

Phone ID	Student ID	Phone Number
1	1	123456789
2	1	987654321
3	2	555555555

2. Second Normal Form (2NF: Every Column Must Depend on the Entire Primary Key)

The Second Normal Form requires that the table already satisfy 1NF and eliminates partial dependencies — non-primary-key fields must depend on the entire primary key, not just part of it. This typically applies to tables with composite primary keys.

For example, consider the following OrderDetail table:

Order ID	Product ID	Product Name	Quantity	Unit Price
1001	A01	Apple	10	2.5
1001	A02	Orange	5	3.0
1002	A01	Apple	7	2.5

In this table, the composite primary key is (Order ID, Product ID). The Product Name and Unit Price depend only on Product ID, not the entire primary key, resulting in partial dependency — which violates 2NF.

Design after conforming to 2NF:

OrderDetail Table

Order ID	Product ID	Quantity
1001	A01	10
1001	A02	5
1002	A01	7

Product Table

Product ID	Product Name	Unit Price
A01	Apple	2.5
A02	Orange	3.0

3. Third Normal Form (3NF: Eliminate Transitive Dependencies)

The Third Normal Form requires that the table already satisfy 2NF and eliminates transitive dependencies — non-primary-key fields should not depend on other non-primary-key fields. In other words, every non-primary-key field must directly depend on the primary key, not indirectly through another non-primary-key field.

For example, consider the following Employee table:

Employee ID	Employee Name	Department ID	Department Name
E01	Alice	D01	Sales
E02	Bob	D02	Engineering
E03	Charlie	D01	Sales

In this table, Department Name depends on Department ID, which in turn depends on the primary key (Employee ID), forming a transitive dependency — violating 3NF.

Design after conforming to 3NF:

Employee Table

Employee ID	Employee Name	Department ID
E01	Alice	D01
E02	Bob	D02
E03	Charlie	D01

Department Table

Department ID	Department Name
D01	Sales
D02	Engineering

By moving the department information to a separate table, the transitive dependency is eliminated, and the database structure conforms to the Third Normal Form.

To summarize, the three normal forms are:

1NF: Ensure each field holds atomic values.
2NF: Eliminate partial dependencies — every non-key field must depend on the whole primary key.
3NF: Eliminate transitive dependencies — non-key fields should depend only on the primary key.

Violating the Three Normal Forms

In practice, while following the three normal forms (1NF, 2NF, 3NF) improves data consistency and reduces redundancy, there are cases where violating them can be beneficial — to improve performance, simplify design, or meet specific business requirements.

Below are common reasons and examples for intentionally violating normal forms:

Performance Optimization

In high-concurrency and large-scale applications, strictly following the normal forms may result in frequent join operations, which can increase query time and system load. To improve performance, designers may denormalize data to reduce joins.

For example, in an e-commerce system with Orders and Users tables, a strictly 3NF design would store only the User ID in the Orders table and require a join to retrieve user details.

To improve query performance, we might redundantly store user name and address in the Orders table to avoid joining the Users table.