Designing tables: normalisation

You now know how to put tables back together with joins. This lesson is about the other half: deciding which tables to have in the first place. The rules are called normalisation, and their goal is simple: store every fact once.

What goes wrong in one big table

Here is an order sheet with the customer’s details on every line. Asha changes her email address, and the update is made for order 1 only:

1CREATE TABLE dbo.OrderSheet (2    OrderId   int           NOT NULL,3    ProductId int           NOT NULL,4    Customer  nvarchar(80)  NOT NULL,5    Email     nvarchar(100) NULL,6    Product   nvarchar(60)  NOT NULL,7    Quantity  int           NOT NULL,8    PRIMARY KEY (OrderId, ProductId)9);10INSERT INTO dbo.OrderSheet VALUES11    (1, 1, N'Asha Patel', N'asha@example.com', N'Claw hammer',       1),12    (1, 3, N'Asha Patel', N'asha@example.com', N'Tape measure 8 m',  2),13    (3, 2, N'Asha Patel', N'asha@example.com', N'Cordless drill',    1),14    (3, 8, N'Asha Patel', N'asha@example.com', N'Wood screws (200)', 2);15 16UPDATE dbo.OrderSheet SET Email = N'asha.p@example.com' WHERE OrderId = 1;17 18SELECT DISTINCT Customer, Email FROM dbo.OrderSheet;

Customer    Email
----------  ------------------
Asha Patel  asha.p@example.com
Asha Patel  asha@example.com

Asha now has two email addresses, and the database cannot tell you which is right. Repeated data causes three kinds of trouble, called anomalies:

The normal forms

A classic summary: every column must depend on the key, the whole key, and nothing but the key. In practice, ask of each column: “Is this a fact about what this table’s key identifies?” Email is a fact about a customer, not about an order line, so it belongs in Customers.

Putting it back together

Normalised tables do not lose the sheet: joins rebuild it whenever it is needed, from facts stored once:

1SELECT o.OrderId, o.OrderDate, c.FirstName + N' ' + c.LastName AS Customer,2       c.Email, p.Name AS Product, cat.Name AS Category, oi.Quantity3FROM dbo.Orders AS o4JOIN dbo.Customers  AS c   ON c.CustomerId   = o.CustomerId5JOIN dbo.OrderItems AS oi  ON oi.OrderId     = o.OrderId6JOIN dbo.Products   AS p   ON p.ProductId    = oi.ProductId7JOIN dbo.Categories AS cat ON cat.CategoryId = p.CategoryId8WHERE o.OrderId <= 39ORDER BY o.OrderId, p.ProductId;

OrderId  OrderDate   Customer    Email             Product            Category  Quantity
-------  ----------  ----------  ----------------  -----------------  --------  --------
      1  2024-02-03  Asha Patel  asha@example.com  Claw hammer        Tools            1
      1  2024-02-03  Asha Patel  asha@example.com  Tape measure 8 m   Tools            2
      2  2024-02-10  Ben Carter  NULL              Cement 25 kg       Building        10
      2  2024-02-10  Ben Carter  NULL              Sand 25 kg         Building        10
      3  2024-03-01  Asha Patel  asha@example.com  Cordless drill     Tools            1
      3  2024-03-01  Asha Patel  asha@example.com  Wood screws (200)  Fixings          2

Now the email change is one row in Customers, and every order sees it at once:

1UPDATE dbo.Customers SET Email = N'asha.p@example.com' WHERE CustomerId = 1;2 3SELECT o.OrderId, c.FirstName, c.Email4FROM dbo.Orders AS o5JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId6WHERE c.CustomerId = 1;

OrderId  FirstName  Email
-------  ---------  ------------------
      1  Asha       asha.p@example.com
      3  Asha       asha.p@example.com

Not every copy is a duplicate

OrderItems stores a UnitPrice, although Products has a Price. That is not a 3NF mistake, because they are different facts: the price paid on that day, and the price today. Cement was sold at a discount on order 7, and if the price rises tomorrow, old orders must keep the price they were charged:

1SELECT oi.OrderId, o.OrderDate, p.Name, oi.UnitPrice AS PricePaid, p.Price AS PriceToday2FROM dbo.OrderItems AS oi3JOIN dbo.Orders   AS o ON o.OrderId   = oi.OrderId4JOIN dbo.Products AS p ON p.ProductId = oi.ProductId5WHERE p.ProductId = 4;

OrderId  OrderDate   Name          PricePaid  PriceToday
-------  ----------  ------------  ---------  ----------
      2  2024-02-10  Cement 25 kg       7.20        7.20
      7  2024-05-05  Cement 25 kg       7.00        7.20

The same goes for a delivery address copied onto an order: the customer may move later, but the parcel went to the old address. Ask whether the value can legitimately differ from the original. If it can, it is a separate fact and it is fine to store it.

Designing a many-to-many relationship

BuildMart wants to record suppliers. A product can come from several suppliers and a supplier sells many products, so this is many-to-many and needs a junction table. The cost price depends on the pair (the same product costs different amounts from different suppliers), so it lives there:

1CREATE TABLE dbo.Suppliers (2    SupplierId int          NOT NULL IDENTITY PRIMARY KEY,3    Name       nvarchar(60) NOT NULL,4    Phone      varchar(20)  NULL5);6 7CREATE TABLE dbo.ProductSuppliers (8    ProductId  int           NOT NULL REFERENCES dbo.Products (ProductId),9    SupplierId int           NOT NULL REFERENCES dbo.Suppliers (SupplierId),10    CostPrice  decimal(10,2) NOT NULL,11    PRIMARY KEY (ProductId, SupplierId)12);

Denormalising on purpose

Normalised tables are the right default for data that changes (OLTP systems such as a shop). Sometimes you copy data deliberately:

• Reporting tables and data warehouses are read far more often than they are written, so they store pre-joined, repeated data to make reports fast.
• Snapshots such as UnitPrice above, which record a value as it was.
• Totals kept for speed, such as an order total stored next to its lines, but only with a reliable way (a trigger or a single code path) to keep it in step.

1CREATE TABLE dbo.Drivers (2    DriverId int          NOT NULL IDENTITY PRIMARY KEY,3    Name     nvarchar(60) NOT NULL,4    Phone    varchar(20)  NULL5);6 7CREATE TABLE dbo.Vans (8    VanId      int        NOT NULL IDENTITY PRIMARY KEY,9    Reg        varchar(8) NOT NULL UNIQUE,10    CapacityKg int        NOT NULL11);12 13CREATE TABLE dbo.Deliveries (14    DeliveryId   int  NOT NULL IDENTITY PRIMARY KEY,15    DeliveryDate date NOT NULL,16    DriverId     int  NOT NULL REFERENCES dbo.Drivers (DriverId),17    VanId        int  NOT NULL REFERENCES dbo.Vans (VanId)18);