Banking Database ER Diagram

A core banking schema centers on accounts: customers own them through a join table, each account belongs to a branch and an account type, and every movement is written to transactions with the resulting balance. Cards and transfers sit alongside as the instruments that move money.

Ownership is deliberately many-to-many. account_holders joins customers and accounts, carries holder_role, and uses the composite primary key (account_id, customer_id), so joint accounts and a customer with several accounts both work without duplicating either side. account_number is UNIQUE independently of the surrogate id, because it is the identifier customers and other banks actually use. accounts.balance is stored rather than computed on every read, and transactions.balance_after snapshots the balance immediately after each movement, which makes statements reproducible even if later corrections are inserted. Statements can therefore be rebuilt from the ledger without trusting a mutable balance column.

The diagram has a reference arm and a movement arm. branches and account_types feed accounts, and accounts feed account_holders on one side and transactions, cards and transfers on the other. transactions.direction uses a CHECK restricting it to debit or credit, while amount stays positive and balance_after records the running result. transfers is different from a transaction: it points at a from_account_id and a to_account_id and checks that the two differ, capturing the intent before the matching debit and credit rows are written. cards.account_id is UNIQUE, so this design allows a single card per account.

Money schemas fail when history is rewritten. Deleting an account cascades to its card and transaction rows, which is acceptable for test data but dangerous in production; most banks instead flip accounts.status to closed and keep the ledger. Deleting a customer is blocked while account_holders rows remain, protecting ownership records. The balance_after column is a snapshot, not a guarantee: if two transactions commit concurrently, the stored running balance can disagree with the ledger order. Enforcing it requires serializing writes per account or deriving the balance inside the transaction that inserts the row.

Extensions depend on how far the system goes. A ledger_entries table can replace the single balance column with an append-only double-entry model, where every movement has equal and opposite lines. A beneficiaries table between customers and transfers stores saved recipient details, including external bank accounts that are not in accounts. Card controls such as blocked, contactless_limit and expires_at fit naturally on cards, and an audit_log table is usually required before any of this reaches a regulated environment. Any of these can be introduced beside the current tables, because the account remains the stable anchor.

The diagram

Tables (8)

Relationships (8)

• 1:Nbranches.id→accounts.branch_id
• 1:Naccount_types.id→accounts.account_type_id
• 1:1accounts.id→account_holders.account_idON DELETE CASCADE
• 1:1customers.id→account_holders.customer_id
• 1:Naccounts.id→transactions.account_idON DELETE CASCADE
• 1:1accounts.id→cards.account_idON DELETE CASCADE
• 1:Naccounts.id→transfers.from_account_id
• 1:Naccounts.id→transfers.to_account_id

The SQL

CREATE TABLE branches (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  city VARCHAR(80) NOT NULL,
  opened_on DATE
);

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  phone VARCHAR(30),
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE account_types (
  id SERIAL PRIMARY KEY,
  name VARCHAR(60) NOT NULL UNIQUE,
  interest_rate DECIMAL(5,2) NOT NULL DEFAULT 0
);

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  branch_id INTEGER NOT NULL REFERENCES branches(id),
  account_type_id INTEGER NOT NULL REFERENCES account_types(id),
  account_number VARCHAR(20) NOT NULL UNIQUE,
  opened_at DATE NOT NULL DEFAULT CURRENT_DATE,
  balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL DEFAULT 'active'
);

CREATE TABLE account_holders (
  account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  holder_role VARCHAR(20) NOT NULL DEFAULT 'primary',
  PRIMARY KEY (account_id, customer_id)
);

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  direction VARCHAR(6) NOT NULL CHECK (direction IN ('debit', 'credit')),
  amount DECIMAL(14,2) NOT NULL CHECK (amount > 0),
  balance_after DECIMAL(14,2) NOT NULL,
  description VARCHAR(200),
  occurred_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE cards (
  id SERIAL PRIMARY KEY,
  account_id INTEGER NOT NULL UNIQUE REFERENCES accounts(id) ON DELETE CASCADE,
  card_number VARCHAR(20) NOT NULL UNIQUE,
  expiry_date DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active'
);

CREATE TABLE transfers (
  id SERIAL PRIMARY KEY,
  from_account_id INTEGER NOT NULL REFERENCES accounts(id),
  to_account_id INTEGER NOT NULL REFERENCES accounts(id),
  amount DECIMAL(14,2) NOT NULL CHECK (amount > 0),
  created_at TIMESTAMP NOT NULL DEFAULT now(),
  CHECK (from_account_id <> to_account_id)
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides