Order-to-Cash (O2C) data model template

Tables in this data model

• customers — 5 columns
• customer_sites — 4 columns
• products — 3 columns
• price_lists — 5 columns
• quotes — 4 columns
• sales_orders — 7 columns
• order_lines — 5 columns
• deliveries — 5 columns
• delivery_lines — 4 columns
• invoices — 6 columns
• ar_open_items — 4 columns
• cash_receipts — 5 columns
• cash_applications — 4 columns
• credit_memos — 4 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 customers (
  customer_id  INTEGER       NOT NULL PRIMARY KEY,
  name         VARCHAR(150)  NOT NULL,
  credit_limit DECIMAL(14,2) NOT NULL,
  payment_terms VARCHAR(20)  NOT NULL,
  segment      VARCHAR(30)
);

CREATE TABLE customer_sites (
  site_id     INTEGER      NOT NULL PRIMARY KEY,
  customer_id INTEGER      NOT NULL REFERENCES customers(customer_id),
  site_type   VARCHAR(10)  NOT NULL,
  address     VARCHAR(250) NOT NULL
);

CREATE TABLE products (
  product_id INTEGER      NOT NULL PRIMARY KEY,
  sku        VARCHAR(30)  NOT NULL UNIQUE,
  name       VARCHAR(150) NOT NULL
);

CREATE TABLE price_lists (
  price_id    INTEGER       NOT NULL PRIMARY KEY,
  product_id  INTEGER       NOT NULL REFERENCES products(product_id),
  customer_id INTEGER       REFERENCES customers(customer_id),
  unit_price  DECIMAL(12,2) NOT NULL,
  valid_to    DATE
);

CREATE TABLE quotes (
  quote_id    INTEGER     NOT NULL PRIMARY KEY,
  customer_id INTEGER     NOT NULL REFERENCES customers(customer_id),
  status      VARCHAR(20) NOT NULL,
  expires_on  DATE
);

CREATE TABLE sales_orders (
  order_id     INTEGER     NOT NULL PRIMARY KEY,
  customer_id  INTEGER     NOT NULL REFERENCES customers(customer_id),
  quote_id     INTEGER     REFERENCES quotes(quote_id),
  ship_to_id   INTEGER     NOT NULL REFERENCES customer_sites(site_id),
  order_no     VARCHAR(20) NOT NULL UNIQUE,
  credit_status VARCHAR(20) NOT NULL,
  ordered_on   DATE        NOT NULL
);

CREATE TABLE order_lines (
  order_line_id INTEGER       NOT NULL PRIMARY KEY,
  order_id      INTEGER       NOT NULL REFERENCES sales_orders(order_id),
  product_id    INTEGER       NOT NULL REFERENCES products(product_id),
  quantity      DECIMAL(12,3) NOT NULL,
  net_price     DECIMAL(12,2) NOT NULL
);

CREATE TABLE deliveries (
  delivery_id INTEGER     NOT NULL PRIMARY KEY,
  order_id    INTEGER     NOT NULL REFERENCES sales_orders(order_id),
  carrier     VARCHAR(60),
  shipped_on  DATE,
  status      VARCHAR(20) NOT NULL
);

CREATE TABLE delivery_lines (
  delivery_line_id INTEGER       NOT NULL PRIMARY KEY,
  delivery_id      INTEGER       NOT NULL REFERENCES deliveries(delivery_id),
  order_line_id    INTEGER       NOT NULL REFERENCES order_lines(order_line_id),
  qty_shipped      DECIMAL(12,3) NOT NULL
);

CREATE TABLE invoices (
  invoice_id  INTEGER       NOT NULL PRIMARY KEY,
  customer_id INTEGER       NOT NULL REFERENCES customers(customer_id),
  delivery_id INTEGER       REFERENCES deliveries(delivery_id),
  invoice_no  VARCHAR(20)   NOT NULL UNIQUE,
  amount      DECIMAL(14,2) NOT NULL,
  due_on      DATE          NOT NULL
);

CREATE TABLE ar_open_items (
  open_item_id INTEGER       NOT NULL PRIMARY KEY,
  invoice_id   INTEGER       NOT NULL REFERENCES invoices(invoice_id),
  balance      DECIMAL(14,2) NOT NULL,
  dunning_level INTEGER      NOT NULL
);

CREATE TABLE cash_receipts (
  receipt_id  INTEGER       NOT NULL PRIMARY KEY,
  customer_id INTEGER       REFERENCES customers(customer_id),
  amount      DECIMAL(14,2) NOT NULL,
  received_on DATE          NOT NULL,
  bank_ref    VARCHAR(40)
);

CREATE TABLE cash_applications (
  application_id INTEGER       NOT NULL PRIMARY KEY,
  receipt_id     INTEGER       NOT NULL REFERENCES cash_receipts(receipt_id),
  open_item_id   INTEGER       NOT NULL REFERENCES ar_open_items(open_item_id),
  applied_amount DECIMAL(14,2) NOT NULL
);

CREATE TABLE credit_memos (
  memo_id    INTEGER       NOT NULL PRIMARY KEY,
  invoice_id INTEGER       NOT NULL REFERENCES invoices(invoice_id),
  reason     VARCHAR(60)   NOT NULL,
  amount     DECIMAL(14,2) NOT NULL
);

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