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

• During the DayEnd procedure, among other tasks, all cash is accounted for and checked. The day is closed. All cash sitting in HouseCash that is beyond whatever the bank deems necessary for everyday cash Transactions, is moved to HouseReserve.

2. The Bank charges fees once a month to all Clients accounts (sample batch job)

• This charges each Account with the Fee
• Fee is dependent on AccountType_Ext
• This is the simple case. If the Fee is dependent on something else, such as the number of transactions in the Account; or the CurrentBalance being below or above some limit; etc, that is not shown. I am sure you can figure that out.

3. A Client does some operation over the counter, and the Bank charges a fee (cash withdrawal + withdrawal fee),

• Simple Transactions do not incur fees, and Deposit/Withdrawal has already been given. Let's examine a business Transaction that actually attracts a fee.

• Mary sends $500 USD to her son Fred, who is travelling overseas looking for whales to save, and has run out of money. The bank charges $30 for an Overseas Bank Transfer. Fred can collect the funds (in local currency equivalent of $500 USD) at any partner bank branch.
• To actually transfer the money to the foreign bank, the House has to interact with a local big bank that provides international settlement and currency exchange services. That is not relevant to us, and not shown. In any case, all those types of Interbank transactions are batched and dealt with once per day, not once per AccountTransaction.
• In this simple DEA system, the House does not have currency accounts in the Ledger. That is easy enough to implement.

4. Mary sends some money from her account, to John's account, which is in the same bank

• The money is currently in Mary's account (deposited on a day prior to today), that is why it is in HouseReserve, not HouseCash
• The money is moved from HouseReserve into HouseCash because John may come into the bank today and withdraw it.
• As described in example [1.3] above, at the DayEnd procedure, any money sitting in HouseCash in all Accounts will be moved to HouseReserve. Not shown.

---

3. Relational Data Model • Initial

Now let's see what the data modeller has done, to support the accountant's needs, the business Transactions.

• This is of course, the second increment of Treatment, what the modeller has understood the real world business Transactions to be, expressed in Relational terms (FOPC; RM; Logic; Normalisation)

• This is not the simplest data model that is required to satisfy the restated scope.

• There are simpler models (more later), but they have problems that this one does not have, problems that are desirable, if not imperative, to avoid.

• The image is too large for in-line viewing. Open the image in a new tab, to appreciate it in full size.

3.1. Notation

• All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993.

• My IDEF1X Introduction is essential reading for those who are new to the Relational Model, or its modelling method. Note that IDEF1X models are rich in detail and precision, showing all required details, whereas home-grown models, being unaware of the imperatives of the Standard, have far less definition. Which means, the notation needs to be fully understood.

3.2. Content

• The main difference between a genuine Relational data model produced by someone else, and mine, is:
  a business Transaction (always two actions; two legs, one per Credit/Debit) is affected by a single row with two sides, one per Credit/Debit,
  in AccountTransaction or LedgerTransaction.

• Most modellers will model two rows for the Credit/Debit pair, one for each leg or side (hey, one leg is a Credit, and the other leg is a Debit, if I Normalise that, I get two rows).

• Wrong. If I tell you that Fred is Sally's father, you know, from that single Fact, that Sally is Fred's daughter.

• A FOREIGN KEY needs to be declared just once, not once for each side.

• Likewise, the Credit/Debit pair is a single Business Transaction,
  a single Atomic article, that can be perceived from either Side, like two sides of one coin. Modelled as such.

• All manner of preventable bugs are prevented, the search for the "missing" leg is eliminated.

• Even for those with sub-standard OLTP code, which causes quite preventable concurrency problems, if this method is implemented, this is one article wherein those problems will not arise.

• Further, the number of rows in the %Transaction tables is halved.

• I have arranged the articles such that the
  External Account
  Internal Ledger and LedgerTransaction
  Internal-External AccountTransaction
  are clear.

• Along with a nugget of definition from the Wikipedia entry.

• Having familiarised yourself with the DEA Credit/Debit pairs, now study the Treatment of the pair. Notice that the Treatment is different, it is based on a number of criteria (three account types; six Ledger types; etc), which in turn is based on the complexity of the General Ledger.

• This Ledger is simple, with Asset/Liability accounts only. Of course, you are free to expand that.

• The eagle-eyed will notice that AccountStatement.ClosingBalance and LedgerStatement.ClosingBalance can actually be derived, and thus (on the face of it), should not be stored. However, these are published figures, eg. the Monthly Bank Statement for each Account, and thus subject to Audit, and therefore it must be stored.

For a full treatment of that issue, including considerations; definition; treatment, refer to this Q & A:

• Derived account balance vs stored account balance for a simple bank account?

3.3. Summary

In closing this section, we should have reached this understanding:

• The overarching principle of DEA, the Credit/Debit pairs, purely intellectual

• The typical business Transactions, always a Credit/Debit pair, two legs, two entries in the accounting books

• A deeper understanding of the Treatment of said Transactions

• The environment that the House (small bank; cooperative; casino) manages (internal Ledger and external customer Account)

