Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 39 ยท Projects and Practice

Project: Order Management Database

Build an order management database step by step: schema and constraints, placing orders atomically, stock control, reporting queries and the T-SQL procedure behind each step.

In this project you design and build the core of a small online shop: customers, products, orders and order lines. You'll write the schema, place orders safely inside a transaction, keep stock correct, and produce reports. Along the way you apply most of what this course covered: transactions, stored procedures, indexes and permissions.

Each step shows the SQL Server (T-SQL) version as read-only code, and then a runnable portable version in the editor so you can see the result. The data model and queries are identical; only identity columns, procedures and error handling differ.

Step 1: the schema

erDiagram
  CUSTOMERS ||--o{ ORDERS : places
  ORDERS ||--|{ ORDER_LINES : contains
  PRODUCTS ||--o{ ORDER_LINES : "appears in"

Each order belongs to one customer and has one or more lines; each line points to a product.

T-SQL:

SQL
CREATE TABLE dbo.Customers (
    CustomerId int IDENTITY(1,1) CONSTRAINT PK_Customers PRIMARY KEY,
    FullName   nvarchar(100) NOT NULL,
    Email      nvarchar(200) NOT NULL CONSTRAINT UQ_Customers_Email UNIQUE,
    CreatedAt  datetime2(0)  NOT NULL CONSTRAINT DF_Customers_Created DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.Products (
    ProductId int IDENTITY(1,1) CONSTRAINT PK_Products PRIMARY KEY,
    Sku       varchar(20)   NOT NULL CONSTRAINT UQ_Products_Sku UNIQUE,
    Name      nvarchar(100) NOT NULL,
    Price     decimal(10,2) NOT NULL CONSTRAINT CK_Products_Price CHECK (Price >= 0),
    Stock     int           NOT NULL CONSTRAINT CK_Products_Stock CHECK (Stock >= 0)
);

CREATE TABLE dbo.Orders (
    OrderId    int IDENTITY(1,1) CONSTRAINT PK_Orders PRIMARY KEY,
    CustomerId int NOT NULL CONSTRAINT FK_Orders_Customers REFERENCES dbo.Customers (CustomerId),
    OrderedAt  datetime2(0) NOT NULL CONSTRAINT DF_Orders_At DEFAULT SYSUTCDATETIME(),
    Status     varchar(12) NOT NULL CONSTRAINT DF_Orders_Status DEFAULT 'pending'
               CONSTRAINT CK_Orders_Status CHECK (Status IN ('pending','paid','shipped','cancelled'))
);

CREATE TABLE dbo.OrderLines (
    OrderId   int NOT NULL CONSTRAINT FK_Lines_Orders   REFERENCES dbo.Orders (OrderId),
    ProductId int NOT NULL CONSTRAINT FK_Lines_Products REFERENCES dbo.Products (ProductId),
    Quantity  int NOT NULL CONSTRAINT CK_Lines_Qty CHECK (Quantity > 0),
    UnitPrice decimal(10,2) NOT NULL,
    CONSTRAINT PK_OrderLines PRIMARY KEY (OrderId, ProductId)
);
CREATE INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId) INCLUDE (OrderedAt, Status);

Design notes: UnitPrice is copied onto the line, so later price changes don't rewrite history; the CHECK constraints make bad data impossible; the foreign-key index on Orders (CustomerId) serves "orders of this customer".

Runnable version (SQLite types; AUTOINCREMENT stands in for IDENTITY) with sample data:

SQL Create the schema and load sample data

Press Run (or Ctrl + Enter) to see the result here.

Step 2: placing an order atomically

The procedure must do three things as one unit: create the order, add the lines, reduce stock. If any product lacks stock, nothing may change.

SQL
CREATE OR ALTER PROCEDURE dbo.PlaceOrder
    @CustomerId int,
    @ProductId  int,
    @Quantity   int,
    @OrderId    int OUTPUT
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        -- decrement stock only if enough is available; the CHECK constraint is the backup
        UPDATE dbo.Products
        SET    Stock = Stock - @Quantity
        WHERE  ProductId = @ProductId AND Stock >= @Quantity;

        IF @@ROWCOUNT = 0
            THROW 50001, 'Not enough stock or unknown product', 1;

        INSERT INTO dbo.Orders (CustomerId) VALUES (@CustomerId);
        SET @OrderId = SCOPE_IDENTITY();

        INSERT INTO dbo.OrderLines (OrderId, ProductId, Quantity, UnitPrice)
        SELECT @OrderId, ProductId, @Quantity, Price FROM dbo.Products WHERE ProductId = @ProductId;

        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH;
END;

The key trick is the conditional UPDATE ... WHERE Stock >= @Quantity: the check and the change happen in one atomic statement, so two buyers can't both take the last item (compare the lost update in isolation and locking).

The portable script does the same steps inside a transaction. First an order that succeeds:

SQL Place an order: decrement stock, add order and line

Press Run (or Ctrl + Enter) to see the result here.

Stock dropped from 12 to 10. Now try an order that asks for more than exists. The conditional update touches no row, so the script knows to stop:

SQL Insufficient stock changes nothing

Press Run (or Ctrl + Enter) to see the result here.

rows_updated is 0, the T-SQL @@ROWCOUNT = 0 check that triggers the THROW.

Step 3: reports

The shop owner wants answers. Load the data in each runnable block (they are independent), then ask questions.

Revenue per customer, ignoring cancelled orders (in T-SQL, the same query; use SUM(l.Quantity * l.UnitPrice)):

SQL Revenue per customer

Press Run (or Ctrl + Enter) to see the result here.

Best sellers and running total by day use window functions (window functions):

SQL Best sellers and cumulative revenue

Press Run (or Ctrl + Enter) to see the result here.

In SQL Server you'd wrap reports in views or inline functions, and give the reporting login SELECT on a Reports schema only.

Step 4: harden and tune

  1. Permissions: an AppRole with EXECUTE on PlaceOrder and no table access; a Reporting role with SELECT on report views.
  2. Indexes: check the plan for the revenue query; add IX_OrderLines_ProductId for product reports. A covering index on Orders (Status, OrderedAt) INCLUDE (CustomerId) helps date reports.
  3. Backups: full recovery model, nightly full, 15-minute log backups (backup plan).
  4. Concurrency: enable READ_COMMITTED_SNAPSHOT so reports never block order placement.

Extensions to try

  • Support orders with several products: pass a table-valued parameter of (ProductId, Quantity).
  • Add a CancelOrder procedure that returns stock and sets Status = 'cancelled' in one transaction.
  • Record every status change in an audit table with a trigger or a temporal table.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is UnitPrice copied onto the order line?

    An order records the price at the time of purchase; reading the live product price would rewrite history.
  2. 2. What makes "UPDATE ... SET Stock = Stock - @Qty WHERE Stock >= @Qty" safe against overselling?

    The condition is evaluated while the row is locked for the update, with no gap between read and write.
  3. 3. Why does the procedure use SET XACT_ABORT ON plus TRY...CATCH?

    Together they guarantee all-or-nothing behavior and report the error to the application.
  4. 4. Which role design follows least privilege for the web application?

    Procedure-only access limits what a compromised application account can do.

Summary

  • Constraints, foreign keys and copied prices make the data model trustworthy.
  • One transaction plus a conditional stock update makes ordering safe under concurrency.
  • Views, window functions and joins deliver the reports; permissions, indexes and backups harden the system.

Next: Project: inventory and audit trail