CloudSketch AI Logo
FeaturesTemplatesPricingEnterpriseAboutContactLog in
🌙☀️
Log inStart free
Home/Templates/Fintech & Capital Markets

💹 Fintech & Capital Markets · Database ERD

Loan Management Database Schema

Tables for a loan book: borrowers, loan products, loans, repayment schedules, payments and collection actions.

More Fintech & Capital Markets templates

Opening a template is free and never counts toward your daily diagrams. Asking the AI to change it counts as one.

Drawing diagram…

What this diagram shows

  • Each loan has a full repayment schedule from day one
  • Payments are matched to schedule instalments
  • Collection actions are logged per overdue instalment

Prompt used

Loan management database: borrowers take loans of a loan product; each loan has instalments (due date, principal, interest); payments are recorded and allocated to instalments; overdue instalments have collection actions (call, SMS, field visit).

Mermaid code
erDiagram
  BORROWER ||--o{ LOAN : takes
  LOAN_PRODUCT ||--o{ LOAN : "offered as"
  LOAN ||--|{ INSTALMENT : "repaid in"
  LOAN ||--o{ PAYMENT : receives
  PAYMENT ||--o{ ALLOCATION : "split into"
  INSTALMENT ||--o{ ALLOCATION : "paid by"
  INSTALMENT ||--o{ COLLECTION_ACTION : "followed up by"
  BORROWER {
    int id PK
    string name
    string pan
    string mobile
  }
  LOAN_PRODUCT {
    int id PK
    string name
    decimal interest_rate
    int tenure_months
  }
  LOAN {
    int id PK
    int borrower_id FK
    int product_id FK
    decimal principal
    date disbursed_on
    string status
  }
  INSTALMENT {
    int id PK
    int loan_id FK
    date due_date
    decimal principal_due
    decimal interest_due
    string status
  }
  PAYMENT {
    int id PK
    int loan_id FK
    decimal amount
    string mode
    date paid_on
  }
  ALLOCATION {
    int id PK
    int payment_id FK
    int instalment_id FK
    decimal amount
  }
  COLLECTION_ACTION {
    int id PK
    int instalment_id FK
    string action
    date action_on
  }

Inside this diagram

An entity relationship diagram with 7 tables, 33 columns and 7 relationships.

Tables and their columns

  • BORROWER: id (PK), name, pan, mobile
  • LOAN: id (PK), borrower_id (FK), product_id (FK), principal, disbursed_on, status
  • LOAN_PRODUCT: id (PK), name, interest_rate, tenure_months
  • INSTALMENT: id (PK), loan_id (FK), due_date, principal_due, interest_due, status
  • PAYMENT: id (PK), loan_id (FK), amount, mode, paid_on
  • ALLOCATION: id (PK), payment_id (FK), instalment_id (FK), amount
  • COLLECTION_ACTION: id (PK), instalment_id (FK), action, action_on

Relationships

  • BORROWER takes LOAN: each BORROWER has zero or more LOAN, each LOAN belongs to exactly one BORROWER
  • LOAN_PRODUCT offered as LOAN: each LOAN_PRODUCT has zero or more LOAN, each LOAN belongs to exactly one LOAN_PRODUCT
  • LOAN repaid in INSTALMENT: each LOAN has one or more INSTALMENT, each INSTALMENT belongs to exactly one LOAN
  • LOAN receives PAYMENT: each LOAN has zero or more PAYMENT, each PAYMENT belongs to exactly one LOAN
  • PAYMENT split into ALLOCATION: each PAYMENT has zero or more ALLOCATION, each ALLOCATION belongs to exactly one PAYMENT
  • INSTALMENT paid by ALLOCATION: each INSTALMENT has zero or more ALLOCATION, each ALLOCATION belongs to exactly one INSTALMENT
  • INSTALMENT followed up by COLLECTION_ACTION: each INSTALMENT has zero or more COLLECTION_ACTION, each COLLECTION_ACTION belongs to exactly one INSTALMENT

Make your own diagram like this

Describe your own system in plain English and the AI ER diagram generator draws it in seconds, in the same style as this template.

Related templates

C4 ArchitectureProFintech & Capital Markets

Digital Lending Platform

A digital lending app that takes a loan application from KYC to disbursal in minutes, using credit bureau data, bank statement analysis and e-mandates for repayment.

SequenceFintech & Capital Markets

Account Aggregator Consent Flow

How a lender gets a customer's bank statements through India's Account Aggregator network: consent request, approval in the AA app, and encrypted data sharing.

FlowchartFintech & Capital Markets

Buy Now Pay Later Checkout

How a Buy Now Pay Later option decides at checkout whether to approve a shopper and splits the purchase into instalments.

State MachineFintech & Capital Markets

Stock Order Lifecycle

The states of a stock market order in a broking app, from placement through exchange matching to partial and full fills.

DeploymentProFintech & Capital Markets

Stock Broking Platform

Where a stock broking platform runs: low-latency order systems co-located near the exchange, and customer apps and back office in the cloud.

SequenceFintech & Capital Markets

Mutual Fund SIP Purchase Sequence

How a monthly SIP in a mutual fund works behind an investment app: mandate debit, order to the exchange platform, NAV allotment and units credited.