General ledger accounting schema template

Tables in this data model

• accounts — 4 columns
• fiscal_periods — 4 columns
• cost_centers — 2 columns
• journal_entries — 5 columns
• journal_lines — 6 columns
• vendors — 2 columns
• bills — 5 columns
• bank_transactions — 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 accounts (
  account_id   INTEGER      NOT NULL PRIMARY KEY,
  code         VARCHAR(10)  NOT NULL UNIQUE,
  name         VARCHAR(120) NOT NULL,
  account_type VARCHAR(30)  NOT NULL
);

CREATE TABLE fiscal_periods (
  period_id  INTEGER NOT NULL PRIMARY KEY,
  starts_on  DATE    NOT NULL,
  ends_on    DATE    NOT NULL,
  is_closed  BOOLEAN NOT NULL
);

CREATE TABLE cost_centers (
  cost_center_id INTEGER     NOT NULL PRIMARY KEY,
  name           VARCHAR(80) NOT NULL
);

CREATE TABLE journal_entries (
  entry_id   INTEGER      NOT NULL PRIMARY KEY,
  period_id  INTEGER      NOT NULL REFERENCES fiscal_periods(period_id),
  entry_date DATE         NOT NULL,
  source     VARCHAR(20)  NOT NULL,
  memo       VARCHAR(255)
);

CREATE TABLE journal_lines (
  line_id        INTEGER       NOT NULL PRIMARY KEY,
  entry_id       INTEGER       NOT NULL REFERENCES journal_entries(entry_id),
  account_id     INTEGER       NOT NULL REFERENCES accounts(account_id),
  cost_center_id INTEGER       REFERENCES cost_centers(cost_center_id),
  debit          DECIMAL(14,2) DEFAULT 0,
  credit         DECIMAL(14,2) DEFAULT 0
);

CREATE TABLE vendors (
  vendor_id INTEGER      NOT NULL PRIMARY KEY,
  name      VARCHAR(150) NOT NULL
);

CREATE TABLE bills (
  bill_id   INTEGER       NOT NULL PRIMARY KEY,
  vendor_id INTEGER       NOT NULL REFERENCES vendors(vendor_id),
  entry_id  INTEGER       REFERENCES journal_entries(entry_id),
  amount    DECIMAL(14,2) NOT NULL,
  due_on    DATE
);

CREATE TABLE bank_transactions (
  txn_id    INTEGER       NOT NULL PRIMARY KEY,
  entry_id  INTEGER       REFERENCES journal_entries(entry_id),
  amount    DECIMAL(14,2) NOT NULL,
  posted_on DATE          NOT NULL,
  reconciled BOOLEAN      NOT NULL
);

Related: ER diagram from SQL · All finance & accounting templates · All 521 templates