Putting It Together: Designing a Robust SQL Server Table Set

Capstone Goal: Build a Small, Robust Schema

In this capstone, you will design and implement a compact order-entry schema that uses tables, relationships, constraints, defaults, and a computed column together as one coherent design. The focus is on translating requirements into a working set of tables and then proving the design by attempting both valid and invalid data changes.

1) Define the Domain and Translate Requirements into Tables

Domain: Customers, Orders, and Order Items

Assume a small online shop with these requirements:

• Each customer has a name and email. Email must be unique.
• A customer can place many orders.
• Each order has an order date/time and a status (e.g., New, Paid, Shipped, Cancelled).
• An order contains one or more order items.
• Each order item references a product, has a quantity, and captures the unit price at the time of purchase.
• Quantities must be positive. Prices must be non-negative.
• Order totals should be derivable from order items (avoid storing redundant totals unless you have a specific performance or auditing need).

Table Breakdown

• Sales.Customer: customer master data.
• Sales.Product: products available for purchase.
• Sales.[Order]: order header (who/when/status).
• Sales.OrderItem: order lines (what/how many/at what price).

Relationships:

• Customer (1) → Order (many)
• Order (1) → OrderItem (many)
• Product (1) → OrderItem (many)

2) Choose Column Data Types (with Practical Justification)

Below is a pragmatic set of choices for this domain. The goal is to balance correctness, storage, and usability.

• Surrogate keys: int for identity-based primary keys (compact and common for OLTP).
• Names: nvarchar(200) for customer names (supports international characters; length is bounded).
• Email: nvarchar(320) (practical upper bound for email addresses; enforce uniqueness with a constraint/index).
• Timestamps: datetime2(0) for order creation time (second precision is often enough; deterministic and modern).
• Status: varchar(20) with a CHECK constraint for allowed values (small set, ASCII-friendly).
• Money values: decimal(12,2) for prices (avoids rounding surprises; 12 digits total with 2 decimals supports typical retail ranges).
• Quantities: int with CHECK > 0 (whole units; adjust to decimal if fractional quantities are needed).

Design note: store UnitPrice on OrderItem rather than joining to Product at query time. This preserves the historical price used for that purchase.

Continue in our app.

• Listen to the audio with the screen off.
• Earn a certificate upon completion.
• Over 5000 courses for you to explore!

Or continue reading below...

Download the app

3) Implement Keys, Relationships, CHECK Constraints, and Defaults

Step A: Create a Dedicated Schema

CREATE SCHEMA Sales AUTHORIZATION dbo;

Step B: Create Master Tables

CREATE TABLE Sales.Customer (    CustomerID int IDENTITY(1,1) NOT NULL,    CustomerName nvarchar(200) NOT NULL,    Email nvarchar(320) NOT NULL,    CreatedAt datetime2(0) NOT NULL CONSTRAINT DF_Customer_CreatedAt DEFAULT (sysdatetime()),    CONSTRAINT PK_Customer PRIMARY KEY (CustomerID),    CONSTRAINT UQ_Customer_Email UNIQUE (Email));

CREATE TABLE Sales.Product (    ProductID int IDENTITY(1,1) NOT NULL,    ProductName nvarchar(200) NOT NULL,    ListPrice decimal(12,2) NOT NULL,    IsActive bit NOT NULL CONSTRAINT DF_Product_IsActive DEFAULT (1),    CreatedAt datetime2(0) NOT NULL CONSTRAINT DF_Product_CreatedAt DEFAULT (sysdatetime()),    CONSTRAINT PK_Product PRIMARY KEY (ProductID),    CONSTRAINT CK_Product_ListPrice_NonNegative CHECK (ListPrice >= 0));

Step C: Create Transaction Tables

CREATE TABLE Sales.[Order] (    OrderID int IDENTITY(1,1) NOT NULL,    CustomerID int NOT NULL,    OrderDate datetime2(0) NOT NULL CONSTRAINT DF_Order_OrderDate DEFAULT (sysdatetime()),    Status varchar(20) NOT NULL CONSTRAINT DF_Order_Status DEFAULT ('New'),    CONSTRAINT PK_Order PRIMARY KEY (OrderID),    CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerID)        REFERENCES Sales.Customer(CustomerID),    CONSTRAINT CK_Order_Status CHECK (Status IN ('New','Paid','Shipped','Cancelled')));

CREATE TABLE Sales.OrderItem (    OrderItemID int IDENTITY(1,1) NOT NULL,    OrderID int NOT NULL,    ProductID int NOT NULL,    Quantity int NOT NULL,    UnitPrice decimal(12,2) NOT NULL,    LineTotal AS (CONVERT(decimal(12,2), Quantity * UnitPrice)) PERSISTED,    CONSTRAINT PK_OrderItem PRIMARY KEY (OrderItemID),    CONSTRAINT FK_OrderItem_Order FOREIGN KEY (OrderID)        REFERENCES Sales.[Order](OrderID),    CONSTRAINT FK_OrderItem_Product FOREIGN KEY (ProductID)        REFERENCES Sales.Product(ProductID),    CONSTRAINT CK_OrderItem_Quantity_Positive CHECK (Quantity > 0),    CONSTRAINT CK_OrderItem_UnitPrice_NonNegative CHECK (UnitPrice >= 0));

