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. It has two foreign keys into the same accounts table, one for the debited account and one for the credited. Its amounts are never updated, only appended.

Below, ErdDocs draws this schema from its SQL, every table and every relation. The Data Dictionary tab has the printable reference, complete and free on this page. The SQL is further down.

Your schema

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';

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';

Your diagram appears here.

Hover a table to light up its relations, and click to pin them.

What each table does

customers / accountsOne-to-many. Money moves between accounts, not between customers.
branchesWhere the account was opened. It is a reporting dimension more than behavior.
transfersTWO foreign keys into accounts: from_account and to_account. The diagram shows both edges landing on one table.
cardsAttached 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, and an append-only ledger cannot silently disagree with its own history. Cache it later if a query needs speed, and keep the ledger as the source of 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, as in real accounting, so 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. You are free to copy it.

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 simplified 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

Inventory Management System ER diagram
CRM ER diagram
All example schemas
Data dictionary generator: the documentation side of the same tool.