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

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:

• Tracking the related transactions, for this I considered using a single LedgerEntry for both sides of a transaction

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

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:

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

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

StackExchange Database Administrators Q#67864, answer score: 5