Inventory Management Database Schema

Reviewed by the Free ER Diagram maintainers. Updated September 2, 2026. Read our editorial policy.

Worked example · Updated September 2, 2026

An inventory system has to answer two different questions: how much stock is available now, and why that quantity changed. A single quantity column on the products table answers the first question poorly and cannot answer the second. This model separates the product catalog, physical storage locations, current balances, and an immutable movement ledger.

Design principle: treat every receipt, sale, transfer, return, and adjustment as a stock movement. Keep a balance table for fast reads, but preserve the movement ledger as the source for reconciliation.

Entities and Responsibilities

SQL DDL

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  sku VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  reorder_point DECIMAL(14,3) NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE warehouses (
  id BIGINT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  timezone VARCHAR(60) NOT NULL
);

CREATE TABLE stock_balances (
  warehouse_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity_on_hand DECIMAL(14,3) NOT NULL DEFAULT 0,
  quantity_reserved DECIMAL(14,3) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NOT NULL,
  PRIMARY KEY (warehouse_id, product_id),
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE stock_movements (
  id BIGINT PRIMARY KEY,
  warehouse_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  movement_type VARCHAR(30) NOT NULL,
  quantity_delta DECIMAL(14,3) NOT NULL,
  reference_type VARCHAR(30),
  reference_id BIGINT,
  occurred_at TIMESTAMP NOT NULL,
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE suppliers (
  id BIGINT PRIMARY KEY,
  supplier_code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  email VARCHAR(255)
);

CREATE TABLE purchase_orders (
  id BIGINT PRIMARY KEY,
  supplier_id BIGINT NOT NULL,
  warehouse_id BIGINT NOT NULL,
  order_number VARCHAR(50) NOT NULL UNIQUE,
  status VARCHAR(30) NOT NULL,
  ordered_at TIMESTAMP,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
);

CREATE TABLE purchase_order_items (
  id BIGINT PRIMARY KEY,
  purchase_order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity_ordered DECIMAL(14,3) NOT NULL,
  quantity_received DECIMAL(14,3) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(14,4) NOT NULL,
  UNIQUE (purchase_order_id, product_id),
  FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

How the Relationships Work

Product and warehouse is many-to-many

A product may be held in several warehouses, and every warehouse holds many products. The stock_balances table resolves that relationship. Its composite primary key prevents duplicate balances for the same product and location. Available quantity is normally calculated as quantity_on_hand minus quantity_reserved; storing it separately would create another value that can drift.

The movement ledger is intentionally one-to-many

Each balance can have thousands of movements over time. A positive delta represents stock entering the location and a negative delta represents stock leaving. Transfers should create two movements in one transaction: a negative movement at the source and a positive movement at the destination. The reference fields connect a movement to a receipt, shipment, return, or adjustment without forcing every operational document into one oversized table.

Purchase order values are snapshots

Unit cost belongs on purchase_order_items because supplier prices change. Reading a current product cost later would rewrite history and invalidate old receiving and valuation reports. Quantity received is useful for fast workflow checks, while detailed receipt rows can be added when partial deliveries, lot numbers, or inspection states matter.

Indexes and Integrity Checks

Add an index on stock_movements(warehouse_id, product_id, occurred_at) for inventory history queries. Purchase order screens usually need an index on supplier_id and status. In production, restrict movement_type and purchase order status with database checks or controlled lookup tables. Decide whether negative stock is allowed; if it is not, update the balance and insert the movement inside one transaction while locking the balance row.

Do not delete products that have movements. Mark them inactive so historical references remain valid. The same rule applies to warehouses and suppliers. For audited environments, include actor_id, reason_code, and an idempotency key on stock movements to prevent duplicate entries from retried jobs.

Review This Model in the Diagram Tool

Paste the DDL into Free ER Diagram. Confirm that products and warehouses each connect to both balances and movements, purchase_orders connects to suppliers and warehouses, and purchase_order_items connects the order to products. Group the tables into Catalog, Inventory, and Purchasing domains to make the operational boundaries visible.