Crowsfoot

Inventory and warehouse database schema: ER diagram and SQL

Inventory systems answer two questions: how much of each product is where, and how did it get that way. This schema keeps a current count and an append-only history of every change.

Open this schema in the editor Jump to the SQL
suppliers PK id bigint name varchar email varchar warehouses PK id bigint name varchar location varchar products PK id bigint FK supplier_id bigint sku varchar name varchar reorder_level int stock_levels PK warehouse_id bigint PK product_id bigint quantity int stock_movements PK id bigint FK product_id bigint FK warehouse_id bigint change int reason varchar moved_at timestamp

Tables in this schema

suppliers

Companies you buy products from.

warehouses

Places where stock is kept.

products

Items you stock, each with a SKU and a reorder threshold.

stock_levels

The current quantity of a product in a warehouse. A junction table with a count on it.

stock_movements

Every increase or decrease, with a reason. This is the audit trail.

How the tables relate

  • products.supplier_id references suppliers.id: one row in suppliers can have many rows in products.
  • stock_levels is a junction table between warehouses and products: many-to-many.
  • stock_movements.product_id references products.id: one row in products can have many rows in stock_movements.
  • stock_movements.warehouse_id references warehouses.id: one row in warehouses can have many rows in stock_movements.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE suppliers (
  id bigint PRIMARY KEY,
  name varchar(160) NOT NULL,
  email varchar(255)
);

CREATE TABLE warehouses (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  location varchar(160)
);

CREATE TABLE products (
  id bigint PRIMARY KEY,
  supplier_id bigint NOT NULL REFERENCES suppliers (id),
  sku varchar(40) NOT NULL,
  name varchar(160) NOT NULL,
  reorder_level int NOT NULL DEFAULT 0
);

CREATE TABLE stock_levels (
  warehouse_id bigint NOT NULL REFERENCES warehouses (id),
  product_id bigint NOT NULL REFERENCES products (id),
  quantity int NOT NULL DEFAULT 0,
  PRIMARY KEY (warehouse_id, product_id)
);

CREATE TABLE stock_movements (
  id bigint PRIMARY KEY,
  product_id bigint NOT NULL REFERENCES products (id),
  warehouse_id bigint NOT NULL REFERENCES warehouses (id),
  change int NOT NULL,
  reason varchar(40) NOT NULL,
  moved_at timestamptz NOT NULL
);

Design notes

  • stock_levels is the fast answer to “how many do we have?”, while stock_movements explains why.
  • Make sure both are updated in one transaction so they can’t drift apart.
  • sku should have a unique index, and reorder_level makes low-stock alerts a simple query.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.