[Solved] You have a table storing customer orders with a column 'Items' that contains a list of item IDs as a... How do you redesign the schema to comply with 1NF and still keep track of all items per order? | SQL

SQL - Database Design and Normalization

You have a table storing customer orders with a column 'Items' that contains a list of item IDs as a string. How do you redesign the schema to comply with 1NF and still keep track of all items per order?

AStore item IDs as a JSON array in the Items column

BKeep the Items column as a comma-separated string but index it

CCreate an OrderItems table with OrderID and ItemID columns, one row per item

DAdd multiple columns like Item1, Item2, Item3 to store items

Step-by-Step Solution

Solution:

Step 1: Identify 1NF violation in Items column
Storing multiple item IDs as a string violates atomicity required by 1NF.
Step 2: Normalize by creating a separate table
Creating an OrderItems table with one item per row linked by OrderID ensures atomic values and tracks all items properly.
Final Answer:
Create an OrderItems table with OrderID and ItemID columns, one row per item -> Option C
Quick Check:
Separate rows for multi-values fix 1NF [OK]

Quick Trick: Use join tables for multi-items per order [OK]

Common Mistakes:

Using JSON arrays instead of separate rows
Adding multiple item columns (bad scalability)
Keeping comma-separated strings

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

How do you redesign the schema to comply with 1NF and still keep track of all items per order?

Step 1: Identify 1NF violation in Items column

Step 2: Normalize by creating a separate table

Final Answer:

Quick Check:

Want More Practice?