sql - Relational Data Model for Double-Entry Accounting

Question

Assume there is a bank, a large shop, etc, that wants the accounting to be done correctly, for both internal accounts, and keeping track of customer accounts. Rather than implementing that which satisfies the current simple and narrow requirement, which would a 'home brew': those turn out to be a temporary crutch for the current simple requirement, and difficult or impossible to extend when new requirements come it.

As I understand it, Double-Entry Accounting is a method that is well-established, and serves all Accounting and Audit requirements, including those that are not contemplated at the current moment. If that is implemented, it would:

• eliminate the incremental enhancements that would occur over time, and the expense,
• there will not be a need for future enhancement.

I have studied this Answer to another question: Derived account balance vs stored account balance for a simple bank account?, it provides good information, for internal Accounts. A data model is required, so that one can understand the entities; their interaction; their relations, and @PerformanceDBA has given that. This model is taken from that Answer:

Whereas that is satisfactory for simple internal accounts, I need to see a data model that provides the full Double-Entry Accounting method.

The articles are need to be added are Journal; internal vs external Transactions; etc..

Ideally I would like to see what those double entry rows look like in database terms, what the whole process will look like in SQL, which entities are affected in each case, etc. Cases like:

1. A Client deposits cash to his account
2. The Bank charges fees once a month to all Clients accounts (sample batch job),
3. A Client does some operation over the counter, and the Bank charges a fee (cash withdrawal + withdrawal fee),
4. Mary sends some money from her account, to John's account, which is in the same bank

Let's just call it System instead of Bank, Bank may be too complex to model, and let the question be about imaginary system which operates with accounts and assets. Customers perform a set of operations with system (deposits, withdrawals, fee for latter, batch fees), and with each other (transfer).

Answer 1

A. Preliminary

Your Approach

First and foremost, I must commend your attitude. It is rare to find someone who not only thinks and works from a solid grounding, and who wishes to understand and implement a Double-Entry Accounting system, instead of:

• either not implementing DEA, thus suffering multiple re-writes, and pain at each increment, each new requirement,

• or implementing DEA, but re-inventing the wheel from scratch, by figuring it out for oneself, and suffering the pain at each exposure of error, and the demanded bug fixes, a sequence that never ends.

To avoid all that, and to seek the standard Method, is highly commended.

Further, you want that in the form of a Relational data model, you are not enslaved by the Date; Darwen; Fagin; et al views that prescribes a Record ID based Record Filing Systems, that cripples both the modelling exercise and the resulting "database". These days, some people are obsessed with primitive RFS and suppress Dr E F Codd's Relational Model.

1. Approach for the Answer

If you do not mind, I will explain things from the top, in logical order, so that I can avoid repeats, rather than just answering your particular requests. I apologise if you have complete knowledge of any of these points.

Obstacle

Ideally I would like to see what those double entry rows look like in database terms

That is an obstacle to the proper approach that is required for modelling or defining anything.

• In the same way that stamping an ID field on every file, and making it the "key", cripples the modelling exercise, because it prevents analysis of the data (what the thing that the data represents actually is), expecting two rows for a Credit/Debit pair at the start will cripple the understanding of what the thing is; what the accounting actions are; what effect those actions have; and most important, how the data will be modelled. Particularly when one is learning.

^{Aristotle teaches us that:}

^{the least initial deviation from the truth is multiplied later a thousandfold ... a principle is great, rather in power, than in extent; hence that which was small [mistake] at the start turns out a giant [mistake] at the end.}

Paraphrased as, a small mistake at the beginning (eg. principles; definitions) turns out to be a large mistake at the end.

Therefore the intellectual requirement, the first thing, is to clear your mind regarding what it will be at the end of the modelling exercise. Of course, that is also required when one is learning what it is, in accounting terms.

2. Scope for the Answer

Assume there is a bank, a large shop, etc, that wants the accounting to be done correctly, for both internal accounts, and keeping track of customer accounts.
Let's just call it System instead of Bank, Bank may be too complex to model ...
Customers perform a set of operations with system (deposits, withdrawals, fee for latter, batch fees), and with each other (transfer).

To be clear, I have determined the scope to be as follows. Please correct me if it is not:

• Not a small business with a General Ledger only, with no Customer Accounts
• But a small community Bank, with no branches (the head office is the branch)
• You want both the internal Accounts, which consists of:
• a simple General Ledger,
• as well as external Accounts, one for each Customer
• The best concept that I have in mind is a small community Bank, or a business that operates like one. An agricultural cooperative, where each farmer has an Account that he purchases against, and is billed and paid monthly, and the cooperative operates like a small bank, with a full General Ledger, and offers some simple bank facilities.
• A single Casino (not a chain) has the same requirement.
• Not a large Bank with multiple branches; various financial products; etc.
• Instead of System or Bank, I will call it House. The relevance of that will be clear later.

