Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

See also:

http://programmers.stackexchange.com/questions/72178/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system

and

http://stackoverflow.com/questions/5543025/need-to-reconcile-accounts-on-a-poorly-designed-system

share|improve this question
    
I liked the comment made by someone on P.SE :: "ask the accountant how it should be" –  jcolebrand Apr 29 '11 at 5:22

2 Answers 2

up vote 3 down vote accepted

So, my question -- officially -- is what is the best way to go about tracking/reconciling payments, and charges, while still allowing for overpayments, etc.

This is the problem. There are several styles of accounting. Are you using double-entry? I would recommend learning double-entry accounting. It's complicated but it works.

Is this for an actual assignment or for learning?

Here's a system that may be easy enough to dig into to give you the concepts you need to move forward? I know the author (by way of this podcast but not personally) but I haven't looked at the codebase. I just know that it does DE accounting.

http://twit.tv/floss150

share|improve this answer
    
This is an actual project. Thanks. –  humble_coder May 3 '11 at 17:59

A system I worked with had the following four tables (among others).

  • Account
  • Invoice
  • Transaction
  • TransactionApplication

Transactions could be charges, payments, refunds, chargebacks, credits &c; positive values represent debts of the related account to us, negative amounts represent credits or other reductions of debt. Invoices denoted periodic summary information to customers. Transactions were (sometimes partially) applied against invoices.


share|improve this answer
    
Well, in this case "invoices" are not true entities per se -- simply statements of current charges due. Payments are applied on the individual charge level, which could certainly be extrapolated to the statement level, but it's not "interesting" data to be tracked as it's not relevant. That said, the new system must have some mechanism by which to directly correlate payments to charges. –  humble_coder May 29 '11 at 15:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.