Advanced Normalization in MySQL

In the previous article, you learned:

  • What normalization is
  • Why is it required
  • 1NF and 2NF
  • Basic anomalies

Now we move one step further and complete the normalization discussion.

All examples apply to MySQL.

1. Third Normal Form (3NF)

A table is in Third Normal Form (3NF) if:

  1. It is already in 2NF.
  2. There is no transitive dependency.

What Is Transitive Dependency?

A transitive dependency occurs when:

A non-key column depends on another non-key column.

In simple terms:

Primary Key → Column A → Column B

Instead of:

Primary Key → Column B directly.

Example

Consider this table:

student_id

student_name

dept_id

dept_name

Primary Key: student_id

Problem:

  • dept_name depends on dept_id
  • dept_id depends on student_id

So:

student_id → dept_id → dept_name

This is a transitive dependency.

Converting to 3NF

Split into two tables:

Students

| student_id | student_name | dept_id |

Departments

| dept_id | dept_name |

Now:

  • dept_name depends only on dept_id
  • No transitive dependency
  • The table is in 3NF

2. Functional Dependency (Basic Introduction)

Functional dependency means:

If you know the value of one column, you can determine another column.

Example:

student_id → student_name

If student_id is known, student_name is uniquely determined.

Normalization is based on identifying and removing incorrect dependencies.

3. Practical Step-by-Step Example

Original unnormalized table:

| order_id | customer_name | customer_city | product_name | product_price |

Problems:

  • customer_city depends on customer_name
  • product_price depends on product_name
  • Data repetition

Step 1: Separate Customers

| customer_id | customer_name | customer_city |

Step 2: Separate Products

| product_id | product_name | product_price |

Step 3: Create Orders Table

| order_id | customer_id |

Step 4: Create Order_Items Table

| order_id | product_id |

Now:

  • No redundancy
  • No update anomaly
  • Clean relationships
  • Proper foreign keys

This structure is in 3NF.

4. Over-Normalization Problems

While normalization reduces redundancy, too much normalization can create issues:

  1. Too many tables
  2. Too many joins required
  3. Slower query performance
  4. Complex query writing

For example:

If every small detail is separated into different tables, retrieving data may require 6–7 joins.

This affects performance and readability.

5. When Denormalization Is Useful

Denormalization means intentionally introducing redundancy for performance reasons.

It is useful when:

  • The system is read-heavy
  • Complex joins slow down queries
  • Reporting queries are frequent
  • Performance is more important than strict normalization

Example:

Instead of joining Customers every time, store customer_name directly in Orders for faster reporting.

Denormalization is a performance optimization strategy, not a design mistake.

6. Normalization vs Performance Trade-Off

Normalization

Denormalization

Reduces redundancy

Increases redundancy

Improves consistency

Improves performance

Requires more joins

Fewer joins

Better for OLTP systems

Often used in reporting systems

Most real-world systems use:

Normalized design + selective denormalization

Summary

In this article, you learned:

  • Third Normal Form (3NF)
  • Transitive dependency
  • Functional dependency
  • Step-by-step normalization example
  • Over-normalization problems
  • When denormalization is useful

Normalization helps design clean, consistent databases.
Denormalization helps optimize performance when needed.