Normalization (1NF, 2NF, 3NF)
Database normalization is the process of structuring a database to reduce redundancy and improve data integrity. The normal forms (1NF, 2NF, 3NF) are progressive rules that guide you to a clean, non-redundant schema design.
Normalization Levels
- 1NF (First Normal Form) - no repeating groups, each cell one atomic value, has a PK
- 2NF (Second Normal Form) - 1NF + no partial dependency (every non-key column depends on the whole PK)
- 3NF (Third Normal Form) - 2NF + no transitive dependency (non-key columns depend ONLY on the PK, not on other non-key columns)
- BCNF (Boyce-Codd NF) - stronger 3NF, every determinant is a candidate key (rare requirement)
- Denormalization - intentionally breaking NF for performance
Normalization Examples
-- UNNORMALIZED (violates 1NF):
-- orders: id | user_name | user_email | products_ordered
-- 1 | John Doe | john@... | Laptop, Phone ← multiple values!
-- 1NF: atomic values, remove repeating groups
-- order_items: order_id | product_name | quantity
-- VIOLATES 2NF (partial dependency with composite PK):
-- order_items: order_id | product_id | product_name | quantity
-- ^^^^^^^^^^^^^ depends only on product_id!
-- 2NF: move product_name to products table
-- order_items: order_id | product_id | quantity
-- products: id | name | price | stock
-- VIOLATES 3NF (transitive dependency):
-- orders: id | user_id | user_city | user_country
-- ^^^^^^^^^ ^^^^^^^^^^^^ depend on user, not order!
-- 3NF: move user data to users table
-- orders: id | user_id | total_amount | status
-- users: id | name | email | city | country
-- Our ecommerce_db is 3NF compliant:
-- Each non-key column depends on its table's PK, nothing else
-- users: id → name, email, created_at
-- orders: id → user_id (FK), total_amount, status, created_at
-- products: id → name, price, stock
-- payments: id → order_id (FK), amount, method, paid_atQuick Quiz
Tip
Tip
Practice Normalization 1NF 2NF 3NF in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Eliminate redundancy - each fact stored once. 3NF covers most real-world needs.
Common Mistake
Warning
A common mistake with Normalization 1NF 2NF 3NF is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Normalization 1NF 2NF 3NF from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Database normalization is the process of structuring a database to reduce redundancy and improve data integrity.
- 1NF (First Normal Form) - no repeating groups, each cell one atomic value, has a PK
- 2NF (Second Normal Form) - 1NF + no partial dependency (every non-key column depends on the whole PK)
- 3NF (Third Normal Form) - 2NF + no transitive dependency (non-key columns depend ONLY on the PK, not on other non-key columns)