Inventory Management Database

Design and query a multi-table inventory system with suppliers, products, and stock levels.

Table DesignJoinsConstraints

Overview

A warehouse doesn't just track "how many of this product do we have" — it tracks who supplies it, what the product actually is, and how much of it sits in stock right now, and those are three different kinds of facts that belong in three different tables. Cramming a supplier's name and address directly onto every product row would mean repeating that same supplier information on every single product they supply, and updating an address would require finding and fixing every one of those rows. This project builds the normalized alternative: a `suppliers` table, a `products` table that references a supplier, and a `stock_levels` table that tracks quantity separately from either.

By the end of this tutorial you will have a three-table T-SQL schema — `suppliers`, `products`, and `stock_levels` — wired together with `FOREIGN KEY` constraints and `IDENTITY(1,1)` surrogate keys, plus `CHECK` constraints that make it structurally impossible for a stock quantity to go negative. You will also write the join queries that turn three tables of raw IDs into a readable inventory report.

What You'll Build

A `suppliers` table with an `IDENTITY(1,1)` primary key and a unique contact email.
A `products` table with a foreign key to `suppliers` and a `CHECK`-constrained unit price.
A `stock_levels` table tracking on-hand quantity per product, with a `CHECK (quantity_on_hand >= 0)`.
Foreign keys enforcing that every product has a real supplier and every stock row has a real product.
Three-way `JOIN` queries connecting suppliers, products, and current stock.
A low-stock report using `TOP N` to surface the products that need reordering soonest.

Prerequisites

CREATE TABLE basics in T-SQL — columns, data types (`INT`, `NVARCHAR`, `DECIMAL`, `DATE`), and `NOT NULL`.
Primary keys and foreign keys — what `PRIMARY KEY` and `FOREIGN KEY ... REFERENCES` enforce.
Basic SELECT — filtering with `WHERE`, sorting with `ORDER BY`, and limiting rows with `TOP N`.
The INSERT statement — adding rows with `INSERT INTO table (columns) VALUES (...)`.
A first look at JOIN — the idea that two tables can be combined on a matching column.

Project Structure

`suppliers` is the independent entity at the top — it doesn't need to know anything about products. `products` depends on `suppliers` through a foreign key. `stock_levels` depends on `products`, and is kept as its own table (rather than a column on `products`) because in a larger system stock is often tracked per warehouse location, so separating it now leaves room to grow without redesigning the schema later.

Table	Purpose	Key Columns
suppliers	One row per supplier company	supplier_id (PK), contact_email (UNIQUE)
products	One row per product in the catalog	product_id (PK), supplier_id (FK), sku (UNIQUE), unit_price
stock_levels	One row per product's current stock	stock_id (PK), product_id (FK), quantity_on_hand, reorder_threshold

Step 1: Create the Suppliers Table

`supplier_id` uses `IDENTITY(1,1)` — SQL Server's auto-numbering column property, starting at 1 and incrementing by 1 with every insert. It is a surrogate key: an ID with no real-world meaning, chosen purely because a company name can change (or two suppliers can share a name) and would make a poor primary key. `contact_email` gets its own `UNIQUE` constraint on top of that, so no two supplier records can be created with the same contact address by mistake.

CREATE TABLE suppliers (
    supplier_id INT IDENTITY(1,1) PRIMARY KEY,     -- Surrogate key: auto-numbered, no real-world meaning, safe to reference elsewhere
    supplier_name NVARCHAR(100) NOT NULL,          -- NVARCHAR stores Unicode text, safe for supplier names in any language
    contact_email NVARCHAR(100) NOT NULL UNIQUE,   -- UNIQUE, not just NOT NULL: no two suppliers may share a contact address
    phone VARCHAR(20) NULL,                        -- Optional; not every supplier record needs a phone number on file
    created_at DATETIME2 NOT NULL DEFAULT GETDATE() -- Stamped automatically the moment the row is inserted
);

Confirmation

Click Run to see what this code prints.

Step 2: Create the Products Table

`sku` (stock keeping unit) is what a human actually looks up on a shelf label, so it gets `UNIQUE` even though `product_id` remains the primary key everything else references — the same "surrogate key plus a separately-unique human-facing code" pattern used for `suppliers.contact_email`. The `CHECK` constraint on `unit_price` stops an obviously invalid value (a negative price) from ever being written, no matter what application code eventually inserts into this table. `supplier_id` is the foreign key that ties every product back to the supplier that provides it.

CREATE TABLE products (
    product_id INT IDENTITY(1,1) PRIMARY KEY,        -- Surrogate key, referenced by stock_levels.product_id
    supplier_id INT NOT NULL,                        -- FK: which supplier provides this product
    sku VARCHAR(20) NOT NULL UNIQUE,                 -- Human-facing code printed on the shelf label, e.g. 'SKU-CBL-01'
    product_name NVARCHAR(150) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,               -- DECIMAL, not FLOAT, so money never suffers rounding error
    CONSTRAINT FK_products_suppliers FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id) ON DELETE NO ACTION,  -- Can't delete a supplier that still has products on file
    CONSTRAINT CK_products_price CHECK (unit_price >= 0)         -- A product can never have a negative price
);

Step 3: Create the Stock Levels Table

Every column that matters here is either the foreign key back to `products` or a number the warehouse cares about. `quantity_on_hand` is protected by a `CHECK` constraint so a buggy decrement (shipping more units than exist) can never quietly write a negative count. `reorder_threshold` is what the low-stock report in the Sample Queries section compares against, so the business logic for "needs reordering" lives entirely in a query, not scattered across application code.

CREATE TABLE stock_levels (
    stock_id INT IDENTITY(1,1) PRIMARY KEY,          -- Own surrogate key for this specific stock record
    product_id INT NOT NULL UNIQUE,                  -- FK: one stock row per product (UNIQUE enforces that one-to-one)
    quantity_on_hand INT NOT NULL DEFAULT 0,         -- Units currently in the warehouse
    reorder_threshold INT NOT NULL DEFAULT 10,       -- Below this quantity, the product should be reordered
    last_restocked DATE NULL,                        -- NULL until the product has been restocked at least once
    CONSTRAINT FK_stock_products FOREIGN KEY (product_id)
        REFERENCES products(product_id) ON DELETE CASCADE,  -- Deleting a product cleans up its stock row too
    CONSTRAINT CK_stock_nonnegative CHECK (quantity_on_hand >= 0)  -- Stock can never go below zero, no matter what decremented it
);

Why not just add quantity_on_hand to products?

A single stock number per product works for this project, but a real warehouse often tracks quantity per storage location or per warehouse. Keeping `stock_levels` as its own table now means that if the business later needs a `location_id` column, it is a change to one focused table instead of a redesign of `products` itself.

Step 4: Insert Sample Data

Insert order matters here just like it does in any schema with foreign keys: `suppliers` first, then `products` (since every product needs a real `supplier_id`), and `stock_levels` last of all, since every stock row needs a real `product_id`.

INSERT INTO suppliers (supplier_name, contact_email, phone) VALUES
('Northwind Traders', 'orders@northwindtraders.example', '555-0101'),
('Global Parts Co.', 'sales@globalparts.example', '555-0102');

INSERT INTO products (supplier_id, sku, product_name, unit_price) VALUES
(1, 'SKU-CBL-01', 'USB-C Cable 1m', 6.99),
(1, 'SKU-CBL-02', 'USB-C Cable 2m', 9.99),
(2, 'SKU-ADP-01', 'HDMI to USB-C Adapter', 14.50);

-- One stock row per product, matching the UNIQUE constraint from Step 3
INSERT INTO stock_levels (product_id, quantity_on_hand, reorder_threshold, last_restocked) VALUES
(1, 250, 50, '2026-07-20'),
(2, 8, 25, '2026-06-15'),    -- Below its reorder_threshold of 25: needs reordering
(3, 40, 10, '2026-08-01');

Confirmation

Click Run to see what this code prints.

Step 5: Join Across All Three Tables

This is the query that shows why splitting the data into three tables pays off: it starts from `products`, joins to `suppliers` to resolve `supplier_id` into an actual company name, and joins to `stock_levels` to attach the current quantity — turning three tables of raw IDs into one readable inventory report.

SELECT
    p.sku,
    p.product_name,
    s.supplier_name,
    sl.quantity_on_hand,
    sl.reorder_threshold
