Results 1 to 3 of 3

Thread: Loan Balances

  1. #1
    Join Date
    Apr 2012
    Location
    near Dallas, Tx
    Posts
    7

    Loan Balances

    I have a dilemma I am not sure how to handle. I have a web based client tracking system for social charities. one of the functions in the package is a loan tracking module. These people are not banks, these are small loans, and most of the time it's interest free.

    The dilemma is how to handle the balance. (the old aggregate problem). If I want to provide the ability to fix a prior payment, it seems like storing a balance anywhere is going to be problematic when changing entries that are not the last one in the list.

    My theoretical studies of Databasing says you don't store that which you can compute. I am about to the point of not having a current balance anywhere, just compute the balance by taking the loan amount and deducting the principle payments for each payment. This would give me the flexibility to change payment 3 of 8 perhaps, and be able to compute the current balance to any given payment occurrence by running the loan payment rows. I doubt there would ever be more than 10 payments for a particular loan and the loan_id in the payment segment is indexed, meaning the SQL call would be relatively efficient,

    I have been messing with data bases for about 30 years (IMS-DL/1 - Focus, SQL Access/DB even dBase). How to handle aggregates has always been a stichy wicket and I have seen it handled different ways. I am looking for some fresh ideas to an age old problem.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Theoretically, no, you should not store any value you can compute.

    But "In Theory, Theory and Practice are the same, but in Practice, Theory and Practice are different."

    If your database becomes very large, then recalculating the same value over and over again is an obvious waste of CPU.

    Plus, from an accounting standpoint, you should never modify old payment entries anyway. Every change should be done as a new adjustment record. There may even be legal requirements prohibiting you from deleting or modifying financial records.

    So, store the balance as a snapshot of the account after every transaction, and set up adjustment records for handling corrections. You could also write a stored procedure that would quickly recalculate all the point-in-time balances for any single account, should that become necessary.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Every change should be done as a new adjustment record.
    Modifying a Reversing Journal Entry


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •