Normalization and practical schema design

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Keep customer details in Customers and reference them from Orders.

Run the example

SELECT TOP (3) o.OrderID, o.CustomerID, c.FirstName, c.LastName
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
ORDER BY o.OrderID;

Read it step by step

1. Customers stores one row of descriptive details per CustomerID.
2. Orders stores the key, not a repeated customer name, and the join retrieves details when needed.
3. The foreign key enforces the relationship; ORDER BY selects a repeatable three-order sample.

Check the result

This design avoids updating a copied name in thousands of order rows.

What normalization fixes

Imagine an orders table that also stored FirstName, LastName, and City on every order row. Customer 1 has 250 orders, so Alex’s details would be copied 250 times. Normalization removes that repetition by storing each fact once, in the table whose key it describes.

• Update anomaly: if Alex moves city, every copied row must change. Missing one leaves the data contradicting itself.
• Insert anomaly: a new customer could not be recorded until they placed an order.
• Delete anomaly: deleting a customer’s only order would also delete the only record of that customer.

The normal forms describe this step by step. First normal form (1NF): each column holds one value, with no repeating groups such as Item1, Item2, and Item3 columns. Second normal form (2NF): with a composite key, every non-key column depends on the whole key. OrderItems has the key (OrderID, LineNumber); Quantity depends on both, but an OrderDate column there would depend only on OrderID, so it belongs in Orders. Third normal form (3NF): non-key columns depend on the key and nothing else. A City column in Orders would depend on CustomerID rather than on OrderID, so it belongs in Customers.