我们有跟踪贷款定期支付情况的网络申请,目前我们正在一个象我的ql数据库管理贷款:

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]

代码流如下:

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 当前 安装第5号,如果用户在安装第2号时更新付款,所有计算<代码>前和penalty都将是错误的。 这样做是有道理的,因为刺激已经发给客户。

• 目前有许多报告使用<代码>前和penalty栏。

我们的询问:

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)