0

I'm building a relational database to model double entry accounting.

Right now it's such that any attempt to enter an unbalanced transaction (where dr != cr) will be rejected; with this restriction, I'm wondering how to handle transfers between bank accounts where a transaction may remain unbalanced for several days until the money shows up in the account at the receiving institution.

Based on an old model I used to track my own finances, what I have is an asset T account called "Transfer" which acts as a go-between from bank account to bank account. This way, when money is sent, a balanced transaction can be entered between the bank account and the Transfer T account, and likewise when the money is received.

Is that a reasonable solution? Or should I remove the restriction of entering unbalanced transactions? Ultimately I'll build an application around this database, which could notify the user of any unbalanced transactions, explaining that transfers will be unbalanced until the money has been received at the other bank, or asking the user to complete the transaction by entering the corresponding debit or credit entry.

One benefit of the "Transfer" T account is that any non-zero balance indicates pending Transfers, but this could also be calculated with a database query in the "unbalanced" scenario.

But I'm hoping for less of a technical implementation answer, and more of a standard practice / best practice answer from people very familiar with double entry accounting. And perhaps any insight into the implications of each solution. For example, is it "bad" to have temporarily unbalanced transactions in the ledger?

Edit: To clarify, when the transaction appears in one account I understand I could immediately debit and credit both involved accounts. The "Transfer" account allows me to avoid having to detect the duplicate transaction once it appeared at the receiving institution. It also saves me from having to find a way to automatically detect what the destination account is.

1
  • Thanks everyone, lots of great information here that will be a big help. I chose base64's answer in particular because I found the first sentence illuminating. – Matt Oct 9 '20 at 3:22
2

A ledger must be balanced. Period.

While your idea of a temporary account to represent Cash-in-Transit or Deposit-in-Transit is an innovative idea, it is not necessary in the context of accounting principle.

Once the money left Account A, it is destined to arrive at Account B.

The question that you should ask yourself is:

What are the chances that the money is lost in transit?

What ability does the bank at Account A have to revoke/reverse the transfer?

Which account has eventual control of the money?

Ledger balance never matches statement balance due to checks and ACH delay. That is the whole of point of Bank Reconciliation, which is a processed product of the ledger. It involves reconciling the difference between the balance listed in the printed statement of the bank and the ledger. Bank Reconciliation is used to "explain" the difference.

Consider an example where money transmitters/remitters experience delay in ACH: https://www.osbckansas.org/mt/guidance/mt2019_01_cash_in_transit.pdf

1
  • 3
    Temporary accounts aren't particularly innovative, clearing and suspense accounts are commonly used temporary accounts. – Hart CO Oct 8 '20 at 17:34
2

