Database Normalization

Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.

1. The Goals

Eliminate Redundant Data: Storing the same data in more than one place wastes space and creates maintenance nightmares (Update Anomalies).
Ensure Data Dependencies Make Sense: Data should be stored logically.

2. The Normal Forms

Normalization is progressive. To be in 2NF, you must first be in 1NF.

First Normal Form (1NF)

Atomicity: Each column must contain atomic values (no lists or comma-separated strings).
Uniqueness: Each row must be unique (Primary Key).
Example Violation: Storing "Red, Blue" in a Colors column.
Fix: Create a separate row for "Red" and "Blue", or a separate table.

Second Normal Form (2NF)

Prerequisite: Must be in 1NF.
No Partial Dependencies: All non-key attributes must depend on the entire primary key. This applies mainly when the Primary Key is composite (made of multiple columns).
Example Violation: Table OrderItems has PK (OrderId, ItemId) and columns ItemName, ItemPrice. ItemPrice depends only on ItemId, not OrderId.
Fix: Move ItemName and ItemPrice to a separate Items table.

Third Normal Form (3NF)

Prerequisite: Must be in 2NF.
No Transitive Dependencies: Non-key attributes must not depend on other non-key attributes.
Example Violation: Table Users has ZipCode and City. City depends on ZipCode. If ZipCode changes, City must change.
Fix: Move ZipCode and City to a Locations table.

Boyce-Codd Normal Form (BCNF)

A slightly stronger version of 3NF. It handles rare cases where a non-key attribute determines a part of the primary key.

3. Denormalization

Normalization is great for data integrity (Write-Optimized), but it can hurt read performance because retrieving data requires joining many tables.

Denormalization is the process of intentionally adding redundancy to the database to speed up reads.

Example: Storing UserCount on a Group table instead of running SELECT COUNT(*) FROM Users WHERE GroupID = ? every time.
Trade-off: You gain read speed but lose write speed (you have to update UserCount every time a user joins/leaves) and risk data inconsistency.

4. Summary

Form	Rule	Mnemonic
1NF	Atomic values	The Key
2NF	No partial dependencies	The Whole Key
3NF	No transitive dependencies	And Nothing But The Key

"The data depends on the key, the whole key, and nothing but the key, so help me Codd."

programming/database-basics programming/database-indexing programming/database-sharding

Backlinks

Database Views

Created: March 17, 2026 • Last Modified: January 31, 2026

Docs Login