Normalization (1NF–3NF)
Overview
Normalization is the process of organizing data to minimize redundancy and prevent inconsistencies. 1NF ensures atomic values (no lists in a column). 2NF removes partial dependencies (e.g., if a primary key is OrderID+ProductID, the ProductName shouldn't be in this table because it only depends on ProductID). 3NF removes transitive dependencies (e.g., City depends on ZipCode, so it shouldn't be stored directly under UserID).
If data isn't normalized, updating a customer's address might require changing 50 order records. Normalization ensures you update it in exactly one place.
Where used: schema design, reducing database size, preventing update anomalies
Why learn this
- You will design scalable database schemas that don't suffer from data anomalies.
- You understand why data is split into multiple tables (like `orders` and `order_items`).
Query walkthrough
SELECT c.name, p.name AS product FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id WHERE o.id = 4 ORDER BY p.name;Focus: Retrieving denormalized data required for display by joining normalized tables.
Common mistakes
- Over-normalization: Normalizing beyond 3NF when performance requires reading data together quickly. Sometimes denormalization is acceptable for analytics.
Glossary
- redundancy
- The unnecessary repetition of the same data in a database, which wastes space and can cause errors.
- inconsistencies
- Situations where the same piece of information is recorded differently in various places, causing confusion or errors.
- denormalization
- The process of intentionally adding redundancy back to a database to speed up complex read operations.
Recall questions
- What is the core principle of First Normal Form (1NF)?
- What does Second Normal Form (2NF) ensure?
- What does Third Normal Form (3NF) prevent?
Understanding checks
What rows does this return?
id: 1, country: US
Because of normalization, the `country` is stored once in `customers`, not duplicated in every `orders` row. We retrieve it via JOIN.
What rows does this return?
total: 89.99
Price is stored in `products` (3NF), not in `order_items`. We calculate the total dynamically.
Practice tasks
Normalize Employee Names
Given this query, change it to retrieve the employee's name and their manager's name by joining the `employees` table to itself, demonstrating how hierarchical data is normalized.
Challenge
Count Products per Category
Write a query that counts the number of products in each category. This relies on the normalized `products` table where category is an attribute.
Continue learning
Previous: Primary, foreign & unique keys