I think the simplest way to manage fines would be to have one table for invoices, and a separate table for credits. Figuring the amount outstanding would be a (relatively) simple sum of a union query. And nether table would ever be touched for updates.
That could easily be step 2, after this is complete.
The trick to getting this to work is having a third table just for tracking the association of credits to invoices. If a payment is made that is more than a single invoice, more rows would be inserted to reflect that. Similarly if many payments are made for one invoice, more rows would be inserted.
Is there a reason that the payments table couldn't just have a foreign key to the invoices table? I assume it's required so a single payment can reference multiple invoices. That is a limitation of what I proposed. To make a single payment against multiple invoices in my proposal, it requires breaking that single payment into multiple smaller payments. Not hard to code, but I don't know what impact this has on accounting practices.
Figuring out if a fine has been paid would be a simple join of the tracking table with the credit table, and sum.
A key would have to be ensured for proper operation, and an index should be added of course. Joe favors adding a column for the key. I would suggest that accountno,borrowernumber could do the job, and wouldn't change the current structure as much. But it doesn't matter as long as the key column ends up being called accountno, that way much less code has to be changed.
I'm in favor of a single field primary key, it makes referencing particular data much easier imho. My work on the reserves system would have been far easier if the table had a reservenumber primary key, instead of a combination of borrowernumber/biblionumber ( which has a side affect of creating limitations on what can be reserved. )
The drawback to this proposal is that I'm talking about overhauling the backend of the fines module. On the other hand if the module is written well we would only have to change a few files; the module itself and a few reports. I haven't looked to see how many, this is just a ball-park guess.
Indeed, this would be a far larger task. A good one, but much more work. That's why I think it would be very good to add to the roadmap.
Moving to this sort of structure is the real hitch. It wouldn't be easy. I imagine a couple queries, one to copy credit lines to the credits table and one to copy fine lines to the fines table. The amountoutstanding column could effectively be ignored since it's status should be reflected well by the tracking table. The accountoffsets table could be used to form the tracking table with minimal data loss. If the borrowernumber,accountno combination isn't used for the key, then a temporary column could be added to the accountlines table and filled via an SQL variable to reflect what the key in the new table will be. Ideally there would be no data loss because the accountoffsets table is being used properly in all code. I'm not an idealist.
What is the point of the accountoffsets table? With all the work I've done, I've never once had to work with it. Kyle