CODEWITHSUNDEEP
databasedatabase-design
001
001

Reputation: 65127

Database design: accounting transaction table

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

Upvotes: 18

Views: 10559

Answers (2)

David
David

Reputation: 61

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.

Upvotes: 6

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 21

Related Questions