Database Normalization in ERD Design

What is Normalization?

Database normalization is the process of organizing a database schema to reduce data redundancy and improve data integrity. It involves decomposing tables into smaller, well-structured tables and defining relationships between them. The goal is a schema where every piece of data is stored in exactly one place.

First Normal Form (1NF)

A table is in 1NF if:

• Every column contains atomic (indivisible) values
• There are no repeating groups or arrays

Violation:

id	name	phone_numbers
1	Alice	555-0001, 555-0002

Fix: Create a separate phone_numbers table with a one-to-many relationship.

Second Normal Form (2NF)

A table is in 2NF if:

• It's already in 1NF
• Every non-key column depends on the entire primary key (no partial dependencies)

This is relevant for tables with composite primary keys.

Violation: In a table with PK (student_id, course_id), a column student_name depends only on student_id, not the full key.

Fix: Move student_name to the students table.

Third Normal Form (3NF)

A table is in 3NF if:

• It's already in 2NF
• No non-key column depends on another non-key column (no transitive dependencies)

Violation:

id	department_id	department_name

department_name depends on department_id, not directly on id.

Fix: Create a separate departments table and reference it via foreign key.

Normalization in ERD Design

When designing your ERD:

1. Start with a single entity containing all the data
2. Identify repeating groups → extract into new entities with 1:N relationships
3. Check composite keys → ensure non-key columns depend on the full key
4. Identify transitive dependencies → extract into lookup/reference tables

When to Denormalize

Sometimes performance requirements justify intentional denormalization:

• Caching computed values (e.g., order_total)
• Reducing JOIN-heavy queries in read-heavy systems
• Time-series or analytics tables

The key is to normalize first, then denormalize strategically with clear documentation.