5

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

Best.

humble_coder
  • 203

3 Answers3

7

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.

Marjan Venema
  • 8,171
3

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.

Christopher Mahan
  • 3,414
  • 21
  • 22
-1

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

Account table:
    AccountID    
    AccountName
    AccountBalance

Transaction Table
    TransactionID
    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:
        ChargeID
        Amount (Can be Pos or Neg)
        Reference no (InvoiceNo ect )

Payment table:
    PaymentId
    Amount 
    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

Morons
  • 14,706