数据库设计：计算帐户余额 [英] Database design: Calculating the Account Balance

问题描述

如何设计数据库来计算帐户余额？

1）目前我从交易表中计算帐户余额
在我的交易表我有说明和金额等。

然后，我会将所有的金额值，将解决用户的帐户余额。 >

---

我向我的朋友展示了这个，他说这不是一个好的解决方案，当我的数据库增长减慢？ ??他说我应该创建单独的表来存储计算的帐户余额。如果是这样，我将保留两个表，而且它的风险，帐户余额表可能不同步。

有任何建议吗？

EDIT ：选项2：我应该为我的事务表Balance添加一个额外的列。
现在我不需要经过许多行数据来执行我的计算。

示例
John购买$ 100信用，

金额 - $ 60，余额$ 40。

$ b。 / p>

金额$ 200，余额$ 240。

解决方案

我使用过的所有银行包都将余额存储在帐户实体中。

正确的方法是：

• 移动表对每个帐户都有一个开始
  余额交易。你需要
  这个在几年的时间，当你
  需要将旧的动作从
  活动移动表移动到历史
  表。


• 帐户实体有一个余额
  字段


• 运动触发器
  表更新帐户
  贷记和借记帐户。显然，它有承诺
  控制。如果您不能有触发器，那么需要一个独特模块，在承诺控制下写入动作。


• 您有一个安全网
  可以离线运行，重新计算
  所有余额和显示（和
  可选更正）错误
  余额。这对于
  测试非常有用。

一些系统将所有移动存储为正数，并表示信用/借记反转from / to字段或使用标志。就个人而言，我更喜欢信用字段，借记字段和签名金额，这使得逆转更容易遵循。

请注意，这些方法适用于现金和证券。

证券交易可能非常棘手，尤其是对于公司行为，您需要容纳一个交易，更新一个或多个买方和卖方现金余额，其安全头寸余额和可能的经纪人/存托。

How do I design the database to calculate the account balance?

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.

Any suggestion?

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.

Example John buys $100 credit, he debt $60, he then adds $200 credit.

Amount $100, Balance $100.

Amount -$60, Balance $40.

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 right way is:

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

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.

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