Database Normalization Explained: 1NF, 2NF, 3NF, and BCNF with examples

Database normalization is the process of organizing a relational schema so each fact is stored in the right place and data anomalies are reduced.

If you only remember one idea, remember this: normalization helps you avoid storing the same business fact in many rows where it can later become inconsistent.

This article follows Relational Data Modeling and prepares you for SQL Index Performance.

What is database normalization?

#

Normalization is a design technique used in relational databases to reduce:

• duplicated data;
• inconsistent updates;
• insert problems;
• delete problems;
• hidden dependencies.

It does this by splitting badly structured tables into smaller, well-defined tables connected by keys. The decomposition must be lossless: joining the resulting tables must reconstruct the original information without introducing spurious tuples.

Why normalization matters

#

A non-normalized schema often works at the beginning because it looks simple. But once the data grows, the same shortcut creates maintenance problems.

Normalization improves:

• data consistency;
• schema clarity;
• maintainability;
• integrity enforcement;
• long-term evolution of the database.

The three classic anomalies

#

Insert anomaly

#

You cannot add one fact without inventing another.

Imagine a table that mixes instructors and courses:

If a new instructor has not been assigned a course yet, where do you store them?

Update anomaly

#

The same value appears in many rows and must be updated everywhere.

Now the same person has two emails in the database.

Delete anomaly

#

Removing one row accidentally removes another important fact.

If the only row containing an instructor also contains their only course assignment, deleting that assignment may delete the instructor record entirely.

Key concepts before normal forms

#

Candidate key

#

A candidate key is any minimal set of attributes that uniquely identifies a row. An attribute belonging to at least one candidate key is prime; an attribute belonging to none is non-prime.

Primary key

#

The primary key is the candidate key chosen as the official row identifier.

Functional dependency

#

A functional dependency means that one attribute determines another.

If city -> region, then knowing the city is enough to know the region.

Partial dependency

#

A partial dependency exists when a non-prime attribute depends on a proper subset of a candidate key.

Transitive dependency

#

In the example below, a non-prime attribute depends on the key through another non-prime attribute: client_id -> city and city -> region imply client_id -> region.

First Normal Form (1NF)

#

Rule

#

A table is in 1NF when:

• each column stores atomic values;
• there are no repeating groups;
• each row can be uniquely identified.

Bad example

#

The products column stores a list, not a single value.

Better 1NF version

#

Now each field is atomic.

Second Normal Form (2NF)

#

Rule

#

A table is in 2NF when:

• it is already in 1NF;
• every non-prime attribute depends on the whole of every candidate key, not on a proper subset of any candidate key.

2NF requires checking all candidate keys: a simple primary key does not rule out partial dependencies on another, composite candidate key.

Bad example

#

Suppose the key is (product_id, sale_point_id):

Problems:

• product_name depends only on product_id;
• region depends only on sale_point_id.

That means the table contains partial dependencies.

Better 2NF version

#

products

sale_points

sales

Now each non-key attribute depends on the proper key.

Third Normal Form (3NF)

#

Rule

#

A table is in 3NF when:

• it is already in 2NF;
• for every non-trivial functional dependency X -> A, either X is a superkey (a set of attributes that uniquely identifies a row) or A is prime (belongs to a candidate key).

A dependency is non-trivial when A is not already part of X.

Bad example

#

If each city belongs to one region, then:

client_id -> city -> region

So region depends on client_id through city; client_id -> region still holds by transitivity. The problem is city -> region: city is not a superkey, and region is non-prime.

Better 3NF version

#

clients

cities

Now each table stores one type of fact.

Boyce-Codd Normal Form (BCNF)

#

Rule

#

A table is in BCNF when every determinant in a non-trivial functional dependency is a superkey. A superkey uniquely identifies a row but, unlike a candidate key, does not have to be minimal.

Example

#

Assume course is the only candidate key and room -> professor. Because room is not a superkey, the design violates BCNF. The dependency course -> room -> professor also violates 3NF here, since professor is non-prime; this example does not demonstrate a table that is in 3NF but not BCNF.

Better BCNF version

#

rooms

courses

Do you always need 4NF and 5NF?

#

Usually no.

For most transactional systems, getting to 3NF or BCNF is already enough. Fourth and fifth normal forms are useful in more specialized cases involving multivalued dependencies or join dependencies, but they are far less common in everyday application design.

Normalization vs denormalization

#

Normalization is about correctness and maintainability.

Denormalization is a deliberate trade-off for performance or reporting convenience.

Examples of denormalization:

• storing a precomputed total;
• duplicating a descriptive field to avoid a join in a read-heavy system;
• using summary tables for dashboards.

The key point is that denormalization should be intentional, documented, and measurable.

Common mistakes when studying normalization

#

Treating normalization as only theory

#

Normalization is not just for exams. It explains why some schemas become painful in production.

Splitting tables too early without understanding dependencies

#

You should split a table because of a dependency problem, not because “more tables feels more relational”.

Confusing 1NF with full normalization

#

Atomic columns are only the first step. A table can satisfy 1NF and still be poorly designed.

Ignoring query patterns completely

#

A normalized schema is usually the right starting point, but performance tuning still matters later. That is why indexing comes after modeling and normalization, not before.

FAQ

#

What is normalization in DBMS?

#

It is the process of structuring relational tables so that redundancy and data anomalies are reduced.

What are 1NF, 2NF, and 3NF?

#

They are successive normal forms:

• 1NF removes repeating groups and non-atomic values;
• 2NF removes partial dependencies of non-prime attributes on any candidate key;
• 3NF requires every non-trivial dependency X -> A to have a superkey X or a prime attribute A.

What is the difference between 3NF and BCNF?

#

BCNF is stricter: every determinant in a non-trivial functional dependency must be a superkey. 3NF also permits a non-superkey determinant when the dependent attribute is prime; BCNF does not.

Is normalization always good?

#

It is almost always the best design starting point. Later, if a proven performance issue appears, you can denormalize carefully.

Next step

#

After normalization, study SQL Index Performance to understand how a clean schema interacts with indexes, query plans, and real workload tuning.