Normal Forms

Normalization (and it’s inverse, renormalization) is one of the foundational concepts in relational database design. It’s a systematic process of organizing data to reduce redundancy, eliminate anomalies, and ensure data integrity. Understanding normal forms is essential for any database professional, even if you don’t always apply them strictly in practice. This post talks about some of its history:

Data Models 1970

The journey from unnormalized data to sixth normal form is a progression of increasingly stringent rules, each solving specific types of data problems. Let’s explore each normal form with practical examples.

The Beginning: Unnormalized Data

Before we discuss normal forms, let’s see what unnormalized data looks like. Imagine a simple spreadsheet tracking customer orders:

This structure has multiple problems: repeating customer information, multiple values in single cells, and difficulty querying or updating data. Normalization fixes these issues systematically.

order_id	customer_name	customer_email	items	total
1001	John Doe	john@email.com	Widget A, Gadget B, Doohickey C	150.00
1002	Jane Smith	jane@email.com	Widget A, Widget B	75.00
1003	John Doe	john@email.com	Gadget B	50.00

First Normal Form (1NF)

Rule: Eliminate repeating groups and ensure each cell contains only atomic (indivisible) values.

First normal form requires that each column contain only one value, not lists or arrays. Every row must be unique, and there should be a primary key.

Converting our example to 1NF:

order_id	customer_name	customer_email	item	total
1001	John Doe	john@email.com	Widget A	150.00
1001	John Doe	john@email.com	Gadget B	150.00
1001	John Doe	john@email.com	Doohickey C	150.00
1002	Jane Smith	jane@email.com	Widget A	75.00
1002	Jane Smith	jane@email.com	Widget B	75.00
1003	John Doe	john@email.com	Gadget B	50.00

Second Normal Form (2NF)

Rule: Must be in 1NF, and all non-key attributes must depend on the entire primary key, not just part of it.

2NF addresses partial dependencies. If a table has a composite key, non-key attributes shouldn’t depend on only part of that key.

Converting our example to 2NF:

order_id	customer_id	total
1001	501	150.00
1002	502	75.00
1003	501	50.00

customer_id	customer_name	customer_email
501	John Doe	john@email.com
502	Jane Smith	jane@email.com

order_id	item	quantity
1001	Widget A	1
1001	Gadget B	1
1001	Doohickey C	1
1002	Widget A	1
1002	Widget B	1
1003	Gadget B	1

Third Normal Form (3NF)

Rule: Must be in 2NF, and no non-key attribute should depend on another non-key attribute (eliminate transitive dependencies).

3NF removes transitive dependencies where a non-key field depends on another non-key field rather than directly on the primary key.

order_id	customer_id	order_date	total
1001	501	2024-10-15	150.00
1002	502	2024-10-16	75.00
1003	501	2024-10-17	50.00

customer_id	customer_name	customer_email	zip_code
501	John Doe	john@email.com	94103
502	Jane Smith	jane@email.com	10001

zip_code	city	state
94103	San Francisco	CA
10001	New York	NY

order_id	product_id	quantity	line_total
1001	201	1	50.00
1001	202	1	60.00
1001	203	1	40.00
1002	201	1	50.00
1002	204	1	25.00
1003	202	1	60.00

product_id	product_name	price
201	Widget A	50.00
202	Gadget B	60.00
203	Doohickey C	40.00
204	Widget B	25.00

Most databases aim for 3NF as it provides a good balance between normalization benefits and practical usability. The remaining normal forms address more specific and less common scenarios.

Boyce-Codd Normal Form (BCNF)

Rule: Must be in 3NF, and every determinant must be a candidate key.

BCNF is a stricter version of 3NF that handles certain edge cases involving overlapping candidate keys. It’s sometimes called 3.5NF. Every determinant (left side of a functional dependency) is now a candidate key. Removed the anomaly where an instructor could be in multiple departments.

course_id	instructor	department
CS101	Dr. Smith	Comp Sci
CS102	Dr. Jones	Comp Sci
CS101	Dr. Brown	Comp Sci
MATH201	Dr. Smith	Mathematics

The problem in that instructor is to department, but instructor is not a candidate key. To fix this we introduce BCNF.

course_id	instructor
CS101	Dr. Smith
CS102	Dr. Jones
CS101	Dr. Brown
MATH201	Dr. Smith

instructor	department
Dr. Smith	Comp Sci
Dr. Jones	Comp Sci
Dr. Brown	Comp Sci

BCNF violations are less common than 3NF violations, and many databases that claim to be in 3NF are actually in BCNF.

