Database schema design for loan repayment management

Question

We have a web-app for tracking periodic payments for a loan, currently we are managing it in a mysql database like this:

loan_payments table with the following columns [ id, customerId, installmentNo, installmentAmount, penalty, previousOutstanding, totalReceivable, amountReceived ]

receipts table with the following columns [ id, loan_payments.id (FK), paymentAmount, otherPaymentDetails]

The code flow is as follows:

1. During creation of new Loan, nrInstallments rows are entered in the loan_payments table for that customer. Assuming there are fixed 10 installments for all customers, 10 rows will be created
2. For the first row ( installmentNo = 1 ), the penalty and previousOutstanding will be set to 0.
3. Whenever a new payment is received, the amountReceived is incremented by that amount in the current installment ( installmentNo = 1) and an entry is done in the payments table. *At any give time there is only ONE current installment*
4. When its time for next installment ( installmentNo = 2) , the previous installment's [ totalReceivable - amountReceived ] is inserted into the next installment's ( installmentNo = 2) previousOutstanding. All previous payments/installments are frozen. And intimation is sent to customers indicating, installmentAmount, penalty and previousOutstanding to be paid.
5. Now all payments will be received against this current installment (installmentNo = 2) and its amountReceived will be incremented whenever a new payment is received.
6. All penalty calculation will be done against the current installment.

Currently we do not provide update/delete of any payments that does not belong to the current installment.

Everything was working fine, until the client asked for a feature to update/delete previous payments. Following are the problems we will face, if we allow update/delete of previous payments

• Suppose current installment no is 5, If user updates payment with installment no 2, all the calculation of previousOutstanding and penalty will be wrong. This doesnt make sense since, intimations were already sent to customers.

• There are lot of reports currently using the previousOutstanding and the penalty columns.

Our queries:

1. Is it good design to store previousOutstanding and penalty in the database? Or should it be calculated in the code?
2. How do we redesign the logic/database to allow the following.
   1. Take payment against ANY installmentNo
   2. Allow update/delete of any previous Payment
   3. Flexible penalty calculation. ( Take % from user if required )
   4. Ability to waive off penalty for particular customer for particular installmentNo.
   5. If possible, report showing how much penalty was waived-off for a given customer against which installmentNo. ( If this requirement makes the design complex, we can drop it)

Answer 1

An accounting database should be easy to audit, which means it is much better to make it append-only and not edit any old rows. If some columns contain precalculated aggregates, denormalise by dropping them, and put them in a view so your reports still work. The mailings you sent using snapshots of aggregate values should be stored in another append-only table, and since you define these to be snapshots they won't become inaccurate.