17

Storing the transaction entry into a double entry accounting database.

I came up with two solutions option 1 and option 2, I was told most banking package chooses option 2 for their database design. However I prefer option 1 over option 2 because it simply makes sense and it is more efficient!

I.e For the 2 movement of funds, option 1 requires 2 records vs option 2 requires 4 records.

I would like to know why the bank would choose option 2 over option 1? what is the reason for this? 

Option 1)
TRANSACTION
Credit_AccountId
Debit_AccountId
Amount
...

Option 2)
TRANSACTION
AccountId
Amount
...
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
001
  • 62,807
  • 94
  • 230
  • 350

2 Answers2

20

Option 1 will potentially be a bit more efficient from an insert perspective. But since a lot of accounting transactions are going to affect more than two accounts, the benefit is likely to be substantially less than 2:1.

Option 2 will be clearer for these more complex transactions. That is, an accountant would normally find three rows

  • Debit A $100
  • Credit B $60
  • Credit C $40

more clear than two rows

  • Debit A $60 Credit B $60
  • Debit A $40 Credit C $40

If you have multiple accounts on both sides, it would also be a bit unclear how to match up the debits and credits to a single account. That is,

  • Debit A $100
  • Debit B $30
  • Credit C $60
  • Credit D $70

could be represented as

  • Debit A $60 Credit C $60
  • Debit A $40 Credit D $40
  • Debit B $30 Credit D $30

but there are also other possible ways to construct the data for data model 2.

Additionally, option 2 is going to be more efficient if you're trying to determine the current balance of a particular account by aggregating the transactions.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • thanks, I can see your point there about complex transactions. – 001 Dec 11 '10 at 06:02
  • Thanks for your answer @Justin Cave, would you please help me on current balance field of the answer, we used model 2, for transactions and also having an Account table with the balance field. the balance field is updating after each transaction, for concurrency issues we used row level lock on account table. every thing was good till we need to use two phase commit due to multi- platform java and .Net and ... We are facing many suspended lock over Account table. Specially "Transaction waiting for lock". DBA is Killing the session manually and removing locks. Is the design incorrect? – Mohsen Heydari Jun 30 '13 at 14:09
  • @M.Heydari - This appears better suited to a new question-- comments are not really the appropriate place for extended discussions. I would not maintain an aggregate value in a transactional system. If you really find that the aggregate is absolutely necessary, use a materialized view. – Justin Cave Jun 30 '13 at 18:09
  • @JustinCave i would like to choose option 2 but my concern is save related information about creditor/debitor for these transactions. How can i do that with option 2 – Marcel Djaman Jun 15 '14 at 21:49
  • @MarcelDjaman - creditor information would be stored with the appropriate credit/ debit transaction. debtor information would likewise be stored with the appropriate transaction. I'm not sure that I see the problem you're having trouble solving. – Justin Cave Jul 02 '14 at 20:15
  • @JustinCave thanks for your answer, what i need to know is how i can tell reading a record in option2 who send money to whom – Marcel Djaman Jul 03 '14 at 11:50
  • 1
    @MarcelDjaman - I'm not sure I follow the question. Presumably, there would be some sort of `transaction_id` that would allow you to identify all N rows that are part of a single transaction. You can see which accounts (and whatever other information you store) were credited and which were debited. As a general accounting matter, though, it may not be possible to say which account sent what to which other account because there may be many sending accounts and many receiving accounts. – Justin Cave Jul 09 '14 at 20:03
6

In a general accounting database design, it is logical and efficient to store your debits and credits in a single fields (i.e. option 2), as this would simplify aggregation,number manipulations and reporting. There should be a datetime field attached to each debit and credit transactions to filter out a particular period. Get the book from Smashwords, titled, accounting database design. It provides some good samples on accounting system design and some interesting sql query for financial reporting.

David
  • 61
  • 1