I'm all in favor of simplicity. The specific case I have in mind is the error on the circulation screen when a patron has fines, but the reports is as good a reason for simplicity.
I'm not sure I understand Joe's proposal, so I'm going to go from scratch with this idea. It's probably the same as what Joe has in mind. 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. 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. 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. 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. 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. Anyway, that's the idea. On Wed, 2009-09-30 at 14:11 -0400, Joe Atzberger wrote: [snip] > > Since you would still have the information, what are the arguments for > keeping that "amountoutstanding" field in the fine line that > represents the subtotal of payments made against it? Mainly > performance and some simplicity, since you don't have to requery the > table or build a nested query to calculate the amountoutstanding > value. But it produces a maintenance problem, since when you insert a > payment line, the fine line also has to be updated. All the > operations that adjust either have to touch both, and operations on at > the database level would be so error-prone as to be impractical. > > I would prefer that the fine line, once in the table is NEVER updated > (i.e., updated as little as possible, ideally NEVER), and that the > authoritative version of how much is outstanding is the ONLY version. > That makes a more atomic, auditable process. Multiple incongruent > representations of the same data leads to the kind of mess seen in > early versions of Koha fines. > > -- Michael Hafen Systems Analyst and Programmer Washington County School District Utah, USA for Koha checkout http://development.washk12.org/gitweb/ or git://development.washk12.org/koha _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
