<style> .reveal-up, .reveal-left, .reveal-right, .reveal-stagger > * { opacity: 1 !important; transform: none !important; } </style>
  1. Learn
  2. SQL
  3. Master level
All lessons in this course

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Lesson 54 of 54 30 minMaster

Putting it together: designing an order-management database

Design and build a small order system from requirements: tables, keys and constraints that make bad data impossible, indexes for the real queries, price history with a temporal table, a PlaceOrder procedure with a table-valued parameter, a transaction and a race-proof stock check, cancelling, reporting, and an application user that can only run procedures.

This lesson uses almost everything in the course. BuildMart’s original tables were kept simple for teaching. Here you design the order side of the shop again, properly, in a new schema called shop, and justify every choice. Run the samples in order: each builds on the one before.

The requirements

  1. Customers have a unique email address and one or more delivery addresses, one of which may be the default.
  2. Each order goes to one of that customer’s addresses.
  3. An order has one or more lines. Each line records the price at the time of the order, even if the product’s price changes later.
  4. We must be able to say what any product cost on any past date.
  5. An order must never take more stock than there is, even when two customers buy the last item at the same moment.
  6. An order moves through Placed → Shipped, or Placed → Cancelled; a cancelled order returns its stock.
  7. The web application may only place, cancel and look up orders through procedures, never touch tables directly.

Tables, keys and constraints

Turn each requirement into structure. Where the database can enforce a rule, let it: a rule written in a constraint holds for every program, script and person, for ever.

Query.sql
CREATE SCHEMA shop;GOCREATE TABLE shop.Customers (    CustomerId int           IDENTITY CONSTRAINT PK_Customers PRIMARY KEY,    FirstName  nvarchar(40)  NOT NULL,    LastName   nvarchar(40)  NOT NULL,    Email      nvarchar(100) NOT NULL CONSTRAINT UQ_Customers_Email UNIQUE); CREATE TABLE shop.Addresses (    AddressId  int           IDENTITY CONSTRAINT PK_Addresses PRIMARY KEY,    CustomerId int           NOT NULL CONSTRAINT FK_Addresses_Customers REFERENCES shop.Customers,    Line1      nvarchar(100) NOT NULL,    City       nvarchar(40)  NOT NULL,    Postcode   varchar(10)   NOT NULL,    IsDefault  bit           NOT NULL CONSTRAINT DF_Addresses_IsDefault DEFAULT 0,    CONSTRAINT UQ_Addresses_Customer UNIQUE (AddressId, CustomerId));CREATE UNIQUE INDEX UX_Addresses_OneDefault ON shop.Addresses (CustomerId) WHERE IsDefault = 1; CREATE TABLE shop.Products (    ProductId int           IDENTITY CONSTRAINT PK_Products PRIMARY KEY,    Sku       varchar(20)   NOT NULL CONSTRAINT UQ_Products_Sku UNIQUE,    Name      nvarchar(60)  NOT NULL,    Price     decimal(10,2) NOT NULL CONSTRAINT CK_Products_Price CHECK (Price >= 0),    ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,    ValidTo   datetime2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo))WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = shop.ProductsHistory)); CREATE TABLE shop.Stock (    ProductId int NOT NULL CONSTRAINT PK_Stock PRIMARY KEY                           CONSTRAINT FK_Stock_Products REFERENCES shop.Products,    OnHand    int NOT NULL CONSTRAINT CK_Stock_OnHand CHECK (OnHand >= 0)); CREATE TABLE shop.Orders (    OrderId    int          IDENTITY CONSTRAINT PK_Orders PRIMARY KEY,    CustomerId int          NOT NULL,    AddressId  int          NOT NULL,    OrderedAt  datetime2(0) NOT NULL CONSTRAINT DF_Orders_OrderedAt DEFAULT SYSUTCDATETIME(),    Status     varchar(10)  NOT NULL CONSTRAINT DF_Orders_Status DEFAULT 'Placed'               CONSTRAINT CK_Orders_Status CHECK (Status IN ('Placed', 'Shipped', 'Cancelled')),    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId) REFERENCES shop.Customers (CustomerId),    CONSTRAINT FK_Orders_CustomerAddress FOREIGN KEY (AddressId, CustomerId)               REFERENCES shop.Addresses (AddressId, CustomerId)); CREATE TABLE shop.OrderLines (    OrderId   int           NOT NULL CONSTRAINT FK_OrderLines_Orders REFERENCES shop.Orders,    ProductId int           NOT NULL CONSTRAINT FK_OrderLines_Products REFERENCES shop.Products,    Quantity  int           NOT NULL CONSTRAINT CK_OrderLines_Quantity CHECK (Quantity > 0),    UnitPrice decimal(10,2) NOT NULL CONSTRAINT CK_OrderLines_UnitPrice CHECK (UnitPrice >= 0),    CONSTRAINT PK_OrderLines PRIMARY KEY (OrderId, ProductId));