FROM products p
JOIN suppliers s ON p.supplier_id = s.supplier_id     -- Resolve supplier_id into the supplier's actual name
JOIN stock_levels sl ON p.product_id = sl.product_id  -- Attach the current stock quantity
ORDER BY p.sku;

sku	product_name	supplier_name	quantity_on_hand	reorder_threshold
SKU-ADP-01	HDMI to USB-C Adapter	Global Parts Co.	40	10
SKU-CBL-01	USB-C Cable 1m	Northwind Traders	250	50
SKU-CBL-02	USB-C Cable 2m	Northwind Traders	8	25

Complete Schema

The full schema assembled from the three steps above, ready to run top to bottom in SQL Server Management Studio or Azure Data Studio.

CREATE TABLE suppliers (
    supplier_id INT IDENTITY(1,1) PRIMARY KEY,
    supplier_name NVARCHAR(100) NOT NULL,
    contact_email NVARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) NULL,
    created_at DATETIME2 NOT NULL DEFAULT GETDATE()
);

CREATE TABLE products (
    product_id INT IDENTITY(1,1) PRIMARY KEY,
    supplier_id INT NOT NULL,
    sku VARCHAR(20) NOT NULL UNIQUE,
    product_name NVARCHAR(150) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    CONSTRAINT FK_products_suppliers FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id) ON DELETE NO ACTION,
    CONSTRAINT CK_products_price CHECK (unit_price >= 0)
);

CREATE TABLE stock_levels (
    stock_id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT NOT NULL UNIQUE,
    quantity_on_hand INT NOT NULL DEFAULT 0,
    reorder_threshold INT NOT NULL DEFAULT 10,
    last_restocked DATE NULL,
    CONSTRAINT FK_stock_products FOREIGN KEY (product_id)
        REFERENCES products(product_id) ON DELETE CASCADE,
    CONSTRAINT CK_stock_nonnegative CHECK (quantity_on_hand >= 0)
);

Sample Queries

Two more queries a warehouse manager would actually run against this schema: a low-stock report using `TOP N`, and a per-supplier product count.

-- Products at or below their reorder threshold, most urgent first
SELECT TOP 5
    p.sku,
    p.product_name,
    sl.quantity_on_hand,
    sl.reorder_threshold
FROM stock_levels sl
JOIN products p ON sl.product_id = p.product_id
WHERE sl.quantity_on_hand <= sl.reorder_threshold
ORDER BY sl.quantity_on_hand ASC;

sku	product_name	quantity_on_hand	reorder_threshold
SKU-CBL-02	USB-C Cable 2m	8	25

-- How many products each supplier provides, including suppliers with zero products
SELECT s.supplier_name, COUNT(p.product_id) AS total_products
FROM suppliers s
LEFT JOIN products p ON s.supplier_id = p.supplier_id  -- LEFT JOIN keeps suppliers even with no products yet
GROUP BY s.supplier_name
ORDER BY total_products DESC;

supplier_name	total_products
Northwind Traders	2
Global Parts Co.	1

Extend This Project

Add a `warehouses` table and a `warehouse_id` column on `stock_levels` to track stock per location.
Add a `purchase_orders` table so a low-stock alert can be turned into an actual reorder record.
Add a `categories` table and a `category_id` foreign key on `products` to support catalog browsing.
Create a `VIEW` called `low_stock_products` that packages the reorder report as a queryable view.
Add a trigger that updates `stock_levels.last_restocked` automatically whenever quantity_on_hand increases.

Summary

You designed a three-table T-SQL schema that separates suppliers, products, and stock into focused tables connected by real foreign keys, with `CHECK` constraints making invalid states (negative prices, negative stock) structurally impossible rather than trusting application code to prevent them. The join queries you wrote — resolving IDs into a readable report and filtering for low stock with `TOP N` — are the same query shapes you will reuse in almost any inventory or catalog schema, not just this one.

Overview

Prerequisites

Project Structure

Step 1: Create the Suppliers Table

Step 2: Create the Products Table

Step 3: Create the Stock Levels Table

Step 4: Insert Sample Data

Step 5: Join Across All Three Tables

Complete Schema

Sample Queries

Extend This Project

Summary

More SQL Server Practice Builds

Bank Transfer System

Sales Analytics Report

Role-Based Access Demo

Query Performance Audit