Fourth Normal Form (4NF)

Rule: Must be in BCNF, and cannot have multi-valued dependencies that aren’t functional dependencies.

4NF addresses situations where a table contains two or more independent multi-valued facts about an entity.

employee_id	skill	language
101	Java	English
101	Java	Spanish
101	Python	English
101	Python	Spanish
102	SQL	French

The problem is that we have a multi-valued dependency as skills and languages are independent of each other.

employee_id	skill
101	Java
101	Python
102	SQL

employee_id	language
101	English
101	Spanish
102	French

This eliminates the cartesian product effect where every combination of skill and language creates a row.

Fifth Normal Form (5NF)

Rule: Must be in 4NF, and every join dependency must be implied by candidate keys.

5NF, also called Projection-Join Normal Form (PJNF), addresses cases where information can be reconstructed from smaller pieces but storing it together creates anomalies.

supplier	part	project
Acme	Widget	Project A
Acme	Gadget	Project A
Acme	Widget	Project B
Globex	Widget	Project A

The problem is a join dependency – this table can be reconstructed from smaller tables without loss of information

supplier	part
Acme	Widget
Acme	Gadget
Globex	Widget

part	project
Widget	Project A
Widget	Project B
Gadget	Project A

supplier	project
Acme	Project A
Acme	Project B
Globex	Project A

The original data can be reconstructed by joining these three tables. 5NF violations are rare and usually indicate complex business rules that might need rethinking.

Sixth Normal Form (6NF)

Rule: The table should only have a primary key and at most one other attribute.

6NF is the ultimate normalization, decomposing tables into irreducible components. It’s primarily theoretical and used in specific scenarios like temporal databases where you need to track when every single attribute changes independently.

employee_id	department	salary	hire_date
101	Engineering	85000	2020-01-15
102	Marketing	72000	2019-06-20
103	Sales	68000	2021-03-10

The problem si that we cannot track changes to individual attributes over time (temporal data).

employee_id	department	valid_from	valid_to
101	Engineering	2020-01-15	2022-05-01
101	Management	2022-05-01	NULL
102	Marketing	2019-06-20	NULL
103	Sales	2021-03-10	NULL

employee_id	salary	valid_from	valid_to
101	85000	2020-01-15	2021-01-01
101	92000	2021-01-01	2023-01-01
101	98000	2023-01-01	NULL
102	72000	2019-06-20	2022-07-01
102	78000	2022-07-01	NULL
103	68000	2021-03-10	NULL

employee_id	hire_date
101	2020-01-15
102	2019-06-20
103	2021-03-10

Each attribute has its own temporal tracking. This is essentially what Anchor Modeling implements. 6NF is rarely used in practice except for specific temporal database requirements.

Practical Considerations

Understanding normal forms is crucial, but applying them requires judgment.

Transactional databases (OLTP) generally benefit from normalization through 3NF or BCNF. This reduces redundancy, prevents update anomalies, and maintains data integrity. The additional joins are acceptable because transactions typically affect small numbers of rows.

Analytical databases (OLAP) often deliberately denormalize for query performance. Star schemas intentionally violate 3NF by storing redundant data in dimension tables. The read-heavy workload and large-scale aggregations make denormalization worthwhile.

Most production databases aim for 3NF as the practical target. This eliminates most redundancy and anomalies without excessive complexity. BCNF is good if achievable without contortions. 4NF and beyond are special cases addressing specific situations rather than general design goals.

Common Pitfalls

Pursuing higher normal forms without considering query patterns can create overly complex schemas requiring too many joins. The theoretical purity doesn’t justify the practical pain.

Stopping at 1NF or 2NF leaves significant redundancy and update anomalies. The short-term convenience of fewer tables creates long-term maintenance headaches.

Normalization decisions should consider how the data will be used. A heavily queried reporting database might deliberately denormalize for performance, while a system-of-record database normalizes for integrity.

The Bottom Line

Normal forms provide a framework for thinking about data organization and dependencies. They’re not arbitrary rules but logical progressions addressing specific types of data problems.

For most applications, we aim for 3NF in transactional systems and deliberately denormalize in analytical systems where appropriate. Understanding all the normal forms gives you the knowledge to make informed tradeoffs rather than blindly following rules.

The goal isn’t achieving the highest normal form possible, it’s designing databases that serve their intended purpose effectively. Sometimes that means aggressive normalization. Sometimes it means strategic denormalization. The normal forms give you the vocabulary and concepts to make these decisions intelligently.

To understand how this approach differs in denormalize in analytical systems, please read.

Star Schema