Normalizing a flat orders table to 3NF

WHAT THIS TESTS The interviewer wants you to apply normalization rules step by step and identify the functional dependencies driving each split, not just recite final tables.

A GOOD ANSWER COVERS First 1NF: values are atomic and there is a key; a single order can contain many products, so the natural key is composite (OrderID, ProductID). Second 2NF: remove partial dependencies, columns that depend on only part of that composite key. OrderDate and CustomerID depend on OrderID alone; ProductName depends on ProductID alone. Split these out. Third 3NF: remove transitive dependencies, where a non-key column depends on another non-key column. CustomerName and CustomerAddress depend on CustomerID, not on the order, so they belong in a Customers table.

The resulting design is Customers(CustomerID PK, CustomerName, CustomerAddress); Products(ProductID PK, ProductName); Orders(OrderID PK, OrderDate, CustomerID FK to Customers); and OrderItems(OrderID FK, ProductID FK, Quantity) with a composite primary key (OrderID, ProductID).

COMMON WRONG ANSWERS Leaving CustomerName in the Orders table, a transitive dependency that breaks 3NF. Skipping the junction table and trying to cram products into Orders. Naming tables correctly but failing to declare the OrderItems composite key or the foreign keys that preserve referential integrity.

LIKELY FOLLOW-UPS Which anomaly does each step prevent? Why is OrderItems necessary for a many-to-many between orders and products? When might you denormalize ProductName back for reporting?

ONE CONCRETE EXAMPLE Before normalization, changing a customer's address means updating every order row for that customer, and an inconsistency arises if one row is missed. After 3NF, the address lives once in Customers and a single update keeps every order correct.