数据库设计：会计事务表 [英] Database design: accounting transaction table

问题描述

将交易条目存储到双重条目会计数据库中。

我想出了两个解决方案选项1和选项2，告诉大多数银行包为他们的数据库设计选择选项2。但是我喜欢选项1比选项2，因为它只是有意义，它是更有效！

对于资金的2次移动，选项1需要2条记录，而选项2需要4条记录。

我想知道为什么银行会选择期权2而不是期权1？这是什么原因？

 选项1）
 TRANSACTION 
 Credit_AccountId 
 Debit_AccountId 
 Amount 
 ... 
 
选项2）
 TRANSACTION 
 AccountId 
金额
 ... 
  pre> 
 
解决方案
从插入角度看，选项1可能会更高效。但由于很多会计交易会影响两个以上的帐户，所以收益可能会大大低于2：1。
 
 
 
选项2会更清楚这些更复杂的事务。也就是说，会计师通常会找到三行
 
 
 
 
 
借记A $ 100 
 
 
 / li> 
 
信用卡C $ 40 
 
 
 
 
 
比两行更清楚
 
 
 
 
 
借记A $ 60信用B $ 60 
 
 
借记A $ 40信用C $ 40 
 
 
 
 
 
如果双方都有多个帐户，那么如何将借方和贷方与单个帐户进行匹配也不太清楚。也就是
 
 
 
 
 
借记A $ 100 
 
 
借记B $ 30 
 
 
信用卡C $ 60 
 
 
信用卡D $ 70 
 
 
 
 
 
可以表示为
 
 
 
 
 
借记A $ 60信用C $ 60 
 
 
借记A $ 40信用D $ 40 
 
 
借记B $ 30信用D $ 30 
 
 
 
 
 
，但也有其他可能的方法来建立资料模型2的资料。 p> 
 
 
此外，如果您要通过聚合交易来确定特定帐户的当前余额，选项2会更加高效。
 
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
...

解决方案
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.

                        
这篇关于数据库设计：会计事务表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！