Example schemas / Inventory Management System

Inventory Management System ER diagram

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

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

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)';

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)';

Your diagram appears here.

Hover a table to light up its relations, and click to pin them.

What each table does

itemsThe catalog. The unit column is needed as soon as anything is measured in kg or meters.
warehouses / stockstock is a composite-key table: one row per (warehouse, item), the running total.
purchase_orders / po_itemsThe order header and its lines, the same header/line split as an e-commerce order.
stock_movesAn 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), and the log answers "why?". 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. You are free to copy it.

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 with sales_orders and so_items. Shipments create 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?

Add a batches table (item_id, batch_no, expires_on) and put batch_id on stock and stock_moves. Quantities then live per batch, and FEFO picking becomes an ORDER BY expires_on.

More example schemas

CRM ER diagram
Restaurant Management System ER diagram
All example schemas
Data dictionary generator: the documentation side of the same tool.