MySQL Inventory Database Project: Stock Movements

Project goal: track how many units of each product are in each warehouse, with a full history of every movement. You will combine two patterns from earlier lessons: an append-only ledger (what happened) and a summary table (what we have now) that are updated together in one transaction (Price History, Denormalization).

Requirements

Products have a SKU, a name and a reorder level (the minimum total stock before we must order more).
Warehouses hold stock. Each (warehouse, product) pair has a current quantity that can never be negative.
Every change is a movement: receipts, shipments, transfers between warehouses and manual adjustments. Movements are never changed or deleted.
A transfer takes from one warehouse and adds to another, as one atomic action.
Reports: stock by warehouse, products below their reorder level, the history of one product, and an audit that proves the summary equals the ledger.

Tables and relationships

Table	Purpose
`products`, `warehouses`	Reference data
`stock_levels`	The current quantity per (warehouse, product): fast to read
`stock_movements`	The ledger of every change: the source of truth

The schema

query.sql

SQL

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(20) NOT NULL,
    name VARCHAR(60) NOT NULL,
    reorder_level INT UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE KEY uq_products_sku (sku)
);

CREATE TABLE warehouses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL,
    UNIQUE KEY uq_warehouses_name (name)
);

CREATE TABLE stock_levels (
    warehouse_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (warehouse_id, product_id),
    KEY idx_stock_product (product_id),
    CONSTRAINT chk_stock_not_negative CHECK (quantity >= 0),
    CONSTRAINT fk_stock_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_stock_product FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE stock_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    warehouse_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity_change INT NOT NULL,
    reason ENUM('receipt', 'shipment', 'transfer_in', 'transfer_out', 'adjustment') NOT NULL,
    moved_at DATETIME NOT NULL,
    note VARCHAR(80) NULL,
    KEY idx_movements_product (product_id, moved_at),
    KEY idx_movements_warehouse (warehouse_id, product_id),
    CONSTRAINT chk_movement_nonzero CHECK (quantity_change <> 0),
    CONSTRAINT fk_movements_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_movements_product FOREIGN KEY (product_id) REFERENCES products (id)
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

quantity_change is positive for stock coming in and negative for stock going out. The CHECK (quantity >= 0) on stock_levels makes it impossible to ship what is not there.

Two procedures

move_stock writes a ledger row and updates the summary. It does not start a transaction: the caller does, so that several movements can belong to one transaction (a transfer). transfer_stock uses it twice (Stored Procedures):

query.sql

SQL

DELIMITER //

CREATE PROCEDURE move_stock(IN p_wh INT UNSIGNED, IN p_product INT UNSIGNED, IN p_change INT, IN p_reason VARCHAR(20), IN p_when DATETIME, IN p_note VARCHAR(80))
BEGIN
    INSERT INTO stock_movements (warehouse_id, product_id, quantity_change, reason, moved_at, note)
    VALUES (p_wh, p_product, p_change, p_reason, p_when, p_note);

    UPDATE stock_levels SET quantity = quantity + p_change WHERE warehouse_id = p_wh AND product_id = p_product;
    IF ROW_COUNT() = 0 THEN
        INSERT INTO stock_levels (warehouse_id, product_id, quantity) VALUES (p_wh, p_product, p_change);
    END IF;
END//

CREATE PROCEDURE transfer_stock(IN p_from INT UNSIGNED, IN p_to INT UNSIGNED, IN p_product INT UNSIGNED, IN p_qty INT UNSIGNED, IN p_when DATETIME)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    CALL move_stock(p_from, p_product, -p_qty, 'transfer_out', p_when, CONCAT('to warehouse ', p_to));
    CALL move_stock(p_to, p_product, p_qty, 'transfer_in', p_when, CONCAT('from warehouse ', p_from));
    COMMIT;
END//

DELIMITER ;

Output

OUTPUT

1
2
3

Query OK, 0 rows affected

Query OK, 0 rows affected

The procedure first tries to update the stock row. If there is none (ROW_COUNT() = 0), this is the first time the product arrives in that warehouse, and it inserts the row. (Two first deliveries of the same product at the same instant could both try to insert: one gets a duplicate-key error, and the caller retries. A busy system can lock the warehouse row first.) The transfer takes out first: if the source does not have enough stock, the CHECK fails at once, and nothing is added anywhere.

Sample data

query.sql

SQL

INSERT INTO products (sku, name, reorder_level) VALUES ('NB-A5', 'Notebook A5', 120), ('PN-BLU', 'Blue Pen', 300), ('BG-001', 'Backpack', 40);
INSERT INTO warehouses (name) VALUES ('Main'), ('East');

START TRANSACTION;
CALL move_stock(1, 1, 150, 'receipt', '2026-03-01 08:00:00', 'supplier delivery');
CALL move_stock(1, 2, 500, 'receipt', '2026-03-01 08:05:00', 'supplier delivery');
CALL move_stock(1, 3, 30, 'receipt', '2026-03-01 08:10:00', 'supplier delivery');
COMMIT;

START TRANSACTION;
CALL move_stock(1, 1, -40, 'shipment', '2026-03-03 10:00:00', 'order 1001');
COMMIT;

CALL transfer_stock(1, 2, 2, 200, '2026-03-04 09:00:00');
CALL transfer_stock(1, 2, 1, 50, '2026-03-04 09:10:00');

Output

OUTPUT

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 1 row affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Why not a plain upsert?

The shortest code would be INSERT ... ON DUPLICATE KEY UPDATE quantity = quantity + change. It fails for negative changes, even when the row exists, because MySQL checks the CHECK constraint on the row it would insert before it notices the duplicate key:

query.sql

SQL

INSERT INTO stock_levels (warehouse_id, product_id, quantity) VALUES (1, 1, -5) ON DUPLICATE KEY UPDATE quantity = quantity - 5;

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_stock_not_negative' is violated.

That is why move_stock tries the UPDATE first. (This is real behaviour, found while testing this project on MySQL 8.4.)

Safety: nothing goes negative

The East warehouse has 0 backpacks. Try to ship one, and to transfer more than exists:

query.sql

SQL

1
2
3

START TRANSACTION;
CALL move_stock(2, 3, -1, 'shipment', '2026-03-05 10:00:00', 'order 1002');
ROLLBACK;

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_stock_not_negative' is violated.

query.sql

SQL

CALL transfer_stock(1, 2, 3, 100, '2026-03-05 11:00:00');

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_stock_not_negative' is violated.

The first error comes from inserting a negative quantity as the first row for a new (warehouse, product): that violates the check. The second is a transfer of 100 backpacks when Main holds 30: the transfer rolled back completely. Nothing changed:

query.sql

SQL

1
2
3

SELECT w.name AS warehouse, p.name AS product, s.quantity
FROM stock_levels s JOIN warehouses w ON w.id = s.warehouse_id JOIN products p ON p.id = s.product_id
ORDER BY w.name, p.name;

Output

OUTPUT

+-----------+-------------+----------+
| warehouse | product     | quantity |
+-----------+-------------+----------+
| East      | Blue Pen    |      200 |
| East      | Notebook A5 |       50 |
| Main      | Backpack    |       30 |
| Main      | Blue Pen    |      300 |
| Main      | Notebook A5 |       60 |
+-----------+-------------+----------+
5 rows in set

Query 1: stock of each product, in total

query.sql

SQL

SELECT p.sku, p.name, COALESCE(SUM(s.quantity), 0) AS total_stock, p.reorder_level
FROM products p LEFT JOIN stock_levels s ON s.product_id = p.id
GROUP BY p.id, p.sku, p.name, p.reorder_level
ORDER BY p.sku;

Output

OUTPUT

+--------+-------------+-------------+---------------+
| sku    | name        | total_stock | reorder_level |
+--------+-------------+-------------+---------------+
| BG-001 | Backpack    |          30 |            40 |
| NB-A5  | Notebook A5 |         110 |           120 |
| PN-BLU | Blue Pen    |         500 |           300 |
+--------+-------------+-------------+---------------+
3 rows in set

Query 2: the reorder report

Products whose total stock is below their reorder level:

query.sql

SQL

SELECT p.sku, p.name, SUM(s.quantity) AS total_stock, p.reorder_level, p.reorder_level - SUM(s.quantity) AS missing
FROM products p JOIN stock_levels s ON s.product_id = p.id
GROUP BY p.id, p.sku, p.name, p.reorder_level
HAVING SUM(s.quantity) < p.reorder_level
ORDER BY missing DESC;

Output

OUTPUT

+--------+-------------+-------------+---------------+---------+
| sku    | name        | total_stock | reorder_level | missing |
+--------+-------------+-------------+---------------+---------+
| NB-A5  | Notebook A5 |         110 |           120 |      10 |
| BG-001 | Backpack    |          30 |            40 |      10 |
+--------+-------------+-------------+---------------+---------+
2 rows in set

HAVING filters after the sums are computed (HAVING).

Query 3: the history of a product

query.sql

SQL

SELECT m.moved_at, w.name AS warehouse, m.reason, m.quantity_change, m.note,
       SUM(m.quantity_change) OVER (PARTITION BY m.warehouse_id ORDER BY m.id) AS warehouse_balance
FROM stock_movements m JOIN warehouses w ON w.id = m.warehouse_id
WHERE m.product_id = 1
ORDER BY m.id;

Output

OUTPUT

+---------------------+-----------+--------------+-----------------+-------------------+-------------------+
| moved_at            | warehouse | reason       | quantity_change | note              | warehouse_balance |
+---------------------+-----------+--------------+-----------------+-------------------+-------------------+
| 2026-03-01 08:00:00 | Main      | receipt      |             150 | supplier delivery |               150 |
| 2026-03-03 10:00:00 | Main      | shipment     |             -40 | order 1001        |               110 |
| 2026-03-04 09:10:00 | Main      | transfer_out |             -50 | to warehouse 2    |                60 |
| 2026-03-04 09:10:00 | East      | transfer_in  |              50 | from warehouse 1  |                50 |
+---------------------+-----------+--------------+-----------------+-------------------+-------------------+
4 rows in set

A running total per warehouse, from the ledger alone (Window Aggregates).

Query 4: the audit

The summary table must agree with the ledger. This query finds every difference, and returns nothing when the books are right:

query.sql

SQL

SELECT COALESCE(s.warehouse_id, m.warehouse_id) AS warehouse_id, COALESCE(s.product_id, m.product_id) AS product_id, s.quantity AS summary_qty, m.ledger_qty
FROM stock_levels s
RIGHT JOIN (SELECT warehouse_id, product_id, SUM(quantity_change) AS ledger_qty FROM stock_movements GROUP BY warehouse_id, product_id) m
       ON m.warehouse_id = s.warehouse_id AND m.product_id = s.product_id
WHERE s.quantity IS NULL OR s.quantity <> m.ledger_qty;

Output

OUTPUT

Empty set

Run it on a schedule. A non-empty result means a bug (code that changed stock_levels directly, bypassing the procedure) or a manual fix that skipped the ledger. Show what happens when the summary is changed by hand:

query.sql

SQL

UPDATE stock_levels SET quantity = quantity + 5 WHERE warehouse_id = 2 AND product_id = 1;

Output

OUTPUT

1
2

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Run the audit again:

query.sql

SQL

SELECT s.warehouse_id, s.product_id, s.quantity AS summary_qty, m.ledger_qty
FROM stock_levels s
JOIN (SELECT warehouse_id, product_id, SUM(quantity_change) AS ledger_qty FROM stock_movements GROUP BY warehouse_id, product_id) m
  ON m.warehouse_id = s.warehouse_id AND m.product_id = s.product_id
WHERE s.quantity <> m.ledger_qty;

Output

OUTPUT

+--------------+------------+-------------+------------+
| warehouse_id | product_id | summary_qty | ledger_qty |
+--------------+------------+-------------+------------+
|            2 |          1 |          55 |         50 |
+--------------+------------+-------------+------------+
1 row in set

The audit finds the five phantom units. The fix is not to edit the number back, but to write a proper adjustment movement with a note, so that the history explains it. To prevent hand edits, give the application account EXECUTE on the procedures and no UPDATE on stock_levels (Privileges).

Common mistakes

Only a quantity column, no history. When the number is wrong, nobody can say why.
Updating the summary and the ledger in separate transactions. A crash between them leaves them different. One transaction for both.
SELECT quantity then UPDATE quantity = :new. Two sessions overwrite each other. Use quantity = quantity + :change.
Allowing negative stock by accident. A CHECK is cheap insurance.
Deleting a product that has movements. Mark it inactive instead (fk_movements_product refuses the delete).
Counting stock with SUM over the ledger on every page view for a big table. That is why stock_levels exists.

Final result

query.sql

SQL

SELECT (SELECT COUNT(*) FROM stock_movements) AS movements, (SELECT COUNT(*) FROM stock_levels) AS stock_rows, (SELECT SUM(quantity) FROM stock_levels) AS units_on_hand;

Output

OUTPUT

+-----------+------------+---------------+
| movements | stock_rows | units_on_hand |
+-----------+------------+---------------+
|         8 |          5 |           645 |
+-----------+------------+---------------+
1 row in set

Next steps: batch numbers and expiry dates, a reservations table for stock held by open orders, supplier and purchase-order tables, and monthly snapshots of stock_levels for reporting (Summary Tables).