Banking System Database Schema database schema

What this schema models

A core banking database schema built the way banks actually build them: on a double-entry ledger. 34 tables and 33 enums covering customers and KYC, branches and staff, multi-currency accounts, the ledger itself, payments and mandates, cards and authorisations, lending with amortisation schedules, fees, statements, disputes and a write-once audit trail. THE RULE EVERYTHING ELSE FOLLOWS FROM Money moves as DOUBLE ENTRY. A transaction is only a header — it deliberately has no amount column, because a single transaction can span currencies. The actual movement lives in ledger_entries, two or more rows whose signed amounts sum to exactly ZERO. accounts.balance_minor is a cached running total that can always be rebuilt by summing entries. There is no UPDATE anywhere that moves money, only inserts. This matters because a bank that stores balance as the truth and transactions as a log cannot answer "why is this figure wrong" — and that question is the entire job. Reversal is a new, opposite transaction referencing the original. Nothing is ever deleted. SUPPORTING DECISIONS - Amounts are integers in MINOR UNITS, always beside an explicit currency. Rates are basis points. No floats anywhere. - currencies carries minor_unit_digits, because JPY has 0, USD has 2 and KWD has 3. Hard-coding a divisor of 100 is a bug waiting to happen. - Available balance is balance minus active holds, computed rather than stored. A card authorisation places a hold and reduces available funds without touching the ledger; capture releases the hold and posts a real transaction. That gap is precisely why available and current balance differ. - account_holders is many-to-many, so joint accounts have two primary holders and a signatory can transact without owning the balance. - Account restrictions and freezes are rows with a lifted_at, not booleans on accounts, so the history survives. - Customer address history is versioned with valid_from and valid_to. Regulators ask where somebody lived at a point in time. - Loans copy interest_rate_bps at approval; repricing the product later cannot rewrite an existing loan. The amortisation schedule is generated at disbursement and frozen, and comparing it against payments is what defines arrears. - Every loan payment records its own allocation across principal, interest and fees, so the split is never recomputed retroactively. - Interest accrues daily and posts monthly, and keeping the daily rows is what makes a mid-month closure calculable. - Statement opening and closing balances are frozen at generation, so a reissued statement for a past period shows the same figures forever. - Dispute provisional credit is a real transaction, reversed if the dispute fails — never a temporary balance adjustment. - audit_logs is append-only and write-once, capturing before and after values. It is the table an auditor actually asks for. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board with cards colour-coded by zone — customers, bank reference, accounts, ledger, payments, cards, lending, fees and statements, disputes and audit — with sticky notes explaining each decision and every table carrying its own Note. Postgres-flavoured types that port cleanly to MySQL, Oracle or SQL Server. Useful for developers building core banking, neobank or fintech ledger infrastructure, students who need a realistic banking ER diagram for a DBMS project, and teams designing money movement who want to see double-entry modelled properly before writing their first migration. This is a schema design reference, not a certified banking system — validate against your own regulatory and accounting requirements before production use.

Tables (34)

Relationships (75)

• account_holders.account_id references accounts.id many to one
• account_holders.customer_id references customers.id many to one
• account_products.currency references currencies.code many to one
• account_restrictions.account_id references accounts.id many to one
• account_restrictions.placed_by references employees.id many to one
• accounts.branch_id references branches.id many to one
• accounts.currency references currencies.code many to one
• accounts.product_id references account_products.id many to one
• alerts.account_id references accounts.id many to one
• alerts.customer_id references customers.id many to one
• audit_logs.actor_customer_id references customers.id many to one
• audit_logs.actor_employee_id references employees.id many to one
• beneficiaries.currency references currencies.code many to one
• beneficiaries.customer_id references customers.id many to one
• beneficiaries.external_bank_id references external_banks.id many to one
• card_authorizations.card_id references cards.id many to one
• card_authorizations.currency references currencies.code many to one
• card_authorizations.hold_id references holds.id many to one
• card_authorizations.transaction_id references transactions.id many to one
• cards.account_id references accounts.id many to one
• cards.customer_id references customers.id many to one
• customer_addresses.customer_id references customers.id many to one
• direct_debits.account_id references accounts.id many to one
• dispute_evidence.dispute_id references disputes.id many to one
• dispute_evidence.submitted_by references employees.id many to one
• disputes.card_authorization_id references card_authorizations.id many to one
• disputes.currency references currencies.code many to one
• disputes.customer_id references customers.id many to one
• disputes.provisional_credit_transaction_id references transactions.id many to one
• disputes.transaction_id references transactions.id many to one
• employees.branch_id references branches.id many to one
• exchange_rates.base_currency references currencies.code many to one
• exchange_rates.quote_currency references currencies.code many to one
• fee_charges.account_id references accounts.id many to one
• fee_charges.currency references currencies.code many to one
• fee_charges.fee_schedule_id references fee_schedules.id many to one
• fee_charges.transaction_id references transactions.id many to one
• fee_charges.waived_by references employees.id many to one
• fee_schedules.currency references currencies.code many to one
• fee_schedules.product_id references account_products.id many to one
• holds.account_id references accounts.id many to one
• holds.currency references currencies.code many to one
• holds.settled_transaction_id references transactions.id many to one
• interest_accruals.account_id references accounts.id many to one
• interest_accruals.currency references currencies.code many to one
• interest_accruals.loan_id references loans.id many to one
• interest_accruals.posted_transaction_id references transactions.id many to one
• kyc_documents.customer_id references customers.id many to one
• kyc_documents.verified_by references employees.id many to one
• ledger_entries.account_id references accounts.id many to one
• ledger_entries.category_id references transaction_categories.id many to one
• ledger_entries.currency references currencies.code many to one
• ledger_entries.transaction_id references transactions.id many to one
• loan_payments.loan_id references loans.id many to one
• loan_payments.schedule_entry_id references loan_schedule_entries.id many to one
• loan_payments.transaction_id references transactions.id many to one
• loan_products.currency references currencies.code many to one
• loan_schedule_entries.loan_id references loans.id many to one
• loans.approved_by references employees.id many to one
• loans.customer_id references customers.id many to one
• loans.disbursement_account_id references accounts.id many to one
• loans.product_id references loan_products.id many to one
• standing_orders.beneficiary_id references beneficiaries.id many to one
• standing_orders.currency references currencies.code many to one
• standing_orders.from_account_id references accounts.id many to one
• statements.account_id references accounts.id many to one
• statements.currency references currencies.code many to one
• transaction_categories.parent_id references transaction_categories.id many to one
• transactions.initiated_by references customers.id many to one
• transactions.reverses_transaction_id references transactions.id many to one
• transfers.beneficiary_id references beneficiaries.id many to one
• transfers.currency references currencies.code many to one
• transfers.from_account_id references accounts.id many to one
• transfers.to_account_id references accounts.id many to one
• transfers.transaction_id references transactions.id many to one

How to use this template

1. Open the diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy.
3. Export it as PostgreSQL, MySQL, or MSSQL DDL, or as C# models.

Frequently asked questions

What is in the Banking System Database Schema schema?

34 tables and 75 relationships, across 305 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, PostgreSQL, MySQL, or MSSQL DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.