Anyone seeking the Double-Entry method for just the Ledger, without the external Customer Account, can glean that easily from this Answer.

In the same vein, the data model given here is easy to expand, the Ledger can be larger than the simple one given.

---

B. Solution

1. Double-Entry Accounting

1.1. Concept

To know what that it is by name; that it has great value; that it is better than a roll-your-own system, is one thing, knowing what it is deeply enough to implement it, is another.

1. First, one needs to have a decent understanding of a General Ledger, and general Accounting principles.

2. Second, understand the concept that money represents value. Value cannot be created or destroyed, it can only be moved. From one bucket in the accounts to another bucket, otherwise known as Debit (the from-account) and Credit (the to-account).

3. While it is true that the SUM( all Credits ) = SUM( all Debits ), and one can obtain such a report from a DEA system, that is not the understanding required for implementation, that is just one end result. There is more to it.

• While it is true that every transaction consists of a pair: one Credit and one Debit for the same amount, there is more to that as well.

• Each leg of the pair; the Credit and Debit, is not in the same Account or Ledger, they are in different Accounts, or Ledgers, or Accounts-and-Ledgers.

• The SUM( all Credits ) is not simple, because they are in those different places (sets). They are not in two rows in the same table (they could be, more later). Likewise, the SUM( all Debits ).

• Thus each of the two SUM()s cover quite different sets (Relational Sets), and have to be obtained first, before the two SUM()s can be compared.

1.2. Understanding Double-Entry Accounting

Before attempting a DEA implementation, we need to understand the thing that we are implementing, properly. I advise the following:

1. You are right, the first principle is to hold the perspective of the Credit/Debit Pair, when dealing with anything in the books, the General Ledger; the Customer Accounts; the bank Accounts; etc.

• This is the overarching mindset to hold, separate to whatever needs to be done in this or that Account or Ledger.

• I have positioned it at the top; left, in the data model, such that the subordination of all articles to it is rendered visually.

2. The purpose or goal of a Double-Entry Accounting system is:

• Eliminate (not just reduce) what is known as:

  • "lost" money

  • "lost" Transactions (one or the other side of the Credit/Debit pair)

  • and the time wasted in chasing it down.

  • Not only can money be found easily, but exactly what happened to it, and where it is now, can be determined quickly.

• Full Audit functionality
  It is not good enough to keep good Accounts, it is imperative for a business that accounts for other people's money, to be readily audit-able. That is, any accountant or auditor must be able to examine the books without let or hindrance.

  • This is why the first thing an outsider, eg. an auditor, wants to know is, does the SUM( all Credits ) = SUM( all Debits ). This also explains why the DEA concept is above any Accounts or accounting system that the company may be keeping.

• The great benefit, although tertiary, is that the everyday or month end tasks, such as a Trial Balance or closing the books, can be closed easily and quickly. All reports; Statements; Balance Sheets; etc, can be obtained simply (and with a single SELECT if the database is Relation).

3. Then ready the Wikipedia entry for Double-Entry Bookkeeping.

• The internet has plenty of misleading information, and Wikipedia is particularly awful that is forever changing (truth does not change, falsity changes with the weather), but sorry, that is all we have. Use it only to obtain an overview, it has no structural or logical descriptions, despite its length. Follow the links for better info.

• I do not entirely agree with the terminology in the Wikipedia article. Nevertheless, in order to avoid avoidable confusion, I will use those terms.

• There are tutorials available on the web, some better than others. These are recommended for anyone who is implementing a proper Accounting system, with or without DEA. That takes time, it is not relevant to an answer such as this, and that is why I have linked the Wikipedia article.

2. Business Transaction

Ideally I would like to see what those double entry rows looks like in database terms, what the whole process will look like in SQL, which entities are affected in each case, etc.

Ok. Let's go with the Transactions first, then build up to understanding the data model that supports them, then inspect the example rows. Any other order would be counter-productive, and cause unnecessary back-and-forth.

Your numbering. Green is House in the General Ledger, blue is external Customer Account, black is neutral.

• This is the first increment of Treatment, how a thing is treated, in different scenarios (your concern, and your request for specific examples, is precisely correct).

• Credit/Debit Pairs
  This is the first principle of DEA, understand the pair, as the pair, and nothing but the pair.

Do not worry about how the General Ledger or the Account is set up, or what the data model looks like. Think in terms of an accountant (what has to be done in the books), not in terms of a developer (what has to be done in the system).

Notice that the each leg of the pair is in the one set (the Ledger), or in two sets (one leg in the Ledger, the other leg in Account). There are no pairs in which both legs are in Account.

• Because DEA is implemented, each Business Transaction (as distinct from a database Transaction), consists of two actions, one for each Credit/Debit leg. The two actions are two entries in a paper-based account book.

1. A Client deposits cash to his account

<i