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


  • 1
    Before I answer I need to ask : Why are (do you need to) you associating payments to a specific charge rather than against the account? This just seems wrong and looks to be the source of your issue.. – Morons Apr 28 '11 at 17:07
  • 2
    @Morons: Do you have a financial background? Without distribution rules, you might as well be writing random numbers into the books. Different charges invariably end up being associated with different G/L codes; some are revenue, some are liabilities, some are subject to further processing. The accounting system must know how a payment is being applied in order for anything to make sense. The rules themselves may be totally arbitrary, or configurable, but they still have to be defined if you want to get through the next audit with all of your limbs intact. – Aaronaught Apr 28 '11 at 18:02
  • To the OP, there is so much context missing from this question that it's hard to know where to begin. Does the system allow multiple buckets per account, e.g. where you could put overpayments? How are overpayments reapplied? Do you handle NSFs? Re-bills? Do you have a control process? Do you even have a G/L to interface with? You've got the basic idea here - you definitely do want to separate charges and payments, and connect them in a separate association table, but that's just the tip of the iceberg. If you haven't got a proper spec, then you are, as you concede below, up the creek... – Aaronaught Apr 28 '11 at 18:09
  • What I'm trying to say is that the technical details of the implementation, which you are currently focusing on, mean absolutely nothing until you're able to clearly describe how the system is supposed to behave at a functional level. If the company does not have an accountant, as you say in your reply to Christopher, then I strongly suggest that you insist that they get you one to consult with. You can occasionally afford to screw up with a lot of business systems like sales and shipping and so on, but accounting is serious business. – Aaronaught Apr 28 '11 at 18:12
  • 1
    humble, that is all very well and good but we still don't know anything about the accounting requirements, just a little bit about the invoicing requirements and even smaller amount about the A/R. Are you sure that all of these reports are really obscure, and not straightforward accounting? If there's no G/L to worry about then does that mean your system is supposed to maintain it's own ledgers? Otherwise, how does the business complete its financial statements? – Aaronaught Apr 28 '11 at 18:48

The book Analysis Patterns by Martin Fowler has a comprehensive section on Accounting patterns that I have found very useful. He does take flexibility in the design a very long way, further than many would need, but you can stop at whatever level suits you.

He has also published an article with similar content. This can be found at the articles page of his site. Look for Accounting Patterns under the the Analysis Patterns heading. It is a pdf for which the direct link is http://martinfowler.com/apsupp/accounting.pdf

Very good stuff.

  • Excellent. Reading PDF now. – humble_coder Apr 28 '11 at 18:09

Ask the accountant. Trust me on that one. He will tell you exactly how to do it.

I want to add that the accounting profession has been dealing with this sort of things for centuries and they've really figured it out.

  • 1
    I'd love to. Unfortunately, this is a pre-existing application coming from a client who I'm not sure even has one. As far as we're concerned, we generally deal with linear payment formats -- nothing out of order, and pay as you go. If we could do that, it would be much simpler, but in a "the client is always right" type of situation, and without a readily referenceable Accountant, we're slightly up a creek with this one. I do agree though, thanks. – humble_coder Apr 28 '11 at 17:47
  • 1
    Accounting is based on the idea of double entry bookkeeping, which is designed around making it hard for humans to take money out of the system without generating records that will get them caught. If you trust your software, double entry bookkeeping's original purpose is not so important. But people still do it because it is what the accounting profession understands. – btilly Apr 28 '11 at 17:58
  • 2
    @humble_coder: You're writing an accounting system and don't have an accountant on staff to tell you how it should work? This will not end well. – btilly Apr 28 '11 at 18:04
  • Correction: we don't have an accountant that buys into this particular paradigm, and as such likely has no input. I will certainly make mention of inviting them to the meeting. – humble_coder Apr 28 '11 at 18:08

Based in my comment above here is how this should be laid out...

Account table:

Transaction Table
    Amount (Can be Pos or Neg)
    Type (Credit,Invoice, Payment ect)
    Reference no (Credit no,Invoice no ect )

Anytime an record is inserted into the Transaction Table you must update the AccountBalance fields. Yes I know that this De-normalization.. But should be done anyhow for performance reasons. (assuming you don't have a really small number of transactions)

In lieu of the additional info below

Charges Table:
        Amount (Can be Pos or Neg)
        Reference no (InvoiceNo ect )

Payment table:
    ChargeId (FK to the Charges table)**
    Reference no (CheckNo exct )***

If 1 payment is against 2 charges it get 2 rows in the payments table but with the same Reference No.

** I think this should be optional (Not required or have a default value such as onAccount) as you should be allowed to accept a payment and assign it to a charge later... but that is up you your business people.

***If you really want to.. you can create a Reference table as well

  • Ok, yeah, I'm a complete normalization "purist", hence a portion of my confusion. Actually the system is a large one. There are GROUPS with MEMBERS and when STATEMENTS are generated, each MEMBERS' account is charged with the relevant charges. For the purposes of allowing the user to see which specific "non-linear" charges remain, everything needs to be tracked w/ relationships. For example, if (for some strange reason) the user wants to apply payment to MARCH's DUES to get a discount before paying the already late FEB DUES. It's convoluted. Unfortunately I didn't design the requirements. – humble_coder Apr 28 '11 at 17:28
  • The old system was horribly designed with the tracking of PAYMENTS to CHARGE CYCLES and GROUP CYCLES and MEMBER CYCLES in addition to a self-referencial TRANSACTIONS table. As previously mentioned, I'm not a fan of de-normalization, nor am I a fan of large groups of potentially NULL fields, etc. That said, keeping a balance isn't the problem, but making sure to keep track of which specific items are paid vs due, etc. =\ – humble_coder Apr 28 '11 at 17:30
  • So the Business rule is the payments are applied to statement? or is it still that it must be applied to a specific payment? – Morons Apr 28 '11 at 17:36
  • Well, the current system attempts to do both. I honestly wish I could show you, but unfortunately I can't. We have a meeting shortly where I will attempt to convince them that this is a bad idea. That said, the main bit of importance is regarding PAYMENTS to CHARGES. They feel that the user MUST be allowed to choose where they want to apply their payments. Another problem is dealing with the occasional user that wants to pad their account with $$$ before the next billing cycle.... – humble_coder Apr 28 '11 at 17:41
  • ...This completely throws off management of actual charge amount vs the remainder due as (with the current accounting system) it potentially eliminates certain charges from posting properly due to miscalculation of "credits". – humble_coder Apr 28 '11 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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