Normalize a Table from 1NF to 3NF Step by Step
Fast recall
Quick revision
Core answer
Whether the learner can identify candidate keys and functional dependencies, then apply 1NF, 2NF, and 3NF to one relation
Splitting columns by intuition without stating dependencies or checking for a lossless join
Track one example through each normal form and name the dependency removed by every split
Interview-ready guide
Interview answer
Start with order_items_flat, whose candidate key is (order_id, product_id).
It contains quantity, product name, category id, category name, and a comma-separated tag list. For 1NF, move the tags to product_tags(product_id, tag) so each tag can have its own key and constraints. For 2NF, notice that product name and category id depend only on product_id, while quantity depends on the full order-and-product key.
Move those product facts to products and keep only product_id on the order line. For 3NF, category id determines category name inside Product. Move category name to categories(category_id, category_name) and leave category_id as a reference.
The final design has orders, order_items, products, product_tags, and categories. Each split follows a stated dependency, and the joins on the keys reconstruct the original facts. A value such as the unit price agreed at purchase time may correctly stay on order_items because that historical fact depends on the order line, not only on the product.
Normalize a Table from 1NF to 3NF Step by Step
Direct Answer
Normalize in three checks. First, make every row-column position hold one domain value and remove repeating groups. Next, identify every candidate key and move non-prime attributes that depend on only part of a key. Finally, apply the 3NF rule to every non-trivial dependency: the determinant must be a superkey or the dependent attribute must be prime. Verify that each decomposition is lossless.
Common Mistakes & Pitfalls
- Skipping candidate keys: the chosen primary key may not be the only candidate key.
- Naming a normal form without a dependency: state the determinant and dependent attribute that cause the violation.
- Losing facts during decomposition: verify that joins reconstruct the original relation without spurious rows.
- Treating every list as forbidden: JSON and array types can be deliberate; decide whether their members need independent constraints and relationships.
- Moving historical facts incorrectly: an order's agreed unit price may belong on the order line even when the current catalogue price belongs on Product.
Check your understanding
Answer these without looking back at the article.
- At what point might you intentionally stop normalizing and why?
- What does denormalization mean and when is it used?
- How does normalization relate to JOIN performance?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.