• A first look at a data model that is proposed to handle all that.

---

4. Relational Data Model • Full

Here it is again, with a full set of sample data.

• Re the Primary Keys:

• Note that LedgerNo and AccountNo are not surrogates, they have meaning for the organisation, in ordering and structuring the Ledger, etc. They are stable numbers, not an AUTOINCREMENT or IDENTITY or anything of the sort.

• The Primary Keys for LedgerTransaction and AccountTransaction are pure, composite Relational Keys.

• It is not a Transaction Number of some kind that is beloved of paper-based accountants.

• It is not a crippling Record ID either.

• The Alternate Keys are more meaningful to humans, hence I have used them in the examples (Business Transactions, above [2], and below [5]). This Answer is already layered, it would be a nightmare trying to relate hundreds of 1's, 2's and 3’s to each other.

• If we wish to understand what something means, we need to hold onto the meaning that exists in the thing, rather than excising the meaning by giving it a number.

• In the example data, the Primary Keys are bold.

---

5. Business Transaction with Row

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.

Now that we understand the Business Transactions, and the data model that services the requirement, we can examine the Business Transactions along with affected rows.

• Each Business Transaction, in DEA terms, has two legs, two entries in the paper-based account books, for each of the Credit/Debit pair,
  is yet a single Business Transaction, and now:
  it is affected by a single row with two sides, for each of the Credit/Debit pair.

• This is the third increment in understanding Treatment: the business Transactions; data model to implement them; and now, the affected rows

• The example database rows are prefixed with the table name in short form.
  Plus means INSERT
  Minus means DELETE
  Equal means UPDATE.

1. A Client deposits cash to his account

2. The Bank charges fees once a month to all Clients accounts (sample batch job)

• This, too, is a batch job, just one task in the MonthEnd procedure.
• Notice the date is the first day of the month.

3. A Client does some operation over the counter, and the Bank charges a fee (cash withdrawal + withdrawal fee),

• To be clear, that is three Business Transactions; two entries each, one for each side of the Credit/Debit pair; affected by one database row each.

4. Mary sends some money from her account, to John's account, which is in the same bank

---

6. SQL Code

There are usually several ways to skin a cat (code), but very few if the cat is alive (code for a high concurrency system).

• The Relational Model is founded on First Order Predicate Calculus (aka First Order Logic), all definitions (DDL) and thus all queries (DML) are entirely Logical.

• A data model that conforms to that understanding, is therefore entirely Logical.

• The queries against such a data model are dead easy: Logical and straight-forward. They have none of the convoluted code that is required for Record ID based filing systems.

Therefore, out of the several methods that are possible for the SQL code requests, I give the most direct and logical.

^{The code examples are that which is appropriate for SO, it is imperative that you trap and recover from errors; that you do not attempt anything that will fail (check the validity of the action before using a verb), and follow OLTP Standards for ACID Transactions, etc. The example code given here are the relevant snippets only.}

6.1. SQL View • Account Current Balance

Since this code segment gets used in many places, let's do the right thing and create a View.

• Note that on genuine SQL platforms, source code is compiled and run when it is submitted, Stored Procs and Views are stored in their compiled form, thus eliminating the compilation on every execution. Unlike the mickey mouse NONsql suites.

• High-end commercial SQL platforms do a lot more, such as caching the Query Plans for Views, and the queries in Stored Procs.

CREATE VIEW Account_Current_V
    AS 
SELECT  AccountNo, 
    Date = DATEADD( DD, -1, GETDATE() ),     -- show /as of/ previous day 
    ASS.ClosingBalance,                      -- 1st of this month
    TotalCredit = ( 
        SELECT SUM( Amount ) 
            FROM AccountTransaction  ATT
            WHERE ATT.AccountNo = ASS.AccountNo 
                AND XactTypeCode_Ext IN ( "AC", "Dp" ) 
                -- >= 1st day of this month yy.mm.01  /AND <= current date/
                AND DateTime >= CONVERT( CHAR(6), GETDATE(), 2 ) + "01" 
            ), 
    TotalDebit = ( 
        SELECT SUM( Amount ) 
            FROM AccountTransaction ATT
            WHERE ATT.AccountNo = ASS.AccountNo 
                AND XactTypeCode_Ext NOT IN ( "AC", "Dp" ) 
                AND DateTime >= CONVERT( CHAR(6), GETDATE(), 2 ) + "01" 
                ),
    CurrentBalance = ClosingBalance + 
        <TotalCredit> -   -- subquery above 
        <TotalDebit>      -- subquery above 
    FROM AccountStatement  ASS
                                             -- 1st day of this month
    WHERE ASS.Date = CONVERT( CHAR(6), GETDATE(), 2 ) + "01"

6.2. SQL Transaction • [1.2] Withdraw from [External] Account

A proc for another DEA business Transaction.