The decisions, requirement by requirement:

RuleHow the design enforces it
Unique emailUQ_Customers_Email: a second customer with the same email fails.
At most one default addressA filtered unique index on CustomerId WHERE IsDefault = 1: many addresses per customer, only one marked default.
The address belongs to the customerFK_Orders_CustomerAddress is a two-column foreign key: the pair (AddressId, CustomerId) must exist in Addresses. That needs the UNIQUE (AddressId, CustomerId) on Addresses to point at.
Price at the time of the orderOrderLines has its own UnitPrice, copied when the order is placed. Changing a product’s price later does not change old orders.
Price on any past dateProducts is a temporal table. Stock is kept apart in shop.Stock: it changes with every order, and keeping a history version for each sale would bloat ProductsHistory.
Never negative stockCK_Stock_OnHand CHECK (OnHand >= 0), plus the stock check in the procedure below.
Valid status, sensible linesCHECK constraints on Status, Quantity and prices; the primary key (OrderId, ProductId) stops the same product appearing twice in one order.

Indexes for the real queries

Primary keys and unique constraints already have indexes. SQL Server does not index foreign key columns for you, and the shop will often ask “all orders for this customer, newest first” and “which orders contain this product?”:

Query.sql
CREATE INDEX IX_Orders_Customer ON shop.Orders (CustomerId, OrderedAt DESC) INCLUDE (Status);CREATE INDEX IX_OrderLines_Product ON shop.OrderLines (ProductId);CREATE INDEX IX_Addresses_Customer ON shop.Addresses (CustomerId);

The index on OrderLines.ProductId has a second job: when a product is deleted, SQL Server must check that no order line still refers to it, and without the index that check scans every line.

Some data

Copy BuildMart’s customers and products into the new tables (customers without an email get a placeholder) and give four customers an address:

Query.sql
INSERT INTO shop.Customers (FirstName, LastName, Email)SELECT FirstName, LastName, COALESCE(Email, LOWER(FirstName) + N'@example.org')FROM dbo.Customers ORDER BY CustomerId; INSERT INTO shop.Addresses (CustomerId, Line1, City, Postcode, IsDefault) VALUES    (1, N'12 Mill Lane', N'Leeds', 'LS1 4AB', 1),    (1, N'Unit 3, Canal Yard', N'Leeds', 'LS9 8QT', 0),    (2, N'5 Minster Row', N'York', 'YO1 7HH', 1),    (4, N'88 Manor Road', N'Bradford', 'BD8 9LE', 1); INSERT INTO shop.Products (Sku, Name, Price)SELECT CONCAT('BM-', FORMAT(ProductId, '000')), Name, PriceFROM dbo.Products ORDER BY ProductId; INSERT INTO shop.Stock (ProductId, OnHand)SELECT ProductId, Stock FROM dbo.Products; SELECT COUNT(*) AS Customers,       (SELECT COUNT(*) FROM shop.Addresses) AS Addresses,       (SELECT COUNT(*) FROM shop.Products) AS ProductsFROM shop.Customers;
Output
Customers  Addresses  Products
---------  ---------  --------
        8          4        12

The constraints are already earning their keep. A second default address for Asha, an address for a customer who does not exist, and a negative stock level are all refused:

Query.sql
INSERT INTO shop.Addresses (CustomerId, Line1, City, Postcode, IsDefault)VALUES (1, N'1 New Street', N'Leeds', 'LS2 1AA', 1); INSERT INTO shop.Addresses (CustomerId, Line1, City, Postcode)VALUES (99, N'Nowhere', N'York', 'YO1 1AA'); UPDATE shop.Stock SET OnHand = -1 WHERE ProductId = 1;
Output
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'shop.Addresses' with unique index 'UX_Addresses_OneDefault'. The duplicate key value is (1).
The statement has been terminated.
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Addresses_Customers". The conflict occurred in database "BuildMart", table "shop.Customers", column 'CustomerId'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 7
The UPDATE statement conflicted with the CHECK constraint "CK_Stock_OnHand". The conflict occurred in database "BuildMart", table "shop.Stock", column 'OnHand'.
The statement has been terminated.

Placing an order

An order arrives with any number of lines. A table-valued parameter passes them all to a procedure in one call: first define a table type, then declare a parameter of that type (it must be READONLY):

Query.sql
CREATE TYPE shop.OrderLineList AS TABLE (    ProductId int NOT NULL PRIMARY KEY,    Quantity  int NOT NULL CHECK (Quantity > 0));GOCREATE OR ALTER PROCEDURE shop.PlaceOrder    @CustomerId int,    @AddressId  int,    @Lines      shop.OrderLineList READONLY,    @OrderedAt  datetime2(0) = NULL,    @OrderId    int OUTPUTASBEGIN    SET NOCOUNT ON;    SET XACT_ABORT ON;     IF NOT EXISTS (SELECT 1 FROM @Lines)        THROW 50010, 'An order needs at least one line.', 1;     BEGIN TRY        BEGIN TRANSACTION;         -- Take the stock first: one statement checks and subtracts, so two orders        -- for the last item cannot both succeed.        UPDATE s        SET OnHand = s.OnHand - l.Quantity        FROM shop.Stock AS s        JOIN @Lines AS l ON l.ProductId = s.ProductId        WHERE s.OnHand >= l.Quantity;         IF @@ROWCOUNT <> (SELECT COUNT(*) FROM @Lines)            THROW 50011, 'Not enough stock for at least one line.', 1;         INSERT INTO shop.Orders (CustomerId, AddressId, OrderedAt)        VALUES (@CustomerId, @AddressId, COALESCE(@OrderedAt, SYSUTCDATETIME()));         SET @OrderId = SCOPE_IDENTITY();         INSERT INTO shop.OrderLines (OrderId, ProductId, Quantity, UnitPrice)        SELECT @OrderId, l.ProductId, l.Quantity, p.Price        FROM @Lines AS l        JOIN shop.Products AS p ON p.ProductId = l.ProductId;         COMMIT;    END TRY    BEGIN CATCH        IF @@TRANCOUNT > 0 ROLLBACK;        THROW;    END CATCH;END;
  • XACT_ABORT ON and TRY…CATCH (lesson 1): any error undoes the stock change, the order and its lines together.
  • The stock check is the UPDATE itself (lesson 2). Its WHERE keeps only lines with enough stock, and @@ROWCOUNT shows whether every line qualified. If two sessions want the last drill, the second one’s UPDATE waits for the first one’s lock, then finds OnHand = 0 and changes nothing. A separate SELECT to check stock first would allow a lost update.
  • Lock order (lesson 3): every order takes Stock rows first and in key order (the join reads @Lines by its primary key), which keeps deadlocks between orders unlikely.
  • UnitPrice comes from the database, not from the caller, so a tampered web page cannot set its own prices.
  • @OrderedAt is only there so this lesson’s orders get fixed dates; the application leaves it out and gets the current UTC time.

Asha orders a drill and two boxes of screws:

