[Koha-devel] RFC: Modification to Fines System

[Kyle Hall]

> 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