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, (f) 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 suppresses 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.}