Storing the transaction entry right into a double entry accounting database.
I emerged with two solutions option 1 and option 2, I had been told most banking package selects option 2 for his or her database design. However prefer option 1 over option 2 since it simply is sensible which is more effective!
I.e For that 2 movement of funds, option 1 requires 2 records versus option 2 requires 4 records.
I must know why the financial institution would choose option 2 over option 1? what's the reason behind this?
Option 1) TRANSACTION Credit_AccountId Debit_AccountId Amount ... Option 2) TRANSACTION AccountId Amount ...
Option 1 will potentially be a little more effective from an place perspective. Consider lots of accounting transactions are likely to affect a lot more than two accounts, the advantage will probably be substantially under 2:1.
Option 2 is going to be clearer of these more complicated transactions. That's, a cpa would normally find three rows
- Debit A $100
- Credit B $60
- Credit C $40
more obvious than two rows
- Debit A $60 Credit B $60
- Debit A $40 Credit C $40
For those who have multiple accounts on sides, it might be also a little unclear how to match the debits and credits to some single account. That's,
- Debit A $100
- Debit B $30
- Credit C $60
- Credit D $70
might be symbolized as
- Debit A $60 Credit C $60
- Debit A $40 Credit D $40
- Debit B $30 Credit D $30
but you will find also other possiblity to construct the information for data model 2.
Furthermore, option 2 will probably be more effective if you are trying to look for the current balance of the particular account by aggregating the transactions.
Inside a general accounting database design, it's logical and efficient to keep your debits and credits in one fields (i.e. option 2), because this would simplify aggregation,number manipulations and confirming. There must be a datetime area mounted on each debit and credit transactions to remove a specific period. Obtain the book from Smashwords, entitled, accounting database design. It offers good quality samples on accounting system design plus some interesting sql query for financial confirming.
You'll need some denormalisation inside your database to keep some historic data for report generation reasons. That will entails some datawarehousing deal with. It is best to store your historic data by datetime format inside a separate table, in the close of the accounting books each and every year-finish, in particulars or by balance. This is often automated by calling a UDF or perhaps a trigger.
By shifting out a few of the past data, you won't just free-up some space for storage, but making your operational server faster and efficient. accountingdes.com responded.