> 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 _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
