Inventory management is one of those problems that looks solved until you add a second branch. Suddenly "how many units do we have?" becomes "how many units do we have at this location, and how many are in transit between locations?" The schema that worked for one branch breaks down immediately.

Here's the design I settled on after building the Novara Shop App's stock module.

The core principle: one product catalogue, many stock ledgers

Products are defined once. Stock quantities are tracked per branch. This sounds obvious but the implementation details matter significantly.

core schema
-- Products defined once, globally
CREATE TABLE products (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  sku         VARCHAR(64) UNIQUE NOT NULL,
  name        VARCHAR(255) NOT NULL,
  unit        VARCHAR(32) DEFAULT 'each',
  base_price  DECIMAL(12,2) NOT NULL
);

-- Stock per branch — this is the ledger, not a mutable quantity
CREATE TABLE stock_movements (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  product_id  INT NOT NULL,
  branch_id   INT NOT NULL,
  type        ENUM('receive','sale','transfer_out','transfer_in',
                   'adjustment','return') NOT NULL,
  qty         DECIMAL(12,4) NOT NULL, -- positive or negative
  ref_id      INT NULL,               -- sale_id, transfer_id, etc.
  created_by  INT NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (product_id, branch_id, created_at)
);

Notice there's no current_qty column anywhere. Current stock is always computed: SELECT SUM(qty) FROM stock_movements WHERE product_id=? AND branch_id=?. This means the stock count is always correct, always auditable, and you can reconstruct the full stock history at any point in time.

Performance: the running balance view

Summing all movements for every stock query would be slow once a busy branch accumulates thousands of movements per product. The solution is a periodic snapshot table.

stock snapshots
CREATE TABLE stock_snapshots (
  product_id   INT NOT NULL,
  branch_id    INT NOT NULL,
  qty          DECIMAL(12,4) NOT NULL,
  snapped_at   DATETIME NOT NULL,
  PRIMARY KEY (product_id, branch_id)
);

-- Current stock query (fast):
SELECT 
  s.qty + COALESCE(SUM(m.qty), 0) AS current_stock
FROM stock_snapshots s
LEFT JOIN stock_movements m
  ON m.product_id = s.product_id
  AND m.branch_id = s.branch_id
  AND m.created_at > s.snapped_at
WHERE s.product_id = ? AND s.branch_id = ?
GROUP BY s.qty;

A nightly scheduled job refreshes the snapshot table. The live query then only sums movements since the last snapshot — typically a few hundred rows, not tens of thousands.

Branch-level price overrides

The base product price is set globally. But a branch in a high-rent CBD location might charge slightly more, and a rural branch might run different promotions. I handle this with a branch_prices override table.

price resolution
CREATE TABLE branch_prices (
  product_id INT NOT NULL,
  branch_id  INT NOT NULL,
  price      DECIMAL(12,2) NOT NULL,
  valid_from DATETIME NOT NULL,
  valid_to   DATETIME NULL,
  PRIMARY KEY (product_id, branch_id, valid_from)
);

-- Resolve effective price for a branch:
SELECT COALESCE(
  (SELECT price FROM branch_prices
   WHERE product_id=? AND branch_id=?
     AND valid_from <= NOW()
     AND (valid_to IS NULL OR valid_to > NOW())
   ORDER BY valid_from DESC LIMIT 1),
  (SELECT base_price FROM products WHERE id=?)
) AS effective_price;

Stock transfers between branches

A transfer creates two movement records atomically: a transfer_out at the source branch (negative qty) and a transfer_in at the destination (positive qty), both referencing the same transfer_id. Both rows get written in a single transaction — either both succeed or neither do.

I also added a transfers header table with a status column (pending / confirmed / cancelled). Receiving branch staff confirm receipt, which freezes the transfer and marks it complete. Until confirmed, the receiving branch sees the stock as "in transit" but not available for sale.

The reorder level alert

Each product can have a per-branch minimum stock level. A daily job computes current stock against the minimum and inserts a row into an alerts table for anything below threshold. The admin dashboard surfaces these as red cards. No email configuration required — the system just makes it visible.

Model the ledger, not the balance. The balance is always derivable; the ledger is the ground truth.

This design has handled everything a retail client has thrown at it: stock counts, year-end audits, theft investigations, promotion pricing, and multi-branch consolidation reports. The schema is stable because the core principle — movements are immutable facts, balances are computed — doesn't change no matter how complex the business gets.