Database design: accounting transaction table
database, database-design
Solution
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.
Problem
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 ... ```