This is just as much a db/programming question as an accounting/math question, so I'm actually posting to all relevant boards. I appreciate your patience in advance.
Basically I'm having trouble wrapping my head around a payments ledger. My system has three tables: CHARGES, PAYMENTS and a PAYMENTS_TO_CHARGES. The issue is that part of the system requirement calls for both the ability to directly associate a payment with a CHARGE as well as allow members to post overpayments (or credits) to their account. So there's the need to track credits then apply them as needed to future CHARGES.
Perhaps I'm thinking about things from the wrong standpoint, but I'm picturing CHARGES as a DEBIT and PAYMENTS as a CREDIT. However, it would appear that they can both have DEBIT/CREDIT depending on your accounting approach.
So, my question -- officially -- is what is the best way to go about tracking/reconciling payments, and charges, while still allowing for overpayments, etc. I'm trying to work out the DB tables and business logic but every time I solve one problem, a reverse (or converse) item presents itself. Do I allow the many-to-many relationship to track positive and negative numbers per transaction? Do I create a fake universal charge that all credits apply to? Do I create a completely separate CREDITS table? As you can tell, I'm likely overthinking this, but I'm certainly befuddled.
If all transactions were guaranteed 1-to-1, it would obviously be much simpler. Unfortunately they're not