3

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.

  • Read this PDF on T-Accounts, then mimic a T-Account in your DB – eyoung100 Jun 9 '14 at 14:34
  • @ECarterYoung I read the PDF. How would I implement the relationship between two lines in the LedgerEntry table? In the PDF it uses an alphabetical letter. – just.another.programmer Jun 9 '14 at 16:41
  • As Verace points out below, the solution I offered is NIH. Any Basic Accounting Book explains the theories behind T Accounts within the first 2 chapters. The rest of the book describes how to fit all scenarios within the T-Accounts. Once you understand this relationship, you should have no issues constructing the DB – eyoung100 Jun 9 '14 at 20:15
  • @ECarterYoung: If bookkeeping, never mind following GAAP (Generally Accepted Accounting Practice), was so simple as you suggest then accountants would be paid much less than they are. – Pieter Geerkens Aug 15 '14 at 20:57
3

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.

  • 1
    I disagree, that storing single entry for both sides means engaging in single-entry bookkeeping. If in this row you store sides as well as debit and credit columns, this single row carries the same amount of information as two separate rows for each side. This is still double-bookkeeping. Also, all your examples can be reduced in terms of data. First example can be represented with 2 rows of data, but still, these rows would represent the same amount of information. The second example can be represented with 3 rows and still carry the same amount of information. – Oleksandr Dec 29 '14 at 7:34
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.

  • 1
    GNUCash and most of the other systems I have looked into are far more than I need. They deal with different commodity types, budgeting, special splits, etc. The DB's are very complex as a result, most of which I don't need. I am not designing an accounting system. I am using accounting tables to track some account values in my system. – just.another.programmer Jun 10 '14 at 7:29
  • What I meant was that you could get ideas about table structure and schema design from them - just take what you need and leave the rest. – Vérace Jun 10 '14 at 13:40
  • 1
    Thanks, after looking more carefully into GnuCash's documentation I actually found the Transaction and Split documentation which were helpful. – just.another.programmer Jun 10 '14 at 13:55
-2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.