sqldatabasedatabase-designrelational-databaseaccounting

Relational Data Model for Double-Entry Accounting

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:

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).

Solution

  • 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:

    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.

    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:

    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.

    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.
    1. The purpose or goal of a Double-Entry Accounting system is:
    1. Then ready the Wikipedia entry for Double-Entry Bookkeeping.

    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.

    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.

    1. A Client deposits cash to his account

    Op11 Op12

    Op13

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

    Op2

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

    Op3

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

    Op4

    3. Relational Data Model • Initial

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

    TA

    3.1. Notation

    3.2. Content

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

    3.3. Summary

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

    4. Relational Data Model • Full

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

    TAdata

    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.

    1. A Client deposits cash to his account

    Row11 Row12 Row13

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

    Row2

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

    Row3

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

    Row4

    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).

    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.

    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:

    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.

    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 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).