From an accounting perspective, you would normally ignore any lags in money actually showing up and being spendable in an account just as you ignore the fact that that checks you write may not be cashed for some time. There may be a separate function if you want to reconcile what the accounting statements show as the balances in certain accounts against what the bank shows those balances to be (for example, you may want to proactively re-issue refund checks if a customer hasn't cashed it after a certain period of time).

So normally you'd just debit one bank's account and credit the other, ignore the time lag from an accounting perspective, have balanced transactions, and omit the transfer account. Just like you'd have a balanced transaction when you issue a check to a vendor to pay for the fish they delivered and ignore the fact that they may deposit the check today or 3 weeks from today.

There are subtleties if you are doing cash accounting (what individuals do) and accrual accounting (what virtually every company does) in how you actually account for certain transactions. But in either regime, your accounting balances aren't going to exactly match your bank balances at any point in time.

5
  • 1
    I was avoiding doing the debit on one bank and the credit on the other because at some point the user will import their data from the other account and I'd have to detect and ignore the duplicate transaction. Sounds like I might just have to deal with that. – Matt Oct 8 '20 at 15:45
  • I'm sure what seems normal depends on the type of accounting you do, but many businesses do not ignore lags in bank transfers, they leverage clearing accounts to handle the lag since proper cash balance reporting is often important. – Hart CO Oct 8 '20 at 17:40
  • @HartCO Those clearing accounts will filter up to cash balances for reporting - I have never seen a company which chose to omit cash on a balance sheet because of a time lag, and am not aware of an accounting system which would allow that. For example - if I write a cheque to you, I should Cr my bank account immediately, even if you don't cash it until next week. To do otherwise lends itself to a form of accounting fraud known as 'cheque kiting', where I show the cash in my account on one side with cheque not having cleared yet, and another party records the anticipated receipt on their side. – Grade 'Eh' Bacon Oct 8 '20 at 18:57
  • @Grade'Eh'Bacon For transfers between bank accounts within a company, a clearing account is used to keep actual bank balance closely aligned with your accounting system. It's not omitting cash on a balance sheet (the clearing account totals to Cash in this case), it's accurately representing how much cash you have in each bank account. – Hart CO Oct 8 '20 at 19:15
  • @HartCO From a reporting perspective, that wouldn't have an impact given presentation of the statements would only show consolidated balances, and internal accounts will naturally have bank recs showing differences month to month, of which intra-company transfers likely form a trivial portion. I'm not aware of this being common practice anywhere. – Grade 'Eh' Bacon Oct 8 '20 at 19:35
1

I'll answer from a "personal finance" standpoint since Accounting rules are different and off-topic.

Any personal finance software I've used to track bank accounts has some notion of "cleared" transactions. So while the money may not be in your second account yet, if you put the transfer there and flag it as "uncleared" you should still be able to reconcile your bank account and not have to deal with the "limbo" in separate accounts.

As a user of these programs, this make much more sense to me that using a "limbo" account that I then have to clear out once transactions clear.

Bank account reconciliation is typically separate from double-entry accounting since income and expense do mot match 1:1 with cash transactions, and may not impact the financials at all. A bank transfer of cash does not impact the financial statements, and there are many non-cash transactions that do not impact bank accounts.

1
  • 1
    You wouldn't have to clear the Transfer account. That would be automatic when importing the transactions from the receiving institution, once the deposit has appeared. – Matt Oct 8 '20 at 15:47
1

I moved from Quicken (not double entry) to GnuCash (double entry), and the main reason I did so was the lack of the forced double entry in Quicken. Not having double entry came back to bite me so many times that I pulled out a lot of hair which never grew back. The way GnuCash handles it is to force an entry into the "Imbalance" account. It's simple, in that regardless of the type of transaction, there is one place it will always go. At any time you can bring up the "Imbalance" account and see everything in your books that is currently imbalanced. I greatly prefer this from the point of view of the user.

Disclaimer: the personal finance portion of this answer has now ended, and the software engineering portion has now begun.

As an aside, there are plenty of ways to skin this cat, and I'm not sure that forcing this at the database level is the best one. This is akin to the debate of whether or not to have foreign keys in a relational database. In the last 10-15 years more DBAs are starting to lean towards removing FKs, and letting the application/API layer control the constraints rather than the DB. In my opinion this is happening due to the natural progression of migrating big data sets from relational to NoSQL databases, since the shape of future data is oftentimes unknown, and performance is oftentimes hindered by FKs in large DBs. Perhaps the data shape is extremely well defined in an accounting database and that isn't relevant. That being said, your question is pretty much the same whether you put this constraint in the DB, or in the API layer. You can decide where to put it, and let's discuss "How should the constraint be handled?"

I like the flexibility of the Imbalance account if you don't yet know the target account. When you do know the target account (which you would typically know with a transfer), then some sort of pending flag may be useful at the transaction level. This may offer more flexibility than a "Transfer" account, since any kind of transaction can then be pending, for a variety of reasons. (The check is in the mail, for example.) So, perhaps any transaction, which has (at least!) two accounts linked to it, could be marked pending to indicate that you don't know where that money is. It could be in one account, or both (or more if a split), or neither until it clears. But the norm is that the "current state" will always show balances as if the transaction is already cleared, because you have to show something.

As an example, it isn't uncommon for a user to set up a transfer to a new account that doesn't exist yet, and then create the new account after the money clears. So in that scenario you'd have the target account use the Imbalance account, with the pending flag enabled. Pending can be removed first, or Imbalance can be replaced with the new account first; the order wouldn't matter.

2
  • 1
    Thanks for sharing your experience with Quicken, it's informative for me. Part of this exercise is an expression of my interest in RDBMS, I'm a big fan of FK and constraints for example - you might be aghast if you knew the extent of the work I chose to do on the DB rather than the backend where it's typically done! Admittedly, it's tricky with balanced transactions, because it means the user can't save their data until it's exactly correct; I'm doing a lot of thinking on my choices with that constraint. This is the sort of intellectual exercise I'm not getting out of my career. – Matt Oct 8 '20 at 16:10
  • 1
    @Matt Hehe. I can relate. I love FKs and other constraints, and I use them a lot. But I also spend a lot of time in a 30TB SQL Server DB with FKs and complex indexes, and sometimes they can cause issues. So I guess I have a love hate relationship with DB constraints in general. As with most things, "there's a time and a place". :) – TTT Oct 8 '20 at 16:18

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.