Why These Constraints Matter in Combination

• IDENTITY + PK gives each row a stable identifier and supports efficient joins.
• FKs prevent orphaned rows (e.g., order items that do not belong to an order).
• CHECK constraints enforce domain rules at the database boundary (quantity and status rules cannot be bypassed by application bugs).
• Defaults reduce required input and standardize values (e.g., new orders default to Status='New').

4) Add a Computed Column for Derived Data

Sales.OrderItem.LineTotal is a computed column derived from Quantity * UnitPrice. Persisting it stores the computed value on disk, which can help when you frequently aggregate totals and want to index the derived value. It also ensures the value is always consistent with its inputs.

Key point: you are not storing an order header total here. Instead, you can compute order totals by summing line totals, which avoids redundancy and update anomalies.

SELECT oi.OrderID, SUM(oi.LineTotal) AS OrderTotalFROM Sales.OrderItem AS oiGROUP BY oi.OrderID;

5) Test the Design with Valid and Invalid Data

Step A: Insert Valid Rows

INSERT INTO Sales.Customer (CustomerName, Email)VALUES ('Ava Chen', 'ava.chen@example.com'),       ('Noah Patel', 'noah.patel@example.com');

INSERT INTO Sales.Product (ProductName, ListPrice)VALUES ('Wireless Mouse', 24.99),       ('Mechanical Keyboard', 89.50);

INSERT INTO Sales.[Order] (CustomerID)VALUES (1);

INSERT INTO Sales.OrderItem (OrderID, ProductID, Quantity, UnitPrice)VALUES (1, 1, 2, 24.99),       (1, 2, 1, 79.99);

Verify computed totals:

SELECT OrderItemID, Quantity, UnitPrice, LineTotalFROM Sales.OrderItemWHERE OrderID = 1;

Step B: Attempt Invalid Data to Confirm Constraints

1) Duplicate email (should fail due to unique constraint):

INSERT INTO Sales.Customer (CustomerName, Email)VALUES ('Duplicate Email', 'ava.chen@example.com');

2) Invalid order status (should fail due to CHECK constraint):

UPDATE Sales.[Order]SET Status = 'InTransit'WHERE OrderID = 1;

3) Negative price (should fail due to CHECK constraint):

INSERT INTO Sales.OrderItem (OrderID, ProductID, Quantity, UnitPrice)VALUES (1, 1, 1, -1.00);

4) Zero quantity (should fail due to CHECK constraint):

INSERT INTO Sales.OrderItem (OrderID, ProductID, Quantity, UnitPrice)VALUES (1, 1, 0, 24.99);

5) Orphaned order item (should fail due to FK constraint):

INSERT INTO Sales.OrderItem (OrderID, ProductID, Quantity, UnitPrice)VALUES (9999, 1, 1, 24.99);

Step C: Confirm Defaults Are Applied

SELECT OrderID, CustomerID, OrderDate, StatusFROM Sales.[Order]WHERE OrderID = 1;

You should see Status populated as New and OrderDate populated automatically.

Review Checklist: Validate the Final Design

Schema Qualification

• All objects created under the intended schema (e.g., Sales.Customer, not dbo.Customer).
• Queries and DDL consistently use two-part names (Schema.Object).

Constraint Coverage

• Every table has a primary key.
• All relationships are enforced with foreign keys.
• Domain rules are enforced with CHECK constraints (status values, positive quantities, non-negative prices).
• Uniqueness rules are enforced (customer email).

NULL Handling

• Columns that are required for correctness are NOT NULL (e.g., names, email, status, quantity).
• Optional attributes (if any) are explicitly allowed to be NULL and are handled intentionally in queries.
• Defaults are used where omission is common and safe (timestamps, initial status, active flag).

Inspecting the Design via System Views and Tooling

• List tables and schemas: sys.tables, sys.schemas.
• Inspect columns and types: sys.columns, sys.types.
• Review constraints: sys.key_constraints, sys.check_constraints, sys.default_constraints.
• Review relationships: sys.foreign_keys, sys.foreign_key_columns.

SELECT s.name AS SchemaName, t.name AS TableNameFROM sys.tables AS tJOIN sys.schemas AS s ON s.schema_id = t.schema_idWHERE s.name = 'Sales'ORDER BY t.name;

SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS TypeName, c.max_length, c.is_nullableFROM sys.tables AS tJOIN sys.columns AS c ON c.object_id = t.object_idJOIN sys.types AS ty ON ty.user_type_id = c.user_type_idJOIN sys.schemas AS s ON s.schema_id = t.schema_idWHERE s.name = 'Sales'ORDER BY t.name, c.column_id;