Inventory Management Database Design: A Practical Guide

Most inventory bugs are not caused by bad code in the order screen. They come from a schema that stores a single quantity column on the product row and lets every feature overwrite it. Good inventory management database design fixes that at the root: every stock change is recorded as a row in a movement ledger, the current balance is derived from (and checked against) that ledger, and concurrent updates are made safe with transactions and row locks. This guide walks through the inventory tables you need, stock movement tracking in PostgreSQL, a Laravel example, and the queries that prove your numbers are right.

Problem: Stock Numbers You Cannot Trust

A typical first version of an inventory feature looks like this: a products table with a stock column, and code that runs UPDATE products SET stock = 47 after reading the old value, subtracting the order quantity and writing the result back. It works in development with one user. In production, with a web store, a POS, a warehouse app and an ERP sync all touching the same rows, the numbers drift. Nobody can explain why a product shows 12 units when the shelf has 9, because the database only knows the latest value, not how it got there.

Symptoms

• Stock goes negative, or two customers buy the last unit at the same time.
• Physical counts never match the system, and the difference cannot be traced to any document.
• There is no way to answer "how many units did we have on the 1st of last month?"
• Returns, transfers and adjustments are handled by editing the quantity directly, sometimes from an admin panel.
• A retried API request or double-clicked button deducts stock twice.
• Reports are slow because they rebuild stock from orders, purchase receipts and returns stored in different tables.
• Adding a second warehouse means adding columns like stock_karachi and stock_lahore.

Root Cause: State Without History

The core mistake is storing only the current state. When a single number is overwritten in place:

• Lost updates happen. Two transactions read 10, both subtract, and both write their own result. One deduction disappears. This is a classic read-modify-write race.
• There is no audit trail. Without a record per change, you cannot reconcile, roll back a mistake cleanly, or report stock at a past date.
• The model ignores locations. Stock belongs to a product in a place, so the natural key is (product, warehouse), not product alone.
• The database does not enforce the rules. Without constraints, nothing stops a negative balance or a movement with zero quantity.

Accounting solved this long ago with double-entry ledgers: you never edit a balance, you post entries. Inventory works the same way. If you are still designing the rest of the schema, the general principles are covered in how to design database tables before writing your backend.

Step-by-Step Solution: Inventory Management Database Design

Step 1: Separate master data from stock

Keep products (what you sell or store) and warehouses (where it is) as plain master tables. Do not put a quantity column on products. If you need bins or shelves, add a locations table under warehouses and use location instead of warehouse in the tables below.

Step 2: Make stock movements an append-only ledger

Create a stock_movements table where each row is one change: product, warehouse, signed quantity (positive for stock in, negative for stock out), movement type (receipt, sale, return, transfer, adjustment), a reference to the source document, and a timestamp. Rows are never updated or deleted. Mistakes are fixed with a new correcting movement, so history stays intact.

Step 3: Keep a balance table for fast reads

Summing the whole ledger on every product page gets slower as it grows. Add stock_balances with one row per (product, warehouse) holding on_hand and reserved. Update it in the same transaction as the ledger insert, so the two can never disagree. Add CHECK constraints so the database itself rejects negative stock.

Step 4: Write movements inside a transaction with locking

Either use a single conditional UPDATE ... WHERE on_hand - reserved >= qty, which PostgreSQL applies atomically, or lock the balance row with SELECT ... FOR UPDATE before checking and writing. Both prevent the lost-update race. When a transfer touches two rows, lock them in a consistent order (for example by warehouse id) to avoid deadlocks.

Step 5: Make writes idempotent

Give each movement an idempotency_key with a unique constraint, built from the source document (for example order:1042:line:3). A retried request then hits the constraint instead of deducting stock twice.

Step 6: Reserve, then deduct

For orders that are placed now and shipped later, increase reserved when the order is confirmed and only post the negative movement when goods actually leave. Available stock is on_hand - reserved. Cancelling the order releases the reservation without touching the ledger.

Code/Commands Examples

PostgreSQL schema for the inventory tables

CREATE TABLE products (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku        text NOT NULL UNIQUE,
  name       text NOT NULL,
  uom        text NOT NULL DEFAULT 'pcs',      -- unit of measure
  is_active  boolean NOT NULL DEFAULT true
);

CREATE TABLE warehouses (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code  text NOT NULL UNIQUE,
  name  text NOT NULL
);

