Should a ledger DB store a separate line for each side of a transaction?

Question

I'm working on a database that needs to do manage transfers between several accounts. We are considering using a simple ledger system for this (like what you find in Quicken).

I have two concerns with how to design the system:

1. Tracking the related transactions, for this I considered using a single LedgerEntry for both sides of a transaction
2. Keeping the totals easy to calculate, for this I want to use a separate LedgerEntry for both sides of a transaction

Here's a fiddle w/ both options and their problems.

Answer 1

If you decide to keep a single entry for both sides of a transaction, then by definition you are engaging in single-entry bookkeeping. This may be the most appropriate solution for some simple applications, but be clear that you are losing all the functional and robustness advantages of double-entry bookkeeping, in exchange for a simpler design.

Note that when viewed stand-alone Subledgers (though not their corresponding Journals) are often implemented as single-entry, since a control-account in the General Ledger captures the subledger total and the balancing side of the transactions are in the General Journal (GJ) and General Ledger (GL).

You also appear to be confusing the distinct concepts of Ledger and Journal in traditional double-entry bookkeeping. The various Journals (of which there will be numerous specialized varieties for specific common transactions of the business in addition to the General Journal) is a chronological history of all transactions entered into the system. The General Ledger is an ordering by account of all transactions entered into the system, and the various subledgers are an ordering by subledger-code of all transactions entered into the corresponding Journal.

Two examples of common Ledger and Journal combinations:

1. the General Journal and General Ledger are an aggregated summary of all transactions used for generating Trial Balances and the Financial Statements.

2. The Account Receivable subledger receives entries from the Invoicing Journal (debits to AR by Customer Number) and the Cash Receipts Journal (credits to AR by Customer Number).

Note that while both sides of every transaction will balance in toto, in general it is not required for the two sides of a transaction to be the same number of lines with the same values. For instance a simple Retail Sales transaction (in most jurisdictions) will look something like this:

Dr Accounts Receivable Customer #xxxx     $X,XXX.XX
Cr Revenue                                           $Z,ZZZ.ZZ
Cr Sales Tax Payable                                 $  YYY.YY
-----------------------------------------------------------------
Totals                                    $X,XXX.XX  $X,XXX.XX

A manufacturing Sales transaction (at the simplest level) will start like this:

Dr Accounts Receivable Customer #xxxx     $X,XXX.XX
Cr Revenue                                           $Z,ZZZ.ZZ
Cr Sales Tax Payable                                 $  YYY.YY
Dr Cost of Sales                          $U,UUU.UU
Cr Finished Goods Inventory                          $U,UUU.UU
-----------------------------------------------------------------
Totals                                    $W,WWW.WW  $W,WWW.WW

A Bond Purchase transaction will have to handle the Discount(Premium) on Face Value as either a Credit or a Debit depending on whether interest rates have risen or fallen since the bond was issued, so you won't even know in advance which side of the ledger your single-entry is on.

Once you decide to engage in single-entry bookkeeping you lose all ability to handle these transactions, which as seen here are by no means either rare or complicated.

Answer 2

At the risk of sounding like a broken record, I think that we (at least many posters to this forum) need to break away from NIH (Not Invented Here) syndrome. The chances are that any reasonable idea that we have has been thought of before. Certainly in this case, it does appear to have been.

A Google of "open source ledger system" gives (amongst other interesting links) this site which should give you plenty of ideas on how to design a ledger system. Newton put it best when he said (paraphrasing) "I only see as far as I do because I stand on the shoulders of giants"

Open Source projects are a great resource for ideas on how to implement systems - many are battle hardened and have stood the test of time. Just a thought.

Answer 3

This is an old question but doesn't appear to have been answered satisfactorily? Here is a proposal:

Simple ledger implementation with single row for each side of a transaction where both entries are linked under the Transaction ID and each side is differentiated in the account descriptor.

Transaction ID, Account List, Commodity, Amount

where

• Account List is "Left Side/Asset/Cash/Checking/My Bank/Acc12345"
• Transaction ID is "nnnnnn01"
• Commodity is "USD" or "Shares" or "Tins of Beans"
• Amount is number of commodity

the example given could be represented as:

"TX01","Left/Accounts Receivable Customer #xxxx","$",+X,XXX.XX
"TX01","Right/Revenue","$",-Z,ZZZ.ZZ
"TX01","Right/Sales Tax Payable","$",-YYY.YY
"TX01","Left/Cost of Sales","$",+U,UUU.UU
"TX01","Right/Finished Goods Inventory","$",-U,UUU.UU

Total of "TX01" "Left/." "$" is W,WWW.WW Total of "TX01" "Right/." "$" is -W,WWW.WW

Total of all is $0.