Inventory Management ER Diagram

Inventory systems need to answer one question — how much of each product sits in each warehouse — so stock levels are modeled as their own table keyed by warehouse and product. Purchase orders then record how stock arrives from suppliers.

stock_levels exists because the central question — how much of product X sits in warehouse Y — has a two-part answer. The composite primary key (warehouse_id, product_id) allows exactly one row per pair, and quantity defaults to zero, so an unstocked product either has no row or an explicit zero. Modeling stock as its own table rather than a column on products is what makes multi-warehouse operation possible at all: a stock column on products can only answer the question globally, not per location.

purchase_order_items.unit_cost and products.unit_price are deliberately different numbers. The first is what you pay the supplier, snapshotted per purchase order line because supplier prices move; the second is what you charge, at catalog level. purchase_orders carries both a supplier_id and a destination warehouse_id, so one header describes who ships and where, and the items below it list products, quantities and costs. The status default of 'draft' reflects that purchase orders are edited before they are committed.

products.supplier_id uses ON DELETE SET NULL: removing a supplier leaves its products in place, just unsourced, which matches how catalogs behave when a vendor relationship ends. The contrast with stock_levels — cascading from both warehouses and products — is the general rule: derived state dies with its owner, reference data survives. Purchase orders reference suppliers and warehouses with no delete rule at all, so procurement history blocks the deletion of anyone who ever shipped anything.

The gap is that stock_levels.quantity is a bare number with no audit trail. A stock_movements ledger (product, warehouse, delta, reason, reference to the purchase order or sale) makes quantity derivable and discrepancies traceable, and once it exists the column becomes a cache of the ledger's running sum. Reorder points per warehouse-product pair turn that ledger into automatically drafted purchase orders, and batch or expiry tracking is a batches table keyed under stock levels — essential for food and pharmaceuticals, unnecessary for screws.

The diagram

1:N 1:1 1:1 1:N 1:N 1:N 1:N warehouses PKid integer name varchar(100) location varchar(150) suppliers PKid integer name varchar(150) Uemail varchar(255) phone varchar(30) products PKid integer FKsupplier_id integer Usku varchar(40) name varchar(150) unit_price decimal(10,2) stock_levels PKwarehouse_id integer PKproduct_id integer quantity integer purchase_orders PKid integer FKsupplier_id integer FKwarehouse_id integer status varchar(20) ordered_at timestamp purchase_order_items PKid integer FKpurchase_order_id integer FKproduct_id integer quantity integer unit_cost decimal(10,2)

Tables (6)

warehouses

  • idintegerPK
  • namevarchar(100)
  • locationvarchar(150)

suppliers

  • idintegerPK
  • namevarchar(150)
  • emailvarchar(255)U
  • phonevarchar(30)

products

  • idintegerPK
  • supplier_idintegerFK
  • skuvarchar(40)U
  • namevarchar(150)
  • unit_pricedecimal(10,2)

stock_levels

  • warehouse_idintegerPK
  • product_idintegerPK
  • quantityinteger

purchase_orders

  • idintegerPK
  • supplier_idintegerFK
  • warehouse_idintegerFK
  • statusvarchar(20)
  • ordered_attimestamp

purchase_order_items

  • idintegerPK
  • purchase_order_idintegerFK
  • product_idintegerFK
  • quantityinteger
  • unit_costdecimal(10,2)

Relationships (7)

  • 1:Nsuppliers.id→products.supplier_idON DELETE SET NULL
  • 1:1warehouses.id→stock_levels.warehouse_idON DELETE CASCADE
  • 1:1products.id→stock_levels.product_idON DELETE CASCADE
  • 1:Nsuppliers.id→purchase_orders.supplier_id
  • 1:Nwarehouses.id→purchase_orders.warehouse_id
  • 1:Npurchase_orders.id→purchase_order_items.purchase_order_idON DELETE CASCADE
  • 1:Nproducts.id→purchase_order_items.product_id

The SQL

Copy this, paste it in the SQL tab, or just open it below.
SQL
CREATE TABLE warehouses (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  location VARCHAR(150)
);

CREATE TABLE suppliers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(255) UNIQUE,
  phone VARCHAR(30)
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  supplier_id INTEGER REFERENCES suppliers(id) ON DELETE SET NULL,
  sku VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL
);

CREATE TABLE stock_levels (
  warehouse_id INTEGER NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  quantity INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (warehouse_id, product_id)
);

CREATE TABLE purchase_orders (
  id SERIAL PRIMARY KEY,
  supplier_id INTEGER NOT NULL REFERENCES suppliers(id),
  warehouse_id INTEGER NOT NULL REFERENCES warehouses(id),
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  ordered_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE purchase_order_items (
  id SERIAL PRIMARY KEY,
  purchase_order_id INTEGER NOT NULL REFERENCES purchase_orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_cost DECIMAL(10,2) NOT NULL
);

Keep learning