Double entry bookkeeping database design

Question

I'm creating accounting software. I need to enforce double entry bookkeeping. I have the classical problem of one row per transaction versus two rows.

Let's take an example and see how it would be implemented in both scenarios.

Consider account Cash and account Rent. When I pay my monthly rent, I transfer $100 from my Cash account to me Rent account.

One row per Transaction

In a one row system, such transaction would be stored as:

transactions

 tx_id | posting_date
 1     | 23/05/2015

transaction_records

 id | tx_id | credit_account | debit_account | amount
 1  | 1     | Cash           | Rent          | 100.00

Two rows per Transaction

In a two row system, I'd have to mirror the same transaction record to create an opposite record that once I sum up both, I'd get zero balance.

transactions

 tx_id | posting_date
 1     | 23/05/2015

transaction_records

id  | tx_id | type   | account | amount
1   | 1     | credit | Cash    | 100.00
2   | 1     | debit  | Rent    | 100.00

The problem

First of all I'd like to note: the reason I have both transactions and transaction_records tables (instead of one table) is to be able to handle split transactions (a case where I transfer $100 from Cash account to two or more different accounts).

At first I tried to implement this with one row per transaction, but its a pain to calculate the account balance, and to actually retrieve the data.

I'm leaning towards the second scenario; however, it also has some issues:

• How do I update a single record? Assuming I've made a mistake and instead of recording $100 for my rent, I've recorded $10. I now have 2 transaction_records - one for credit and one for debit, both with amount $10.
• Now I do my reconciliation and I want to fix this typo. How would I fix this in the database? I don't know the connection between records, and in case of a split, one transaction can have more than 2 records. The only solution I came up with is to add some ref_id for each records pair that will uniquely identify those records as being the "opposite sides of each other" inside a context of a specific tx_id.

Which approach is better/simpler?

---

To simplify my question: I want to represent a movement of funds from account A to account B. The two scenarios I gave are both valid designs to store such transaction. As I also pointed out they both have cons & pros (first one: easier to save, harder to retrieve; second one the opposite).

They might have other pros/cons that I do not spot right now, hence I ask an opinion from more experienced people.

Answer 1

Indeed, the single row accounting schema proposed allows to do proper double entry accounting (to always specify the account debited and credited) without introducing the redundancy of the "amount" data.

The one row schema gives you an implementation of a double entry that balances by construction, so that it is impossible to ever "loose balance". The machine may recompute the ledgers on the fly.

You have to do 2 select instead of one to retrieve a ledger.

Note, besides transaction split, there are other transactions such as foreign exchange might end up with 2 records instead of 4. It all depends if you would denormalize a bit just enter 4 transactions with similar description.

You may prevent the entry or modification of any transaction to maintain an audit trail, this is required if you want to be able to audit the transaction log.

It appears that in the thread above, for the CPA, "fully normalized" appears to mean rules recognized by all accountants, whereas for the programmer it has a diferent meaning, that there is no derived or redundant data stored.

All there is to accounting data is a set of transactions which give amount and the accounts from which and to which they flow, along with their date, some description (and other attachements). Ledgers and balances are simple views derived from this transactional data by doing sums.

Answer 2

A Journal is a chronological listing of all transactions of a specified type for an accounting system. Here is a classical presentation on ledger paper of a simple Sales (on Account) Journal:

Note that every line is a single transaction, with Total Debits = Total Credits; and that every transaction hits the same three accounts. A Cash Sales Journal would look similar but replace the column Accounts Receivable Debit with one labelled Cash Debit. A Cash Disbursements Journal would have a first column labelled Cash Credit and additional columns such as Accounts Payable Debit and Employee Expenses Debit.

This presentation was standard for hundreds of years until personal computers became affordable a couple of decades ago. It has a significant advantage of easily verifying that every transaction is balanced by simply checking each row. Likewise, prior to posting a page of such transaction to the Ledgers the page totals could be similarly verified. That is a batch-processing model used in many accounting systems.

It is easy to see that this paper model has significant disadvantages when literally transcribed to an automated system:

1. The data structure is a pivoted one, whereas experience has shown that it is much simpler (also more robust and more easily verified) to program against a folded data structure; and
2. Because every specialized Journal hits a different set of accounts, each such Journal would have to be separately designed and programmed, a wasteful and error-prone activity.

For these reasons it is generally recommended to use the Specialized Journals design as the interface to the accounting system, but to design a data structure that can be the numerical repository for multiple journals. In a modern RDBMS this has the potential advantage that the General Ledger, and even the specialized subledgers, can become Indexed Views on the Journal, completely eliminating the requirement to code a Posting Process (the step where a Journal transaction is locked and has its account totals transcribed to the various ledgers).

Whatever data design you end up with, the key is to have a single Posting Entry for each transaction type (ie each specialized Journal in the equivalent paper system) where balance checks are made.

---

My point is that both approaches you propose are unsound mechanisms for double-entry bookkeeping. First point: Journals are write-once tables for very good reasons. Sometimes the two virtual tables Pending Entries and Posted Entries are collocated in a single data structure, but the IsPosted bit is always write-only and the system must ensure that the read-only nature of the Posted Entries records is maintained.

The way accountants have posted Journal Entries for the past 800 years is fully normalized. The only difference between a paper presentation and a sound electronic presentation is that a folded table structure is more convenient in the latter case while a pivoted table structure in the former, which historically was most significant when highly parallel processing was desired - i.e. many clerks each maintaining a single or small number of specialized journals. Only the Controller historically had permissions for the General Journal.

Note carefully that in the above I specified that Journal Entries are fully normalized; Journal Entries are a chronological record of all transactions, grouped in Journals specific to each transaction type. The posting of journal entries to the Ledgers is a separate endeavour and, while fully normalized on its own, is a redundant copy of the journal entries where all transactions are summarized (General Ledger) or detailed (Sub Ledger) by account. Both Journals and Ledgers employ double-entry bookkeeping independently.

@Codism Any accounting system, DEB or SEB, give you generalized reporting for all accounts recorded. Note that, internally, a sub-ledger is by definition a single-entry bookkeeping record; the other side is the corresponding control account(s) on the Balance Sheet. DEB ensures that for every asset dollar there is a precise record of the business claim on the asset, i.e. the equity in the asset whether it be a debt equity (aka liability) or an ownership equity, and of the priority of those claims if the organization becomes insolvent.

Answer 3

May I suggest that you take a look at the treasure trove of Open Source software that is out there in this area?

A Google of "open source double entry accounting software" gives several promising avenues of investigation.

You could look at the GNU accounting package here, a couple of review sites (1 & 2) and finally the wiki of accounting software with a section on Free Software.

I'm sure that by examining some of the F/LOSS source code and schemas, you might get some good hints and indications of how to write schemas and/or software which suit your own particular needs.

Answer 4

I have a system that does double lines and its lots of challenges than having single lines accounts. First i have encountered instances of only one line being pushed like the debit side, resulting to imbalanced account. Even if am not doing proper controls of committing and rollbacks, this would not happen in a single line accounts. lastly, your db grows in double growth rate, your send second line will have lots of duplicate info, the only difference being the second account. Would advise retain, single line account, am going to that route..