ErdDocs

Example schemas / Banking System

Banking System ER diagram

A banking system database is customers holding accounts and a ledger of transfers between them. The instructive part is the transfers table: two foreign keys into the same accounts table — one for the debited account, one for the credited — and amounts that are never updated, only appended.

Your schema

Hover a table to light up its relations, click to pin. Switch to Data Dictionary for the printable reference version of this schema.

What each table does

  • customers / accounts — One-to-many; the account, not the customer, is what money moves between.
  • branches — Where the account was opened — reporting dimension more than behavior.
  • transfers — TWO foreign keys into accounts: from_account and to_account. The diagram shows both edges landing on one table.
  • cards — Attached to the account; only the last four digits are stored on purpose.

Design decisions worth copying

  • Balance is not a column. It is SUM(credits) - SUM(debits) over transfers — an append-only ledger cannot silently disagree with its own history. Cache it later if a query needs speed; the ledger stays the truth.
  • Nullable from/to models the boundary of the system: a deposit has no source account, a withdrawal no destination. The CHECK keeps at least one side real.
  • Transfers are never UPDATEd. A mistake is corrected by a reversing row — exactly how real accounting works, and why the table has no updated_at.

The SQL

PostgreSQL dialect; paste it into the tool above (it is already there), into your own database, or into a migration. Copy freely — example schemas are meant to be taken.

CREATE TABLE customers (
    id         bigserial    PRIMARY KEY,
    tax_id     varchar(20)  NOT NULL UNIQUE,
    full_name  varchar(120) NOT NULL,
    joined_on  date         NOT NULL
);

CREATE TABLE branches (
    id    bigserial   PRIMARY KEY,
    code  varchar(10) NOT NULL UNIQUE,
    city  varchar(80) NOT NULL
);

CREATE TABLE accounts (
    id          bigserial     PRIMARY KEY,
    customer_id bigint        NOT NULL REFERENCES customers(id),
    branch_id   bigint        NOT NULL REFERENCES branches(id),
    iban        varchar(34)   NOT NULL UNIQUE,
    kind        varchar(20)   NOT NULL,
    opened_on   date          NOT NULL,
    closed_on   date
);
COMMENT ON COLUMN accounts.kind IS 'checking / savings / loan';
COMMENT ON COLUMN accounts.closed_on IS 'NULL while the account is open';

CREATE TABLE transfers (
    id            bigserial     PRIMARY KEY,
    from_account  bigint        REFERENCES accounts(id),
    to_account    bigint        REFERENCES accounts(id),
    amount        numeric(14,2) NOT NULL CHECK (amount > 0),
    made_at       timestamptz   NOT NULL DEFAULT now(),
    memo          varchar(140),
    CHECK (from_account IS NOT NULL OR to_account IS NOT NULL)
);
COMMENT ON COLUMN transfers.from_account IS 'NULL for cash deposits (money enters the system)';
COMMENT ON COLUMN transfers.to_account IS 'NULL for cash withdrawals (money leaves the system)';

CREATE TABLE cards (
    id          bigserial   PRIMARY KEY,
    account_id  bigint      NOT NULL REFERENCES accounts(id),
    pan_last4   varchar(4)  NOT NULL,
    expires     date        NOT NULL,
    active      boolean     NOT NULL DEFAULT true
);
COMMENT ON COLUMN cards.pan_last4 IS 'Never store the full card number';

FAQ

Is a single transfers table real double-entry bookkeeping?

It is the honest study version: one row carries both sides atomically. Full double-entry splits each transfer into two ledger_entries rows (a debit and a credit) — do that when transfers can touch more than two accounts, e.g. fees taken mid-transfer.

Why numeric and not float for money?

Floats cannot represent 0.10 exactly and the errors compound across a ledger. numeric(14,2) is exact; every real banking schema uses decimal types or integer cents.

More example schemas