CREATE PROC Account_Withdraw_tr ( 
    @AccountNo, 
    @Amount
    ) AS
    IF EXISTS ( SELECT 1                       -- validate before verb
            FROM AccountCurrent_V 
            WHERE AccountNo = @AccountNo 
                AND CurrentBalance >= @Amount  -- withdrawal is possible
            )
        BEGIN
        SELECT @LedgerNo = LedgerNo 
            FROM Ledger 
            WHERE Name = "HouseCash"
        BEGIN TRAN
        INSERT AccountTransaction 
            VALUES ( @LedgerNo, GETDATE(), "Cr", "Wd", @AccountNo, @Amount )
        COMMIT TRAN
        END

6.3. SQL Transaction • [1.1] Deposit to [External] Account

A proc, set up as an SQL Transaction, to execute a DEA business Transaction.

CREATE PROC Account_Deposit_tr ( 
    @AccountNo, 
    @Amount
    ) AS
    ... IF EXISTS, etc ...                   -- validate before verb
        BEGIN
        SELECT @LedgerNo ...
        BEGIN TRAN
        INSERT AccountTransaction 
            VALUES ( @LedgerNo, GETDATE(), "Dr", "Dp", @AccountNo, @Amount )
        COMMIT TRAN
        END

6.4. SQL Transaction • [Internal] Ledger Account Transfer

A proc to add any business Transaction to LedgerAccount. It is always:

• one LedgerTransaction.LedgerNo, which is the Credit leg
• one LedgerTransaction.LedgerNo_Dr, which is the Debit leg.
• given by the caller.

CREATE PROC Ledger_Xact_tr ( 
    @LedgerNo,    -- Credit Ledger Account
    @LedgerNo_Dr, -- Debit  Ledger Account
    @Amount 
    ) AS
    ... IF EXISTS, etc ...
        BEGIN
        SELECT @LedgerNo ...
        BEGIN TRAN
        INSERT LedgerTransaction  
            VALUES ( @LedgerNo, GETDATE(), @LedgerNo_Dr, @Amount )
        COMMIT TRAN
        END

6.5. SQL Batch Task • Account Month End

This uses a View that is similar to [6.1 Account Current Balance], for any month (views are generic), with the values constrained to the month. The caller selects the previous month.

• This Answer now exceeds the SO limit, thus it is provided in a link Account_Month_V.

Just one Task, in a stored proc, to process the Month End for AccountStatement, which is executed as a batch job. Again, just the essential code, the infrastructure needs to be added.

CREATE PROC Account_MonthEnd_btr ( ... )
    AS    
... begin loop
... batch transaction control (eg. 500 rows per xact), etc ...
INSERT AccountStatement
    SELECT  ACT.AccountNo,
            CONVERT( CHAR(6), GETDATE(), 2 ) + "01",  -- 1st day THIS month
            AMV.ClosingBalance,                       -- for PREVIOUS month
            AMV.TotalCredit,
            AMV.TotalDebit
        FROM Account ACT 
            JOIN Account_Month_V AMV               -- follow link for code
                ON ACT.AccountNo = AMV.AccountNo
                                                   -- 1st day PREVIOUS month
        WHERE AMV.OpeningDate = DATEADD( MM, -1, ACT.Date ) 
... end loop
... batch transaction control, etc ...

6.6. SQL Report • SUM( Credit ) vs SUM( Debit )

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. There is more to it.

Hopefully, I have given the Method and details, and covered the understanding and the more, such that you can now write the required SELECT to produce the required report with ease.

Or perhaps the Monthly Statement for external Accounts, with a running total AccountBalance column. Think: a Bank Statement.

• One of the many, great efficiencies of a genuine Relational database is, any report can be serviced via a single SELECT command.

---

One PDF

Last but not least, it is desirable to have the Data Model; the example Transactions; the code snippets, all organised in a single PDF, in A3 (11x17 for my American friends). For studying and annotation, print in A2 (17x22).

---

Answer 2

Here is my schema (using sqlite as an example) with 2 tables:

Tables

-- This is a list of your chart of accounts
CREATE TABLE "accounts" (
    "name"      TEXT,
    "number"    INTEGER,
    "normal"    INTEGER
)

-- This is a table of each transaction
CREATE TABLE "transactions"
  (
     "id"        INTEGER, 
     "date"      TEXT,
     "amount"    REAL,
     "account"   INTEGER,
     "direction" INTEGER
  ) 

With this convention, the accounts.normal and transaction.direction fields are set to 1 for debit and -1 for credit. The end user never sees this but it makes arithmetic easy.

When you create a journal entry, it will have at least 2 rows in the transactions table - a debit and a credit. They should share the same id.

To see your balances, you can run this query:

select
  (account) as a,
  name,
  sum(amount * direction * normal) as balance
from
  transactions
  left join accounts on a = accounts.number
group by
  name
order by
  a,
  name;

To view the ledger, you can run this:

select
  id,
  date,
  name,
  case when direction == 1 then amount end as DR,
  case when direction == -1 then amount end as CR
from
  transactions
  left join accounts on account = accounts.number
order by
  id,
  date,
  CR,
  DR;

I have a much more detailed post of different queries you can run, along with example data. But, with the above two tables, you can create a working double-entry system.