Practitioner Knowledge Base •

Database Schema Patterns for Specialist Lending Solutions

Practical database design patterns for loan management, payment history, and account relationships. Balancing normalization with query performance.

Writing database schema opensearch

Schema Design for Specialist Lending Solutions

Lending systems have complex data relationships. Loans have borrowers. Borrowers have accounts. Accounts have payments. Payments have transactions. Get the schema wrong and you’ll fight the database forever.

The Core Tables

Start with these:

loans: The loan itself. Principal, rate, term, origination date, status.

borrowers: Who owes the money. Name, contact info, credit profile.

accounts: The payment account. A borrower can have multiple accounts. An account can be shared between borrowers (co-signers).

payments: Money moving into the system. Expected amount, actual amount, due date, paid date.

transactions: The ledger. Every financial movement gets a transaction record. Debits, credits, fees, refunds.

The Relationships

loans.borrower_id → borrowers.id is straightforward. But what about co-signers? Don’t denormalize into loans.co_signer_id. That breaks when there are three signers.

Instead: loan_parties table. Maps loans to borrowers with a role (primary, co-signer, guarantor). Query joins get slightly more complex but the data model stays flexible.

Handling Payment History

Every payment creates multiple transactions. A $100 loan payment might split into $80 principal, $15 interest, $5 fees. Store this granularly:

transactions (
  id,
  payment_id,
  loan_id,
  type, -- principal, interest, fee
  amount,
  created_at
)

Now you can report on interest collected, principal paid down, fees charged. All from the same table.

Audit Trails

Financial systems need complete audit history. Who changed what, when, and why.

Don’t use separate audit tables. Use temporal tables or a changelog pattern:

loan_changes (
  id,
  loan_id,
  field_name,
  old_value,
  new_value,
  changed_by,
  changed_at,
  reason
)

Every update to the loans table writes a row here. Now you can reconstruct the entire history of any loan.

Indexes That Matter

Index on status and dates:

  • loans (status, next_payment_date)
  • payments (loan_id, due_date)
  • transactions (created_at, type)

These support the queries you run constantly: “Show me all active loans with payments due today”, “Sum transactions by type for this loan”, “Find payments in the last 30 days”.

Don’t over-index. Every index slows down writes. Start with the queries you actually run, then add indexes as needed.

Soft Deletes vs Hard Deletes

Never hard delete financial data. Use soft deletes:

ALTER TABLE loans ADD COLUMN deleted_at TIMESTAMP;

Filter by deleted_at IS NULL in queries. Keep the data forever. Storage is cheap, regulatory compliance is expensive.

← Back to all topics