[Solved] You have a table storing orders with multiple products in one column:Orders(OrderID INT, Customer VARCHAR(50), Products VARCHAR(255))Products column stores product IDs separated by commas.... | SQL

SQL - Database Design and Normalization

You have a table storing orders with multiple products in one column:

Orders(OrderID INT, Customer VARCHAR(50), Products VARCHAR(255))
Products column stores product IDs separated by commas.

How do you redesign the schema to fully comply with First Normal Form (1NF)?

ACreate a new table OrderProducts(OrderID, ProductID) with one product per row.

BSplit Products into multiple columns like Product1, Product2, Product3.

CStore Products as JSON array in the same column.

DKeep Products column but restrict to one product ID only.

Step-by-Step Solution

Solution:

Step 1: Identify 1NF violation in current design
Storing multiple product IDs in one column violates 1NF because values are not atomic.
Step 2: Choose redesign that enforces atomicity
Create a new table OrderProducts(OrderID, ProductID) with one product per row. creates a new table with one product per row, fully normalizing the data and satisfying 1NF. Split Products into multiple columns like Product1, Product2, Product3. uses multiple columns but is limited and not scalable. Options C and D still violate 1NF.
Final Answer:
Create a new table OrderProducts(OrderID, ProductID) with one product per row. -> Option A
Quick Check:
Separate repeating data into rows for 1NF compliance [OK]

Quick Trick: Use separate table with one value per row for 1NF [OK]

Common Mistakes:

Adding multiple columns instead of rows
Using JSON or arrays inside one column
Restricting to one product but losing data

Master "Database Design and Normalization" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

You have a table storing orders with multiple products in one column:

Step 1: Identify 1NF violation in current design

Step 2: Choose redesign that enforces atomicity

Final Answer:

Quick Check:

Want More Practice?