ErdDocs

Example schemas / Inventory Management System

Inventory Management System ER diagram

An inventory management system database tracks items across warehouses on two levels: stock is the current quantity per item per warehouse (a composite-key table), and stock_moves is the append-only history explaining how it got there. Purchase orders bring goods in through supplier deliveries.

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • items — The catalog; unit matters the moment anything is measured in kg or meters.
  • warehouses / stock — stock is a composite-key table: one row per (warehouse, item), the running total.
  • purchase_orders / po_items — The order header and its lines — the same header/line split as an e-commerce order.
  • stock_moves — Append-only movement log; nullable from/to marks goods entering or leaving the system, like a bank ledger.

Design decisions worth copying

  • stock and stock_moves coexist deliberately: the total answers "how many now?" in O(1), the log answers "why?" — and a nightly job can rebuild one from the other, which is your consistency check.
  • stock has no surrogate id: (warehouse_id, item_id) is the natural identity, and the composite PK doubles as the lookup index.
  • The nullable from/to pattern on stock_moves is the same trick as bank transfers — the system boundary appears as NULL, not as a fake "EXTERNAL" warehouse row.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

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

CREATE TABLE warehouses (
    id    bigserial   PRIMARY KEY,
    code  varchar(10) NOT NULL UNIQUE,
    city  varchar(80) NOT NULL
);

CREATE TABLE items (
    id        bigserial    PRIMARY KEY,
    sku       varchar(40)  NOT NULL UNIQUE,
    title     varchar(200) NOT NULL,
    unit      varchar(12)  NOT NULL DEFAULT 'pcs'
);

CREATE TABLE stock (
    warehouse_id  bigint NOT NULL REFERENCES warehouses(id),
    item_id       bigint NOT NULL REFERENCES items(id),
    on_hand       int    NOT NULL DEFAULT 0,
    PRIMARY KEY (warehouse_id, item_id)
);
COMMENT ON TABLE stock IS 'Current quantity; stock_moves is the history behind it';

CREATE TABLE purchase_orders (
    id           bigserial   PRIMARY KEY,
    supplier_id  bigint      NOT NULL REFERENCES suppliers(id),
    ordered_on   date        NOT NULL,
    status       varchar(20) NOT NULL DEFAULT 'open'
);
COMMENT ON COLUMN purchase_orders.status IS 'open -> received, or cancelled';

CREATE TABLE po_items (
    po_id    bigint        NOT NULL REFERENCES purchase_orders(id),
    item_id  bigint        NOT NULL REFERENCES items(id),
    qty      int           NOT NULL,
    price    numeric(10,2) NOT NULL,
    PRIMARY KEY (po_id, item_id)
);

CREATE TABLE stock_moves (
    id         bigserial   PRIMARY KEY,
    item_id    bigint      NOT NULL REFERENCES items(id),
    from_wh    bigint      REFERENCES warehouses(id),
    to_wh      bigint      REFERENCES warehouses(id),
    qty        int         NOT NULL CHECK (qty > 0),
    moved_at   timestamptz NOT NULL DEFAULT now(),
    CHECK (from_wh IS NOT NULL OR to_wh IS NOT NULL)
);
COMMENT ON COLUMN stock_moves.from_wh IS 'NULL for inbound (delivery)';
COMMENT ON COLUMN stock_moves.to_wh IS 'NULL for outbound (shipment, write-off)';

FAQ

Where do sales orders fit?

Mirror the purchase side: sales_orders and so_items, with shipments creating stock_moves rows whose to_wh is NULL. Purchasing and selling are symmetric around the stock tables.

How would batch or expiry tracking change this?

A batches table (item_id, batch_no, expires_on) and batch_id on stock and stock_moves — quantities then live per batch, and FEFO picking becomes an ORDER BY expires_on.

More example schemas