RFC: Modification to Fines System
Here is my problem, and my proposed solution. One of our libraries is requesting a breakdown of payments by day, by account type. That is, they want a report the gives the total amount paid for Late Fees, for Damaged Books, and for Lost Books. Apparently, they have separate money accounts for each. In Koha as it stands, there is no direct way to connect a payment to a particular accountline that was paid. The least invasive idea I have is to add a new column to accountlines ( say, accountno_paid ) for payments. Each payment would then refer to the fine or charge that it was paid against. The largest difference, is that instead of one large payment that runs down the amountoutstanding field for a number of charges, the total payment would need to be broken down into a series of smaller payments, one for each accountno paid on. For example, suppose that a borrower has 3 items with and overdue fine of $5.00 each, and the borrower is paying in full. The current version would create a payment line of $15, and then set the amountoutstanding to $0.00 for each of the 3 accountno's. The revamped system would still set the amountoutstanding to $0.00 for each accountno, but would create three separate payment lines, one for each of the 3 accountno's, and the accountno paid on would be stored in accountno_paid. I don't think this would break any existing reports or functionality. Does this sound like a reasonable solution? Or have I missed something that may solve my problem without code? Thanks, Kyle http://www.kylehall.info Information Technology Crawford County Federated Library System ( http://www.ccfls.org )
I studied (and ranted) on fines several times previously. You have the right idea. One important thing to realize is that you need a primary key in accountlines. There is no way for the table to refer back to itself without it. I also think that in the proposed structure, maybe "amountoutstanding" should go away completely, since the data will be represented by the sum of the line and any payments against that line. Ideally, we also create an "accountlines_paid" table where fine lines that have been paid completely and ALL their associated payment lines are copied over, such that accountlines only holds unpaid, current fines. hah. The main impediment is that these revisions are non-trivial, and legacy data cannot be reliably "upgraded". So the restructuring would have to be timed and coordinated appropriately. --Joe On Wed, Sep 30, 2009 at 10:03 AM, Kyle Hall <kyle.m.hall@gmail.com> wrote:
Here is my problem, and my proposed solution.
One of our libraries is requesting a breakdown of payments by day, by account type. That is, they want a report the gives the total amount paid for Late Fees, for Damaged Books, and for Lost Books. Apparently, they have separate money accounts for each.
In Koha as it stands, there is no direct way to connect a payment to a particular accountline that was paid.
The least invasive idea I have is to add a new column to accountlines ( say, accountno_paid ) for payments. Each payment would then refer to the fine or charge that it was paid against.
The largest difference, is that instead of one large payment that runs down the amountoutstanding field for a number of charges, the total payment would need to be broken down into a series of smaller payments, one for each accountno paid on.
For example, suppose that a borrower has 3 items with and overdue fine of $5.00 each, and the borrower is paying in full. The current version would create a payment line of $15, and then set the amountoutstanding to $0.00 for each of the 3 accountno's. The revamped system would still set the amountoutstanding to $0.00 for each accountno, but would create three separate payment lines, one for each of the 3 accountno's, and the accountno paid on would be stored in accountno_paid.
I don't think this would break any existing reports or functionality.
Does this sound like a reasonable solution? Or have I missed something that may solve my problem without code?
Thanks, Kyle
http://www.kylehall.info Information Technology Crawford County Federated Library System ( http://www.ccfls.org ) _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
I studied (and ranted) on fines several times previously. You have the right idea. One important thing to realize is that you need a primary key in accountlines. There is no way for the table to refer back to itself without it.
I always thought of accountno as the primary key to the table, though it is not marked as such. Are there cases where accountno is used more than once?
I also think that in the proposed structure, maybe "amountoutstanding" should go away completely, since the data will be represented by the sum of the line and any payments against that line.
I vote to keep amountoutstanding, it has it's uses. Our previous ILS, as well as Koha, support incremental payments ( C4::Accounts::recordpayment, had to add a new field and script to make use of it. ) Many of our patrons will pay a portion of their fines each visit. So, in the case that a borrower had a single $10 fine and made two $5 payments a week apart, there would just be two payment lines with an accountno_paid line that refers to the original $10 fine. The upside is there is no work needed to leave it in.
Ideally, we also create an "accountlines_paid" table where fine lines that have been paid completely and ALL their associated payment lines are copied over, such that accountlines only holds unpaid, current fines. hah.
Sounds good.
The main impediment is that these revisions are non-trivial, and legacy data cannot be reliably "upgraded". So the restructuring would have to be timed and coordinated appropriately.
Indeed. As a programmer, my instinct has always been to rewrite a system from scratch, using my understanding of the current system to write a completely revamped and improved system. However, in a case like this, I cannot see a good upgrade path, unless we kept both the old, and new fine systems simultaneously. I cannot imagine the mess that would cause. That is why I think this is a good start. It adds new possibilities while not breaking anything. Kyle
On Wed, Sep 30, 2009 at 10:03 AM, Kyle Hall <kyle.m.hall@gmail.com> wrote:
Here is my problem, and my proposed solution.
One of our libraries is requesting a breakdown of payments by day, by account type. That is, they want a report the gives the total amount paid for Late Fees, for Damaged Books, and for Lost Books. Apparently, they have separate money accounts for each.
In Koha as it stands, there is no direct way to connect a payment to a particular accountline that was paid.
The least invasive idea I have is to add a new column to accountlines ( say, accountno_paid ) for payments. Each payment would then refer to the fine or charge that it was paid against.
The largest difference, is that instead of one large payment that runs down the amountoutstanding field for a number of charges, the total payment would need to be broken down into a series of smaller payments, one for each accountno paid on.
For example, suppose that a borrower has 3 items with and overdue fine of $5.00 each, and the borrower is paying in full. The current version would create a payment line of $15, and then set the amountoutstanding to $0.00 for each of the 3 accountno's. The revamped system would still set the amountoutstanding to $0.00 for each accountno, but would create three separate payment lines, one for each of the 3 accountno's, and the accountno paid on would be stored in accountno_paid.
I don't think this would break any existing reports or functionality.
Does this sound like a reasonable solution? Or have I missed something that may solve my problem without code?
Thanks, Kyle
http://www.kylehall.info Information Technology Crawford County Federated Library System ( http://www.ccfls.org ) _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
I can't find the correspondence or the bug report right now, of course, but a week or two ago there was some discussion of separating the fines display into stuff that's currently outstanding and stuff that's been paid. That would seem to tie in nicely to the comment:
Ideally, we also create an "accountlines_paid" table where fine lines that have been paid completely and ALL their associated payment lines are copied over, such that accountlines only holds unpaid, current fines. hah.
Jane Wagner Library Systems Analyst PTFS Inc. Content Management and Library Solutions 6400 Goldsboro Road, Suite 200 Bethesda, MD 20817 (301) 654-8088 x 151 jwagner@ptfs.com -----Original Message----- From: koha-devel-bounces@lists.koha.org [mailto:koha-devel-bounces@lists.koha.org] On Behalf Of Kyle Hall Sent: Wednesday, September 30, 2009 12:01 PM To: Joe Atzberger Cc: koha-devel Subject: Re: [Koha-devel] RFC: Modification to Fines System
I studied (and ranted) on fines several times previously. You have the right idea. One important thing to realize is that you need a primary key in accountlines. There is no way for the table to refer back to itself without it.
I always thought of accountno as the primary key to the table, though it is not marked as such. Are there cases where accountno is used more than once?
I also think that in the proposed structure, maybe "amountoutstanding" should go away completely, since the data will be represented by the sum of the line and any payments against that line.
I vote to keep amountoutstanding, it has it's uses. Our previous ILS, as well as Koha, support incremental payments ( C4::Accounts::recordpayment, had to add a new field and script to make use of it. ) Many of our patrons will pay a portion of their fines each visit. So, in the case that a borrower had a single $10 fine and made two $5 payments a week apart, there would just be two payment lines with an accountno_paid line that refers to the original $10 fine. The upside is there is no work needed to leave it in.
Ideally, we also create an "accountlines_paid" table where fine lines that have been paid completely and ALL their associated payment lines are copied over, such that accountlines only holds unpaid, current fines. hah.
Sounds good.
The main impediment is that these revisions are non-trivial, and legacy data cannot be reliably "upgraded". So the restructuring would have to be timed and coordinated appropriately.
Indeed. As a programmer, my instinct has always been to rewrite a system from scratch, using my understanding of the current system to write a completely revamped and improved system. However, in a case like this, I cannot see a good upgrade path, unless we kept both the old, and new fine systems simultaneously. I cannot imagine the mess that would cause. That is why I think this is a good start. It adds new possibilities while not breaking anything. Kyle
On Wed, Sep 30, 2009 at 10:03 AM, Kyle Hall <kyle.m.hall@gmail.com> wrote:
Here is my problem, and my proposed solution.
One of our libraries is requesting a breakdown of payments by day, by account type. That is, they want a report the gives the total amount paid for Late Fees, for Damaged Books, and for Lost Books. Apparently, they have separate money accounts for each.
In Koha as it stands, there is no direct way to connect a payment to a particular accountline that was paid.
The least invasive idea I have is to add a new column to accountlines ( say, accountno_paid ) for payments. Each payment would then refer to the fine or charge that it was paid against.
The largest difference, is that instead of one large payment that runs down the amountoutstanding field for a number of charges, the total payment would need to be broken down into a series of smaller payments, one for each accountno paid on.
For example, suppose that a borrower has 3 items with and overdue fine of $5.00 each, and the borrower is paying in full. The current version would create a payment line of $15, and then set the amountoutstanding to $0.00 for each of the 3 accountno's. The revamped system would still set the amountoutstanding to $0.00 for each accountno, but would create three separate payment lines, one for each of the 3 accountno's, and the accountno paid on would be stored in accountno_paid.
I don't think this would break any existing reports or functionality.
Does this sound like a reasonable solution? Or have I missed something that may solve my problem without code?
Thanks, Kyle
http://www.kylehall.info Information Technology Crawford County Federated Library System ( http://www.ccfls.org ) _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha.org http://lists.koha.org/mailman/listinfo/koha-devel
On Wed, Sep 30, 2009 at 12:00 PM, Kyle Hall <kyle.m.hall@gmail.com> wrote:
I studied (and ranted) on fines several times previously. You have the right idea. One important thing to realize is that you need a primary key in accountlines. There is no way for the table to refer back to itself without it.
I always thought of accountno as the primary key to the table, though it is not marked as such. Are there cases where accountno is used more than once?
The accountno is smallint(6) with no constraints. It is not guaranteed unique, and it is not even indexed! It cannot be used as a substitute for a true primary key.
I also think that in the proposed structure, maybe "amountoutstanding" should go away completely, since the data will be represented by the sum of the line and any payments against that line.
I vote to keep amountoutstanding, it has it's uses. Our previous ILS, as well as Koha, support incremental payments ( C4::Accounts::recordpayment, had to add a new field and script to make use of it. )
This is not dependent on the data representation having an amountoutstanding field. The same information would be represented by combining the amount of a fine with any payment lines that reference it. In fact, that will be the authoritative version of the amount still owed, right? 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.
Many of our patrons will pay a portion of their fines each visit. So, in the case that a borrower had a single $10 fine and made two $5 payments a week apart, there would just be two payment lines with an accountno_paid line that refers to the original $10 fine. The upside is there is no work needed to leave it in.
It sounds here like you are mostly agreeing with me. I'm not saying get rid of amountoutstanding features, just make sure we pick one consistent data model.
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
Missed an edge case in my idea. If a payment covers all on one fine, but not all of two fines. I was looking at the accountoffsets table ( had to make sure it still exists and is in use ), and realized what the amount column there is for. So have the same column, for the same reason, in the tracking table. It's not as simple an idea now, but it could still work. On Wed, 2009-09-30 at 12:55 -0600, Michael Hafen wrote: [snip]
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.
-- 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
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
On Wed, 2009-09-30 at 15:37 -0400, Kyle Hall wrote:
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.
I'm thinking about this a little more, and there wouldn't have to be separate tables. The point, as Joe said, is that the table(s) aren't updated; only inserted into.
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.
Foreign keys are always good, and would be applicable in any case I think.
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.
The accountoffsets table is the tracking table. I realize now that what I've described isn't that different from what we have now. Drop the amountoutstanding column and add a key column, and you have it basically. The other difference is how to handle odd payments, which is where we began. The question of splitting the payment to better match the fines payed is a good one. Not splitting the payment represents what the patron did; they brought in money and gave it to the library. Splitting the payment represents what the library did; they took the money and applied it to fines the patron had. The difference is a matter of accounting practices, which I don't know that well. For all I know an accountant would want both done. Record receipt of the whole sum, and have a separate table where the credit is split and applied against fines.
Kyle
-- 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
I just thought of a reasonably easy way to track both the payment made by a borrower, and the payment as applied to charges on a borrowers account. Let's start with the three $5 fine scenario again, which the borrower is paying in full. 1. A payment of $15 is made, it is inserted with a new accounttype ( say BORPMNT ). 2. This payment is broken down into 3 $5 payments, each of which reference one of the existing charges. 3. Each of these payments also reference the $15 payment through a new column, say accountno_pmnt The BORPMNT could either live in the existing accountlines table, or could be stored in yet another table. The real question is: is this necessary? Do any libraries using Koha need to track total payments against individual fine payments? Kyle http://www.kylehall.info Information Technology Crawford County Federated Library System ( http://www.ccfls.org ) On Wed, Sep 30, 2009 at 4:03 PM, Michael Hafen <mdhafen@tech.washk12.org> wrote:
On Wed, 2009-09-30 at 15:37 -0400, Kyle Hall wrote:
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.
I'm thinking about this a little more, and there wouldn't have to be separate tables. The point, as Joe said, is that the table(s) aren't updated; only inserted into.
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.
Foreign keys are always good, and would be applicable in any case I think.
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.
The accountoffsets table is the tracking table. I realize now that what I've described isn't that different from what we have now. Drop the amountoutstanding column and add a key column, and you have it basically.
The other difference is how to handle odd payments, which is where we began.
The question of splitting the payment to better match the fines payed is a good one. Not splitting the payment represents what the patron did; they brought in money and gave it to the library. Splitting the payment represents what the library did; they took the money and applied it to fines the patron had.
The difference is a matter of accounting practices, which I don't know that well. For all I know an accountant would want both done. Record receipt of the whole sum, and have a separate table where the credit is split and applied against fines.
Kyle
-- 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
Dear Kyle Hall, On Thu, 1 Oct 2009 08:47:52 -0400, Kyle Hall <kyle.m.hall@gmail.com> wrote:
I just thought of a reasonably easy way to track both the payment made by a borrower, and the payment as applied to charges on a borrowers account. [...] The real question is: is this necessary? Do any libraries using Koha need to track total payments against individual fine payments?
I don't know if libraries needs to track payments in this way. In Italy, as standard, we don't use fines. We put days of embargo on user or we ask to pay the value of book if heavily damaged or lost. But if you do the work, read the MySql documentation here: http://wiki.koha.org/doku.php?id=en:development:dbschema http://www.koha.it/local--files/working/koha303_schema.zip I'm sure that there are errors, and to have a prefect idea on how the system work, read the code of the API. A one person previous said, there are lacks on constraints, unique keys, indexes. And please write here any error you find in the docs, I will update them. And also any update on the tables. Bye Zeno Tajoli
2009/10/2 Kyle Hall <kyle.m.hall@gmail.com>:
I just thought of a reasonably easy way to track both the payment made by a borrower, and the payment as applied to charges on a borrowers account. Let's start with the three $5 fine scenario again, which the borrower is paying in full.
1. A payment of $15 is made, it is inserted with a new accounttype ( say BORPMNT ). 2. This payment is broken down into 3 $5 payments, each of which reference one of the existing charges. 3. Each of these payments also reference the $15 payment through a new column, say accountno_pmnt
The BORPMNT could either live in the existing accountlines table, or could be stored in yet another table.
The real question is: is this necessary? Do any libraries using Koha need to track total payments against individual fine payments?
Yes, they need to be able to say to a person who queries their account, you paid $15 on this day, and it went against these 3 fines. Or paid off $15 of this lost book replacement charge. The more information they have when a borrower queries their account the easier their lives are. Chris
The accountno is smallint(6) with no constraints. It is not guaranteed unique, and it is not even indexed! It cannot be used as a substitute for a true primary key.
Is there any reason we can't change it to an int(8) and make it the primary key?
This is not dependent on the data representation having an amountoutstanding field. The same information would be represented by combining the amount of a fine with any payment lines that reference it. In fact, that will be the authoritative version of the amount still owed, right?
That is an excellent point. I hereby withdraw my support for the amountoutstanding column!
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.
Agreed.
It sounds here like you are mostly agreeing with me. I'm not saying get rid of amountoutstanding features, just make sure we pick one consistent data model.
Your argument against it makes perfect sense to me, and seems to be the optimal solution. Kyle
participants (6)
-
Chris Cormack -
Joe Atzberger -
Kyle Hall -
Michael Hafen -
tajoli -
Wagner, Jane