Database Design – 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 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.

Best Answer

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.

Related Question