Loan Management Database Schema

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
  }