Double entry bookkeeping database design

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 ad

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:

• 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

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

StackExchange Database Administrators Q#102370, answer score: 22