Query.sql
DECLARE @Lines shop.OrderLineList, @OrderId int;INSERT INTO @Lines (ProductId, Quantity) VALUES (2, 1), (8, 2); EXEC shop.PlaceOrder @CustomerId = 1, @AddressId = 1, @Lines = @Lines,                     @OrderedAt = '2024-07-01 10:15', @OrderId = @OrderId OUTPUT; SELECT o.OrderId, o.Status, p.Name, l.Quantity, l.UnitPrice, s.OnHand AS StockLeftFROM shop.Orders AS oJOIN shop.OrderLines AS l ON l.OrderId = o.OrderIdJOIN shop.Products AS p ON p.ProductId = l.ProductIdJOIN shop.Stock AS s ON s.ProductId = l.ProductIdWHERE o.OrderId = @OrderId;
Output
OrderId  Status  Name               Quantity  UnitPrice  StockLeft
-------  ------  -----------------  --------  ---------  ---------
      1  Placed  Cordless drill            1      89.00         11
      1  Placed  Wood screws (200)         2      11.90        148

Now the failures. Ben orders a drill and five boxes of wall plugs, which are out of stock. Then he tries to send an order to Asha’s address:

Query.sql
DECLARE @Lines shop.OrderLineList, @OrderId int;INSERT INTO @Lines (ProductId, Quantity) VALUES (2, 1), (9, 5); EXEC shop.PlaceOrder @CustomerId = 2, @AddressId = 3, @Lines = @Lines, @OrderId = @OrderId OUTPUT;GODECLARE @Lines shop.OrderLineList, @OrderId int;INSERT INTO @Lines (ProductId, Quantity) VALUES (2, 1); EXEC shop.PlaceOrder @CustomerId = 2, @AddressId = 1, @Lines = @Lines, @OrderId = @OrderId OUTPUT;GOSELECT (SELECT COUNT(*) FROM shop.Orders) AS Orders,       (SELECT OnHand FROM shop.Stock WHERE ProductId = 2) AS DrillsInStock;
Output
Msg 50011, Level 16, State 1, Procedure shop.PlaceOrder, Line 27
Not enough stock for at least one line.
Msg 547, Level 16, State 0, Procedure shop.PlaceOrder, Line 29
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_CustomerAddress". The conflict occurred in database "BuildMart", table "shop.Addresses".

Orders  DrillsInStock
------  -------------
     1             11

Both orders were refused, and nothing was half done: still one order, and the drill that each attempt took out of stock was put back by the rollback. The second error comes from the two-column foreign key, not from any code in the procedure.

Cancelling and price history

Cancelling must return the stock and may only happen to an order that has not shipped. The status check is part of the UPDATE, as with the stock check, so two cancels of the same order cannot both return the stock:

Query.sql
CREATE OR ALTER PROCEDURE shop.CancelOrder @OrderId intASBEGIN    SET NOCOUNT ON;    SET XACT_ABORT ON;    BEGIN TRY        BEGIN TRANSACTION;         UPDATE shop.Orders SET Status = 'Cancelled'        WHERE OrderId = @OrderId AND Status = 'Placed';         IF @@ROWCOUNT = 0            THROW 50020, 'Only a placed order can be cancelled.', 1;         UPDATE s SET OnHand = s.OnHand + l.Quantity        FROM shop.Stock AS s        JOIN shop.OrderLines AS l ON l.ProductId = s.ProductId        WHERE l.OrderId = @OrderId;         COMMIT;    END TRY    BEGIN CATCH        IF @@TRANCOUNT > 0 ROLLBACK;        THROW;    END CATCH;END;GODECLARE @Lines shop.OrderLineList, @OrderId int;INSERT INTO @Lines (ProductId, Quantity) VALUES (7, 2);EXEC shop.PlaceOrder @CustomerId = 4, @AddressId = 4, @Lines = @Lines,                     @OrderedAt = '2024-07-02 16:40', @OrderId = @OrderId OUTPUT; EXEC shop.CancelOrder @OrderId = @OrderId;SELECT @OrderId AS CancelledOrder;GOEXEC shop.CancelOrder @OrderId = 3;                  -- the same order againGOSELECT OrderId, Status FROM shop.Orders ORDER BY OrderId;SELECT OnHand AS PlywoodInStock FROM shop.Stock WHERE ProductId = 7;
Output
CancelledOrder
--------------
             3

Msg 50020, Level 16, State 1, Procedure shop.CancelOrder, Line 13
Only a placed order can be cancelled.

