[Koha-devel] RFC: Modification to Fines System

[Michael Hafen]

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