Record-to-Report (R2R) data model template

Tables in this data model

• company_codes — 4 columns
• gl_accounts — 5 columns
• ledgers — 3 columns
• fiscal_periods — 5 columns
• cost_centers — 3 columns
• journal_headers — 7 columns
• journal_lines — 7 columns
• fx_rates — 5 columns
• intercompany_pairs — 4 columns
• account_reconciliations — 6 columns
• fs_line_items — 4 columns
• fs_account_mapping — 3 columns
• consolidation_balances — 6 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 company_codes (
  company_code_id INTEGER      NOT NULL PRIMARY KEY,
  code            VARCHAR(10)  NOT NULL UNIQUE,
  name            VARCHAR(150) NOT NULL,
  local_currency  CHAR(3)      NOT NULL
);

CREATE TABLE gl_accounts (
  account_id   INTEGER      NOT NULL PRIMARY KEY,
  account_no   VARCHAR(20)  NOT NULL UNIQUE,
  name         VARCHAR(150) NOT NULL,
  account_type VARCHAR(20)  NOT NULL,
  is_reconciliation BOOLEAN NOT NULL
);

CREATE TABLE ledgers (
  ledger_id INTEGER     NOT NULL PRIMARY KEY,
  name      VARCHAR(40) NOT NULL,
  gaap      VARCHAR(20) NOT NULL
);

CREATE TABLE fiscal_periods (
  period_id       INTEGER     NOT NULL PRIMARY KEY,
  company_code_id INTEGER     NOT NULL REFERENCES company_codes(company_code_id),
  fiscal_year     INTEGER     NOT NULL,
  period_no       INTEGER     NOT NULL,
  status          VARCHAR(10) NOT NULL
);

CREATE TABLE cost_centers (
  cost_center_id  INTEGER     NOT NULL PRIMARY KEY,
  company_code_id INTEGER     NOT NULL REFERENCES company_codes(company_code_id),
  code            VARCHAR(20) NOT NULL
);

CREATE TABLE journal_headers (
  journal_id      INTEGER     NOT NULL PRIMARY KEY,
  company_code_id INTEGER     NOT NULL REFERENCES company_codes(company_code_id),
  ledger_id       INTEGER     NOT NULL REFERENCES ledgers(ledger_id),
  period_id       INTEGER     NOT NULL REFERENCES fiscal_periods(period_id),
  source          VARCHAR(20) NOT NULL,
  posted_on       DATE        NOT NULL,
  reversal_of     INTEGER     REFERENCES journal_headers(journal_id)
);

CREATE TABLE journal_lines (
  line_id        INTEGER       NOT NULL PRIMARY KEY,
  journal_id     INTEGER       NOT NULL REFERENCES journal_headers(journal_id),
  account_id     INTEGER       NOT NULL REFERENCES gl_accounts(account_id),
  cost_center_id INTEGER       REFERENCES cost_centers(cost_center_id),
  debit_lc       DECIMAL(18,2) NOT NULL,
  credit_lc      DECIMAL(18,2) NOT NULL,
  doc_currency   CHAR(3)       NOT NULL
);

CREATE TABLE fx_rates (
  rate_id    INTEGER       NOT NULL PRIMARY KEY,
  from_ccy   CHAR(3)       NOT NULL,
  to_ccy     CHAR(3)       NOT NULL,
  rate_date  DATE          NOT NULL,
  rate       DECIMAL(18,8) NOT NULL
);

CREATE TABLE intercompany_pairs (
  ic_id           INTEGER NOT NULL PRIMARY KEY,
  journal_id      INTEGER NOT NULL REFERENCES journal_headers(journal_id),
  partner_journal INTEGER NOT NULL REFERENCES journal_headers(journal_id),
  matched         BOOLEAN NOT NULL
);

CREATE TABLE account_reconciliations (
  recon_id   INTEGER       NOT NULL PRIMARY KEY,
  account_id INTEGER       NOT NULL REFERENCES gl_accounts(account_id),
  period_id  INTEGER       NOT NULL REFERENCES fiscal_periods(period_id),
  gl_balance DECIMAL(18,2) NOT NULL,
  sub_balance DECIMAL(18,2) NOT NULL,
  approved_by VARCHAR(100)
);

CREATE TABLE fs_line_items (
  fs_item_id INTEGER      NOT NULL PRIMARY KEY,
  statement  VARCHAR(10)  NOT NULL,
  caption    VARCHAR(150) NOT NULL,
  sort_order INTEGER      NOT NULL
);

CREATE TABLE fs_account_mapping (
  mapping_id INTEGER NOT NULL PRIMARY KEY,
  account_id INTEGER NOT NULL REFERENCES gl_accounts(account_id),
  fs_item_id INTEGER NOT NULL REFERENCES fs_line_items(fs_item_id)
);

CREATE TABLE consolidation_balances (
  cons_id         INTEGER       NOT NULL PRIMARY KEY,
  company_code_id INTEGER       NOT NULL REFERENCES company_codes(company_code_id),
  period_id       INTEGER       NOT NULL REFERENCES fiscal_periods(period_id),
  fs_item_id      INTEGER       NOT NULL REFERENCES fs_line_items(fs_item_id),
  amount_group_ccy DECIMAL(18,2) NOT NULL,
  elimination     BOOLEAN       NOT NULL
);

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