数据库设计:计算账户余额
如何设计数据库来计算账户余额?
How do I design the database to calculate the account balance?
1) 目前我从交易表中计算账户余额在我的交易表中，我有描述"和金额"等.
1) Currently I calculate the account balance from the transaction table In my transaction table I have "description" and "amount" etc..
然后我会将所有金额"值相加，然后计算出用户的帐户余额.
I would then add up all "amount" values and that would work out the user's account balance.
我向我的朋友展示了这个，他说这不是一个好的解决方案，当我的数据库增长时它会变慢????他说我应该创建单独的表来存储计算出的帐户余额.如果这样做，我将不得不维护两个表，而且风险很大，帐户余额表可能会不同步.
I showed this to my friend and he said that is not a good solution, when my database grows its going to slow down???? He said I should create separate table to store the calculated account balance. If did this, I will have to maintain two tables, and its risky, the account balance table could go out of sync.
有什么建议吗?
编辑:选项 2:我是否应该在我的交易表余额"中添加一个额外的列.现在我不需要遍历多行数据来执行我的计算.
EDIT: OPTION 2: should I add an extra column to my transaction tables "Balance". now I do not need to go through many rows of data to perform my calculation.
示例约翰购买了 100 美元的信用，他欠了 60 美元，然后他添加了 200 美元的信用.
Example John buys $100 credit, he debt $60, he then adds $200 credit.
金额 100 美元，余额 100 美元.
Amount $100, Balance $100.
金额 - 60 美元，余额 40 美元.
Amount -$60, Balance $40.
金额 200 美元，余额 240 美元.
Amount $200, Balance $240.
推荐答案
一个从未优雅解决的古老问题.
An age-old problem that has never been elegantly resolved.
我使用过的所有银行软件包都将余额存储在帐户实体中.从运动历史中快速计算它是不可想象的.
All the banking packages I've worked with store the balance with the account entity. Calculating it on the fly from movement history is unthinkable.
正确的做法是:
- 运动表有一个开口"平衡每个帐户的交易.你需要这在几年后当你需要将旧动作移出到历史的活动移动表表.
- 账户实体有余额字段
- 有一个动作触发更新帐户的表贷方和借方账户的余额.显然，它有承诺控制.如果你不能有触发器，那么需要有一个独特的模块，它在承诺控制下编写动作
- 您有一个安全网"计划可以离线运行，重新计算所有天平和显示器(和可选更正)错误余额.这对于测试.
- The movement table has an 'opening balance' transaction for each and every account. You'll need this in a few year's time when you need to move old movements out of the active movement table to a history table.
- The account entity has a balance field
- There is a trigger on the movement table which updates the account balances for the credited and debited accounts. Obviously, it has commitment control. If you can't have a trigger, then there needs to be a unique module which writes movements under commitment control
- You have a 'safety net' program you can run offline, which re-calculates all the balances and displays (and optionally corrects) erroneous balances. This is very useful for testing.
有些系统将所有移动存储为正数，并通过反转 from/to 字段或使用标志来表示贷方/借方.就我个人而言，我更喜欢贷方字段、借方字段和签名金额，这样可以更容易地跟踪逆转.
Some systems store all movements as positive numbers, and express the credit/debit by inverting the from/to fields or with a flag. Personally, I prefer a credit field, a debit field and a signed amount, this makes reversals much easier to follow.
请注意，这些方法既适用于现金，也适用于证券.
Notice that these methods applies both to cash and securities.
证券交易可能要复杂得多，尤其是对于公司行为而言，您将需要容纳更新一个或多个买方和卖方现金余额、他们的证券头寸余额以及可能的经纪人/存款机构的单笔交易.
Securities transactions can be much trickier, especially for corporate actions, you will need to accommodate a single transaction that updates one or more buyer and seller cash balances, their security position balances and possibly the broker/depository.
相关文章