计算客户余额的最佳方法是什么？ [英] What is the best way to calculate customer balance?
问题描述
大家好，
我想要下面的帮助
背景
有两张表，即提款和存款。每个商店的客户ID，金额和日期。
计算客户余额的最佳方式
选项1.
在每笔交易中添加另一个名为customer_balance的表，其中包含字段customerID，余额和更新余额字段。
可以使用sql触发器
选项2.
余额=客户存款金额 - 提款金额
sql存储procudure或视图可以使用
我也想知道银行和库存管理系统如何计算余额
我的尝试：
余额=客户存款金额 - 提款金额
Hi everybody,
I want help with the below
Background
There are two tables, Withdrawal and Deposit. Each store customerID, amount and date.
What is the best way to calculate customer balance
Option 1.
Add another table called customer_balance with fields customerID,balance and update balance field on every transaction.
sql triggers can be used
Option 2.
balance = sum of customer Deposits - sum of withdrawals
sql stored procudure or view can be used
I also want to know how banking and inventory management systems calculate balances
What I have tried:
balance = sum of customer Deposits - sum of withdrawals
推荐答案
我曾经处理过的一个系统（在金融服务中）有一个隔夜批处理作业，它将根据自那以来的交易总和计算当时的所有余额。上次运行过程（通常是每天一次）但并非总是如此）和当时的平衡 - 开放平衡。这些计算正在进行中，系统的更新被关闭。
重点是找到一个余额（如果你会原谅双关语）之间的开销从第0天开始计算数据存储并更新数据。
关键是你必须有一些方法来确定上次更新余额 - 是的，你可以有一个单独的表格，但是我个人只会在账户表上存储日期余额和最后余额日期（在我们的系统中，每个客户可以拥有多个账户）。
我不同意
A system (in Financial Services) that I have worked on had an overnight batch job that would calculate all balances at that time based on the sum of transactions since the last time the process was run (usually daily but not always) and the balance at that time - the "opening balance". Updates to the system were turned off whilst these calculations were on-going.
The point was to find a balance (if you will excuse the pun) between the overhead of doing calculations from day 0 against the storage of data and updating it.
The key is that you must have some means of determining the "last time the balance was updated" - yes you could have a separate table with it's own ID but personally I would just store the "balance at date" and "last balance date" on the accounts table (in our system each customer could have many accounts).
I don't agree with
有两个表，提款和存款。每个商店的客户ID，金额和日期。
There are two tables, Withdrawal and Deposit. Each store customerID, amount and date.
对我而言，拥有一个带有CustomerID，金额（可以是取款的负数）和交易日期的交易表更为合理。银行业务领域的交易也有一个参考字段（自由格式文本）和唯一标识符。
To me it makes more sense to have a "Transaction" table with CustomerID, Amount (which can be negative for Withdrawals) and Transaction Date. Transactions in the banking arena would also have a reference field (free format text) and a unique identifier.
一般使用 sum 方法首先，直到数据变得太大而索引不够快，然后为余额创建一个缓存值，并承受保持缓存与数据输入同步的成本。
Generally use the sum method first, until the data gets too large and indexes are not fast enough, then create a "cache" value for the balance and endure the cost of keeping that cache "in-sync" with the data input.
你已经走错了路：
提款和存款只是交易（贷记和借方）应该被实施为 ONEtable（而不是2）。
这笔交易被发布到账户。
账户可以是货币（即
Your already off on the wrong foot:
"Withdrawls and Deposits" are simply "transactions" (credits and debits) that should have been implemented as "ONE" table (and not 2).
The transactions are posted to "accounts".
Accounts can be monetary (i.e.
这篇关于计算客户余额的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
