What is Database Normalization?
Database normalization is the process of systematically organizing a database's structure to minimize data redundancy and improve data integrity. Proposed by Edgar F. Codd in the 1970s, this theory remains the foundation of relational database design.
Without normalization, databases suffer from three types of anomalies:
- Insertion Anomaly: Unintended data gets inserted along with desired data
- Deletion Anomaly: Deleting data unintentionally removes other important data
- Update Anomaly: Updating data in one place causes inconsistencies elsewhere
First Normal Form (1NF): Ensuring Atomicity
1NF Rule: All column values must be atomic (indivisible). A single column cannot hold multiple values.
1NF Violation Example:
| OrderID | CustomerName | Products |
|---|---|---|
| 1 | John Smith | Laptop, Mouse |
| 2 | Jane Doe | Keyboard |
The 'Products' column holds multiple values separated by commas — this violates 1NF.
After Applying 1NF:
| OrderID | CustomerName | Product |
|---|---|---|
| 1 | John Smith | Laptop |
| 1 | John Smith | Mouse |
| 2 | Jane Doe | Keyboard |
Each row now holds one atomic piece of data.
Second Normal Form (2NF): Eliminating Partial Dependencies
2NF Rule: Must be in 1NF, and every non-key attribute must be fully functionally dependent on the entire primary key.
This applies when a composite key (primary key made of multiple columns) exists.
2NF Violation Example:
| OrderID | ProductID | CustomerName | ProductName | Quantity |
|---|---|---|---|---|
| 1 | A | John Smith | Laptop | 1 |
| 1 | B | John Smith | Mouse | 2 |
The primary key is (OrderID, ProductID). However, CustomerName depends only on OrderID, and ProductName depends only on ProductID. These are partial dependencies.
After Applying 2NF:
Orders table:
| OrderID | CustomerName |
|---|---|
| 1 | John Smith |
Products table:
| ProductID | ProductName |
|---|---|
| A | Laptop |
| B | Mouse |
OrderItems table:
| OrderID | ProductID | Quantity |
|---|---|---|
| 1 | A | 1 |
| 1 | B | 2 |
Third Normal Form (3NF): Eliminating Transitive Dependencies
3NF Rule: Must be in 2NF, and no non-key attribute should transitively depend on the primary key.
In other words, there should be no A → B → C dependency chains.
3NF Violation Example:
| StudentID | StudentName | DeptCode | DeptName |
|---|---|---|---|
| 1 | Alice Brown | CS | Computer Science |
| 2 | Bob Green | EE | Electrical Engineering |
DeptName depends on DeptCode, and DeptCode depends on StudentID. Therefore, DeptName transitively depends on StudentID. If a department name changes, multiple rows need updating.
After Applying 3NF:
Students table:
| StudentID | StudentName | DeptCode |
|---|---|---|
| 1 | Alice Brown | CS |
| 2 | Bob Green | EE |
Departments table:
| DeptCode | DeptName |
|---|---|
| CS | Computer Science |
| EE | Electrical Engineering |
Pros and Cons of Normalization
Benefits
- Minimized Redundancy: Saves storage space by not repeating information
- Improved Data Integrity: Only one place to update when data changes
- Anomaly Elimination: Resolves insertion, deletion, and update anomalies
Drawbacks
- More JOIN Operations: Split tables require joins for data retrieval
- Increased Query Complexity: Simple queries may become more complex
- Potential Performance Impact: Excessive joins can slow down queries
Denormalization
Normalization isn't always the final answer. In performance-critical systems, denormalization — deliberately relaxing normalization — is sometimes applied. For example, storing frequently accessed combined data in a single table to reduce join operations.
In practice, find the right balance between normalization and performance. Normalize to 3NF first, then selectively denormalize only where performance issues are confirmed.
Practical Tips
When designing a database, aim for 3NF normalization. Erdmates' AI optimization feature can automatically detect normalization violations in your current ERD and suggest improvements.
Normalization is better understood through practice with real examples than through memorizing theory.