CREATE TABLE stock_movements (
  id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id       bigint NOT NULL REFERENCES products(id),
  warehouse_id     bigint NOT NULL REFERENCES warehouses(id),
  qty_change       numeric(14,3) NOT NULL CHECK (qty_change <> 0),
  movement_type    text NOT NULL CHECK (movement_type IN
                     ('receipt','sale','return','transfer_in','transfer_out','adjustment')),
  reference_type   text,                       -- e.g. 'order', 'purchase_order'
  reference_id     bigint,
  idempotency_key  text UNIQUE,
  unit_cost        numeric(14,4),
  note             text,
  created_by       bigint,
  created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX stock_movements_item_time_idx
  ON stock_movements (product_id, warehouse_id, created_at);

CREATE TABLE stock_balances (
  product_id    bigint NOT NULL REFERENCES products(id),
  warehouse_id  bigint NOT NULL REFERENCES warehouses(id),
  on_hand       numeric(14,3) NOT NULL DEFAULT 0 CHECK (on_hand >= 0),
  reserved      numeric(14,3) NOT NULL DEFAULT 0 CHECK (reserved >= 0),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (product_id, warehouse_id),
  CHECK (reserved <= on_hand)
);

Use numeric rather than float for quantities and costs so values such as 0.1 kg add up exactly. If you only ever sell whole units, integer is fine.

Block updates and deletes on the ledger

CREATE FUNCTION forbid_ledger_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'stock_movements is append-only; post a correcting movement instead';
END;
$$;

CREATE TRIGGER stock_movements_append_only
BEFORE UPDATE OR DELETE ON stock_movements
FOR EACH ROW EXECUTE FUNCTION forbid_ledger_change();

Post a sale atomically (conditional update + ledger insert)

This statement only inserts the ledger row if the balance update succeeded, so stock can never be oversold. If it returns no rows, there was not enough available stock.

BEGIN;

WITH upd AS (
  UPDATE stock_balances
     SET on_hand = on_hand - 3,
         updated_at = now()
   WHERE product_id = 101
     AND warehouse_id = 1
     AND on_hand - reserved >= 3
  RETURNING product_id, warehouse_id
)
INSERT INTO stock_movements
  (product_id, warehouse_id, qty_change, movement_type,
   reference_type, reference_id, idempotency_key)
SELECT product_id, warehouse_id, -3, 'sale', 'order', 1042, 'order:1042:line:1'
  FROM upd
RETURNING id;

COMMIT;

For receipts, create the balance row first if it does not exist: INSERT INTO stock_balances (product_id, warehouse_id) VALUES (101, 1) ON CONFLICT DO NOTHING; then add stock the same way with a positive quantity.

Transfer between warehouses without deadlocks

BEGIN;

-- lock both balance rows in a fixed order
SELECT product_id, warehouse_id, on_hand, reserved
  FROM stock_balances
 WHERE product_id = 101 AND warehouse_id IN (1, 2)
 ORDER BY warehouse_id
   FOR UPDATE;

-- application checks on_hand - reserved >= 5 for warehouse 1, then:
UPDATE stock_balances SET on_hand = on_hand - 5, updated_at = now()
 WHERE product_id = 101 AND warehouse_id = 1;
UPDATE stock_balances SET on_hand = on_hand + 5, updated_at = now()
 WHERE product_id = 101 AND warehouse_id = 2;

INSERT INTO stock_movements
  (product_id, warehouse_id, qty_change, movement_type, reference_type, reference_id)
VALUES (101, 1, -5, 'transfer_out', 'transfer', 77),
       (101, 2,  5, 'transfer_in',  'transfer', 77);

COMMIT;

If transactions sit open while waiting on application code, row locks are held longer than needed and other requests queue up. Keep these transactions short; PostgreSQL idle in transaction explains how to spot sessions that hold locks.

Laravel: locked, idempotent stock deduction

use Illuminate\Support\Facades\DB;

public function deduct(int $productId, int $warehouseId, string $qty, int $orderId, string $key): void
{
    DB::transaction(function () use ($productId, $warehouseId, $qty, $orderId, $key) {
        $balance = DB::table('stock_balances')
            ->where('product_id', $productId)
            ->where('warehouse_id', $warehouseId)
            ->lockForUpdate()
            ->first();

        if (! $balance || bccomp(bcsub($balance->on_hand, $balance->reserved, 3), $qty, 3) < 0) {
            throw new \RuntimeException('Insufficient stock');
        }

        // ON CONFLICT DO NOTHING on PostgreSQL: a retry with the same key inserts 0 rows
        $inserted = DB::table('stock_movements')->insertOrIgnore([
            'product_id'      => $productId,
            'warehouse_id'    => $warehouseId,
            'qty_change'      => bcmul($qty, '-1', 3),
            'movement_type'   => 'sale',
            'reference_type'  => 'order',
            'reference_id'    => $orderId,
            'idempotency_key' => $key,
        ]);

        if ($inserted === 0) {
            return; // already applied, do not deduct twice
        }

        DB::table('stock_balances')
            ->where('product_id', $productId)
            ->where('warehouse_id', $warehouseId)
            ->decrement('on_hand', $qty, ['updated_at' => now()]);
    });
}

The bc* functions need PHP's bcmath extension; they avoid floating-point errors when comparing decimal quantities.

Stock on hand at a past date

SELECT product_id, warehouse_id, SUM(qty_change) AS on_hand
  FROM stock_movements
 WHERE created_at < '2026-10-01 00:00:00+05'
 GROUP BY product_id, warehouse_id;

Verification: Prove the Numbers Match

The balance table is a cache of the ledger, so you can check it at any time. This query should return zero rows:

SELECT b.product_id, b.warehouse_id, b.on_hand,
       COALESCE(SUM(m.qty_change), 0) AS ledger_total
  FROM stock_balances b
  LEFT JOIN stock_movements m
         ON m.product_id = b.product_id AND m.warehouse_id = b.warehouse_id
 GROUP BY b.product_id, b.warehouse_id, b.on_hand
HAVING b.on_hand <> COALESCE(SUM(m.qty_change), 0);

• Run the reconciliation query after a load test and on a schedule in production; alert if it returns rows.
• Try UPDATE stock_movements SET qty_change = 1 WHERE id = 1; and confirm the trigger raises an error.
• Try to oversell: fire two concurrent requests for the last unit (for example with two psql sessions running the sale statement). One should succeed and the other should return no rows.
• Replay the same API request twice with the same idempotency key and confirm only one movement exists.
• Run EXPLAIN ANALYZE on the history query for a busy product and confirm it uses stock_movements_item_time_idx. If it does not, see how to optimize a slow PostgreSQL query.

Prevention

• Route every stock change through one service or stored procedure. No feature writes to stock_balances directly.
• Never expose a "set quantity" field in admin screens. Stock counts post an adjustment movement for the difference, with a note and user id.
• Keep the database constraints (CHECK, foreign keys, unique idempotency keys) even if the application also validates. They are the last line of defence.
• Use timestamptz for movement times and decide which time zone your reporting periods use.
• Store unit_cost on receipts if you need FIFO or weighted average valuation later; it is hard to reconstruct afterwards.
• Partition or archive old movements by date once the table is very large, and keep monthly snapshot balances for fast historical reports.
• Run the reconciliation query automatically and treat any mismatch as an incident.

FAQs

Why not just store a quantity column on the products table?

It loses history, breaks with multiple warehouses, and invites lost updates under concurrency. A ledger plus a balance table gives you fast reads and a full audit trail.

Do I need the stock_balances table if I have the ledger?

Not strictly. For small catalogs you can compute stock with SUM(qty_change). As movements grow, a balance table updated in the same transaction keeps reads fast and lets you enforce non-negative stock with a constraint.

How do I correct a wrong stock movement?

Post a new movement with the opposite quantity and a note that references the original. Do not edit or delete the old row.

Should I use triggers to update balances?

You can. A trigger on stock_movements that updates stock_balances guarantees they stay in sync even for writes outside your app. Explicit application code is easier to read and test. Pick one approach and use it everywhere.

How should batches, lots or serial numbers be modelled?

Add a lots table (lot number, expiry date) and include lot_id in both stock_movements and the balance key. Serial-tracked items usually get a table with one row per unit and its current location.

Does this design work with MySQL?

Yes. The same tables work with InnoDB, SELECT ... FOR UPDATE and unique keys. Syntax such as identity columns and the trigger function differs, and CHECK constraints are enforced only from MySQL 8.0.16.