OrderId  Status
-------  ---------
      1  Placed
      3  Cancelled

PlywoodInStock
--------------
            80

The new order is number 3, not 2. The failed attempt that reached the INSERT used up identity value 2, and a rollback does not give identity values back. Gaps in order numbers are normal. The second cancel is refused, so the plywood went back into stock only once. Each call that can fail is in a batch of its own, because THROW ends the batch, as you saw in the error-handling lesson.

The drill’s price now goes up. Asha’s order keeps the price she paid, and the temporal table still knows the old price:

Query.sql
DECLARE @BeforeRise datetime2 = SYSUTCDATETIME();WAITFOR DELAY '00:00:00.100';UPDATE shop.Products SET Price = 94.50 WHERE ProductId = 2; SELECT p.Name, p.Price AS PriceNow,       old.Price AS PriceBefore,       (SELECT l.UnitPrice FROM shop.OrderLines AS l        WHERE l.OrderId = 1 AND l.ProductId = 2) AS PaidOnOrder1FROM shop.Products AS pJOIN shop.Products FOR SYSTEM_TIME AS OF @BeforeRise AS old ON old.ProductId = p.ProductIdWHERE p.ProductId = 2;
Output
Name            PriceNow  PriceBefore  PaidOnOrder1
--------------  --------  -----------  ------------
Cordless drill     94.50        89.00         89.00

Reporting

A view gives report writers order totals without repeating the joins and the rule that cancelled orders do not count as sales:

Query.sql
CREATE OR ALTER VIEW shop.OrderTotalsASSELECT o.OrderId, o.CustomerId, o.OrderedAt, o.Status,       COUNT(*) AS Lines,       SUM(l.Quantity * l.UnitPrice) AS TotalFROM shop.Orders AS oJOIN shop.OrderLines AS l ON l.OrderId = o.OrderIdGROUP BY o.OrderId, o.CustomerId, o.OrderedAt, o.Status;GODECLARE @Lines shop.OrderLineList, @OrderId int;INSERT INTO @Lines (ProductId, Quantity) VALUES (6, 40), (4, 10);EXEC shop.PlaceOrder @CustomerId = 4, @AddressId = 4, @Lines = @Lines,                     @OrderedAt = '2024-08-14 09:05', @OrderId = @OrderId OUTPUT; SELECT c.FirstName + N' ' + c.LastName AS Customer,       COUNT(*) AS Orders, SUM(t.Total) AS SpentFROM shop.OrderTotals AS tJOIN shop.Customers AS c ON c.CustomerId = t.CustomerIdWHERE t.Status <> 'Cancelled'GROUP BY c.CustomerId, c.FirstName, c.LastNameORDER BY Spent DESC;
Output
Customer    Orders   Spent
----------  ------  ------
Dev Singh        1  344.00
Asha Patel       1  112.80

The application’s account

Last, the security requirement (lesson 5). The web application’s user gets EXECUTE on the shop schema, which covers its procedures, and nothing else:

Query.sql
CREATE USER ShopWeb WITHOUT LOGIN;GRANT EXECUTE ON SCHEMA::shop TO ShopWeb; EXECUTE AS USER = 'ShopWeb'; DECLARE @Lines shop.OrderLineList, @OrderId int;INSERT INTO @Lines (ProductId, Quantity) VALUES (1, 1);EXEC shop.PlaceOrder @CustomerId = 2, @AddressId = 3, @Lines = @Lines,                     @OrderedAt = '2024-08-20 12:00', @OrderId = @OrderId OUTPUT;SELECT @OrderId AS NewOrderId; SELECT COUNT(*) FROM shop.Customers;DELETE FROM shop.Orders; REVERT;
Output
NewOrderId
----------
         5

Msg 229, Level 14, State 5, Line 12
The SELECT permission was denied on the object 'Customers', database 'BuildMart', schema 'shop'.
Msg 229, Level 14, State 5, Line 13
The DELETE permission was denied on the object 'Orders', database 'BuildMart', schema 'shop'.

