4

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
  • 193
  • 1
  • 8
  • 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
  • The concept of the system as it stands is an arguably simple one. The only issue is the client's desire to track any and everything without regard for simplicity. That was the failure of the original implementation (the one before I was brought onboard). My background is in software and DB design, but that inherently requires an understanding of the relationships between the entities with which you're dealing. And that's where my brain falls apart. – humble_coder Apr 28 '11 at 18:20
  • That said, the system is one very similar to "www.wepay.com" but with a lot more report-related requirements/features. There are PENDING GROUP/INDIVIDUAL CHARGES that get billed at later dates, there are DISCOUNTS/FEES for early/late payments, etc. As stated, the base system is fairly straightforward -- it's the need for obscure reporting that is a major part of the issue. There's no GL to worry about, per se, nor are there NSFs involved. Re-bills do occur but only in the form of statements and have no bearing on the amount due other than an initial LATE FEE past an admin-specified DUE DATE. – humble_coder Apr 28 '11 at 18:21
  • I agree completely with @aaronaught: Without knowing what kind of business you're doing, there isn't even any useful advice I can give, and I've been doing this stuff for a long time. – Satanicpuppy Apr 28 '11 at 18:43
  • 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
  • @Satanicpuppy The business is literally like that of WePay -- sort of a dues collector (with the ability to ad manual charges). The user creates a group (whether a "bridge club", or a "frat" or, "flower enthusiasts". Members can then subscribe to their group and pay dues based on the recurring charges entered by the group's administrator. – humble_coder Apr 28 '11 at 19:12
  • @Aaronaught The "ledger" is currently caught in a nebulous realm between "hopefully we're tracking payments-to-charges properly" and "on-the-fly" calculations. It's bad -- very bad. A/R is literally strictly a matter of what has been billed (or late) -- no more, no less. As for the client's personal business, I'm not sure what their goal is every April 15. Based on the poor construction of the system (and since 99.9% of the charges are strictly dues/service related), I'd say it's simply a matter of net gain/loss with very little detail otherwise. – humble_coder Apr 28 '11 at 19:18
  • I'll obviously get a lot more information on their goals (and general intentions) in the upcoming meeting. I've only been on the project "cleanup crew" for a couple of weeks. I'll try to post with more info as soon as I have it. Thanks so far for your input, everyone! – humble_coder Apr 28 '11 at 19:25
  • Fair enough. I hope for your sake that you haven't been chained to a time bomb; sadly, I've seen these half-assed financial systems far too many times, usually from small businesses who've contracted to inexperienced or incompetent developers because *it's so simple, right?* Sometimes they scrape by, but if I were you, I wouldn't wait too long to collect my paycheck. ;) – Aaronaught Apr 28 '11 at 19:58
  • Ok, it would indeed appear that I am strapped to a "time bomb". That said, the client did give the go ahead to restructure the database while they speak with their accountant. I told them that that was backwards, but they want "progress" nonetheless (*smacks forehead*). They also want the ability now to handle refunds. Anyway, just a small update. I'll continue to post relevant data -- if any presents itself that is. *smh* In the meantime, if any epiphanies (other than "ruuuuuuuuuuuuuun!") present themselves, feel free to post. Thanks again. – humble_coder May 03 '11 at 17:43

3 Answers3

6

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,151
  • 3
  • 32
  • 35
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,404
  • 19
  • 22
  • 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
-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,674
  • 4
  • 37
  • 73
  • 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
  • Yeah, I was thinking of creating a PAYMENTS_TO_CHARGES reference table that would handle all of that. The only problem then is when overpayments arrive. I would almost need another table in which to throw in credits. This would also allow then to keep the initial CHARGE and PAYMENT amounts in tact for future reference purposes without breaking them down from their initial form by muddling them with "on-they-fly" credits, etc. I don't know, honestly, I think the *ultimate* solution is to get them to simplify their requirements. Thanks for your input btw! – humble_coder Apr 28 '11 at 17:53
  • if you follow my suggestion By making the ChargeId on the payments table Optional you don't have this issue. all payments or credits are on the payments table. If a Charge is associated to the payment it is filled in.. if not its "on account" – Morons Apr 28 '11 at 18:01
  • If you were going to pick one thing to denormalize, why would you pick money? There is effectively no benefit to having the total stored, because 9 times out of 10, the customer is immediately going to ask for a breakdown of charges. Also, very scary to have your cash stored in two places, even if the "total" number isn't meant to be authoritative. – Satanicpuppy Apr 28 '11 at 18:48
  • Just for performance... There can easily be Millions upon Millions of transactions spanning back 30 years.. Suppose you wanted to archive some of that? then what? Also People only ask for there recent transaction.. not all. – Morons Apr 28 '11 at 18:58
  • This is a very poor design. Aside from the obvious conceptual problem of putting two records in a "Payments" table that represent the exact same payment, a *reference number* is one of the worst key choices imaginable because (a) there won't always be one and (b) they're usually going to get manually entered and are thus prone to errors. And there's no rational reason for this oversimplification; it does *not* improve performance (think of all the `DISTINCT` queries you'll be doing) and even if it did, there's no reason to believe that performance is particularly important here. – Aaronaught Apr 28 '11 at 19:01
  • Morons, if people only ask for recent transactions then you put an index on the date column - as any sane DBA would do right off the bat without even giving it a second thought. What's bad for *performance* is having duplicate data in a transaction table; put that in an association table and 90% of the time you don't even have to look at it. – Aaronaught Apr 28 '11 at 19:03
  • @morons: Generally transactions are grouped into "active" where money is owed to someone, and "archived" where all money is accounted for. Generally the "active" table will be small, yet important, and the archive table, as you'd expect, will likely get offloaded to inactive storage after 5 years or so. We keep about 8 years, but its mostly out of laziness. Revenue forecasting in my industry is hard to do even month to month. – Satanicpuppy Apr 28 '11 at 19:32
  • No Joke, you guys have clearly never worked on such a systems, And have no idea what you are talking about. That being said, yes i am simplifying, this is an example. – Morons Apr 28 '11 at 20:11
  • @Morons: As much as I respect you, this time I'm with the other people here. Most businesses, most of the time, have (as far as a computer is concerned) fairly trivial numbers of transactions. An index on (account, date) will make most common queries sufficiently fast. On the flip side many databases (eg Oracle) will by default leave you with a difficult to find race condition on your update of AccountBalance. That means that, unless performance has been shown to be a problem, it would be premature to optimize for it here. – btilly Apr 29 '11 at 00:16