Procure-to-Pay (P2P) data model template

Tables in this data model

• suppliers — 5 columns
• supplier_bank_accounts — 4 columns
• materials — 4 columns
• cost_centers — 3 columns
• purchase_requisitions — 5 columns
• requisition_lines — 5 columns
• contracts — 4 columns
• purchase_orders — 6 columns
• po_lines — 6 columns
• goods_receipts — 3 columns
• gr_lines — 4 columns
• supplier_invoices — 6 columns
• invoice_lines — 6 columns
• payments — 5 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE TABLE suppliers (
  supplier_id   INTEGER      NOT NULL PRIMARY KEY,
  name          VARCHAR(150) NOT NULL,
  tax_id        VARCHAR(30),
  payment_terms VARCHAR(20)  NOT NULL,
  status        VARCHAR(20)  NOT NULL
);

CREATE TABLE supplier_bank_accounts (
  bank_account_id INTEGER     NOT NULL PRIMARY KEY,
  supplier_id     INTEGER     NOT NULL REFERENCES suppliers(supplier_id),
  iban            VARCHAR(34) NOT NULL,
  verified        BOOLEAN     NOT NULL
);

CREATE TABLE materials (
  material_id INTEGER      NOT NULL PRIMARY KEY,
  material_no VARCHAR(30)  NOT NULL UNIQUE,
  description VARCHAR(200) NOT NULL,
  uom         VARCHAR(10)  NOT NULL
);

CREATE TABLE cost_centers (
  cost_center_id INTEGER     NOT NULL PRIMARY KEY,
  code           VARCHAR(20) NOT NULL,
  owner          VARCHAR(100)
);

CREATE TABLE purchase_requisitions (
  requisition_id INTEGER     NOT NULL PRIMARY KEY,
  cost_center_id INTEGER     NOT NULL REFERENCES cost_centers(cost_center_id),
  requested_by   VARCHAR(100) NOT NULL,
  status         VARCHAR(20) NOT NULL,
  created_on     DATE        NOT NULL
);

CREATE TABLE requisition_lines (
  req_line_id    INTEGER       NOT NULL PRIMARY KEY,
  requisition_id INTEGER       NOT NULL REFERENCES purchase_requisitions(requisition_id),
  material_id    INTEGER       NOT NULL REFERENCES materials(material_id),
  quantity       DECIMAL(12,3) NOT NULL,
  est_price      DECIMAL(12,2)
);

CREATE TABLE contracts (
  contract_id INTEGER NOT NULL PRIMARY KEY,
  supplier_id INTEGER NOT NULL REFERENCES suppliers(supplier_id),
  valid_from  DATE    NOT NULL,
  valid_to    DATE    NOT NULL
);

CREATE TABLE purchase_orders (
  po_id       INTEGER     NOT NULL PRIMARY KEY,
  supplier_id INTEGER     NOT NULL REFERENCES suppliers(supplier_id),
  contract_id INTEGER     REFERENCES contracts(contract_id),
  po_number   VARCHAR(20) NOT NULL UNIQUE,
  status      VARCHAR(20) NOT NULL,
  ordered_on  DATE        NOT NULL
);

CREATE TABLE po_lines (
  po_line_id  INTEGER       NOT NULL PRIMARY KEY,
  po_id       INTEGER       NOT NULL REFERENCES purchase_orders(po_id),
  req_line_id INTEGER       REFERENCES requisition_lines(req_line_id),
  material_id INTEGER       NOT NULL REFERENCES materials(material_id),
  quantity    DECIMAL(12,3) NOT NULL,
  unit_price  DECIMAL(12,2) NOT NULL
);

CREATE TABLE goods_receipts (
  gr_id       INTEGER NOT NULL PRIMARY KEY,
  po_id       INTEGER NOT NULL REFERENCES purchase_orders(po_id),
  received_on DATE    NOT NULL
);

CREATE TABLE gr_lines (
  gr_line_id   INTEGER       NOT NULL PRIMARY KEY,
  gr_id        INTEGER       NOT NULL REFERENCES goods_receipts(gr_id),
  po_line_id   INTEGER       NOT NULL REFERENCES po_lines(po_line_id),
  qty_received DECIMAL(12,3) NOT NULL
);

CREATE TABLE supplier_invoices (
  invoice_id  INTEGER       NOT NULL PRIMARY KEY,
  supplier_id INTEGER       NOT NULL REFERENCES suppliers(supplier_id),
  invoice_no  VARCHAR(40)   NOT NULL,
  amount      DECIMAL(14,2) NOT NULL,
  due_on      DATE          NOT NULL,
  match_status VARCHAR(20)  NOT NULL
);

CREATE TABLE invoice_lines (
  invoice_line_id INTEGER       NOT NULL PRIMARY KEY,
  invoice_id      INTEGER       NOT NULL REFERENCES supplier_invoices(invoice_id),
  po_line_id      INTEGER       NOT NULL REFERENCES po_lines(po_line_id),
  gr_line_id      INTEGER       REFERENCES gr_lines(gr_line_id),
  quantity        DECIMAL(12,3) NOT NULL,
  amount          DECIMAL(14,2) NOT NULL
);

CREATE TABLE payments (
  payment_id      INTEGER       NOT NULL PRIMARY KEY,
  invoice_id      INTEGER       NOT NULL REFERENCES supplier_invoices(invoice_id),
  bank_account_id INTEGER       NOT NULL REFERENCES supplier_bank_accounts(bank_account_id),
  amount          DECIMAL(14,2) NOT NULL,
  paid_on         DATE          NOT NULL
);

Related: Database schema diagram tool · ER diagram from SQL · All database & er models templates · All 521 templates