ShopWeb placed an order (ownership chaining lets the procedure use tables ShopWeb cannot see) and could even use the table type, because EXECUTE on the schema includes its types. Reading customers or deleting orders directly is refused. An injection bug in the web site could do no more than call these procedures.

Interactive

The shop schema

shop.CustomersCustomerIdPKFirstNameLastNameEmailUQshop.AddressesAddressIdPKCustomerIdFKLine1CityPostcodeIsDefaultshop.OrdersOrderIdPKCustomerIdFKAddressIdFKOrderedAtStatusshop.OrderLinesOrderIdPK FKProductIdPK FKQuantityUnitPriceshop.ProductsProductIdPKSkuUQNamePriceValidFromValidToshop.ProductsHistory(same columns)shop.StockProductIdPK FKOnHand
shop.OrdersOne row per order: who, where to, when, and its status.
ColumnTypeKey
OrderIdint IDENTITYPK
CustomerIdintFK
AddressIdintFK
OrderedAtdatetime2(0)
Statusvarchar(10)
  • FK_Orders_CustomerAddress (AddressId, CustomerId) → Addresses: the address must be this customer’s (requirement 2).
  • CK_Orders_Status: Placed, Shipped or Cancelled only (requirement 6).
  • DF_Orders_OrderedAt: the current UTC time unless given.
  • IX_Orders_Customer (CustomerId, OrderedAt DESC) INCLUDE (Status): a customer’s recent orders.

Where each idea came from

Part of the designCourse module
Tables, data types, keys, CHECK, UNIQUE, DEFAULTCreating and changing data
Two-column foreign key, normalised tablesJoins and relationships
Totals per order and per customerGrouping and aggregation
Table-valued parameter, procedures, views, TRY…CATCHProgrammability
Indexes on foreign keys, covering index for the customer’s ordersIndexes and performance
Transaction, XACT_ABORT, atomic stock check, lock order, temporal history, least-privilege userThis module
Quick check

Why does PlaceOrder subtract stock with UPDATE … WHERE OnHand >= Quantity instead of first SELECTing the stock and then updating it?

Quick check

What stops an order being sent to another customer’s address?

Practice

Add the last status change: a procedure shop.ShipOrder @OrderId that moves a Placed order to Shipped and raises error 50030 'Only a placed order can be shipped.' otherwise. Ship Asha’s order 1, then try to ship it again and to cancel it, and show order 1’s status.

Show a hint

Like CancelOrder, put the status check in the UPDATE’s WHERE clause and test @@ROWCOUNT. No stock changes, so a transaction is not needed for one statement.

Show the solution
Solution.sql
CREATE OR ALTER PROCEDURE shop.ShipOrder @OrderId intASBEGIN    SET NOCOUNT ON;     UPDATE shop.Orders SET Status = 'Shipped'    WHERE OrderId = @OrderId AND Status = 'Placed';     IF @@ROWCOUNT = 0        THROW 50030, 'Only a placed order can be shipped.', 1;END;GOEXEC shop.ShipOrder @OrderId = 1;GOEXEC shop.ShipOrder @OrderId = 1;GOEXEC shop.CancelOrder @OrderId = 1;GOSELECT OrderId, Status FROM shop.Orders WHERE OrderId = 1;
Output
Msg 50030, Level 16, State 1, Procedure shop.ShipOrder, Line 10
Only a placed order can be shipped.
Msg 50020, Level 16, State 1, Procedure shop.CancelOrder, Line 13
Only a placed order can be cancelled.

OrderId  Status
-------  -------
      1  Shipped

Remember

  • Start from requirements, and enforce every rule the database can enforce with keys and constraints.
  • A two-column foreign key can tie a row to a parent that belongs to the same customer.
  • Filtered unique indexes enforce “at most one” rules, such as one default address.
  • Copy prices into order lines; keep price history in a temporal table; keep fast-changing data such as stock out of it.
  • Index foreign key columns and the queries the application really runs.
  • Table-valued parameters pass a whole order in one call.
  • Check and change stock in one UPDATE inside a transaction with XACT_ABORT, so concurrent orders cannot oversell.
  • Give the application EXECUTE on its procedures and nothing more.
0 of 54 lessons completed