I'm struggling a little bit with how to design a system to keep track of bills and payments. I currently have two functioning objects (Bill and Payment), but can't settle on a way to keep track of the accounting between them.
Fundamentally, I just need to know which specific bills have been paid off and the total balance after all of the accounting. I figure that I can do this two ways:
1) Build a separate accounting table where I keep track of each transaction, mapping a specific Bill to a specific payment. This would let me easily look up in the database how much is remaining on a particular bill. The downside is that it seems like a lot of added complexity, as I need to create a new record in this table whenever a new object is created.
2) Try to just write logic to calculate on-the-fly how much is remaining on a particular Bill by looking through the whole transaction history and doing the accounting. On the plus side, this is guaranteed to always be correct, but it seems kind of wrong to continue to do the same calculation over and over to get to what should be a static value.
Has anyone faced a challenge like this in the past, and if so, how did you solve it? Is there some best practice that I'm just missing?
One table: transactions. Bills have a positive value, payments have a negative value. You can give it a column for transaction_type if you want (Invoice, Payment, Credit, Refund), and you can even use Rails STI on that column if you really feel like it. Other useful columns - number, payment_type (credit/cash/check/eft), date.
The remaining balance is just simply a sum of all the values. If the balance is negative, a credit is owed.
If you really need to apply payments to particular bills (a practice I'm not entirely sure is correct accounting) you can have a secondary table (paid_bills) that maps payments to bills, with an amount; presumably the sum of all of the paid_bills.payment_id could not be more than the payment itself.
When displaying things for users, you can always flip the sign - Show a payment as a positive number, and when a payment form submits a positive number flip it back negative.
This is the best way I have found over the years to do this while maintaining best accounting practices.