编写一个简单的银行模式：我应该如何让我的余额与他们的交易历史保持同步？

一尘不染

编写一个简单的银行模式：我应该如何让我的余额与他们的交易历史保持同步？

sql

我正在为一个简单的银行数据库编写架构。以下是基本规格：

数据库将存储针对用户和货币的交易。
每个用户每种货币都有一个余额，因此每个余额只是针对给定用户和货币的所有交易的总和。
余额不能为负数。

银行应用程序将专门通过存储过程与其数据库进行通信。

我希望这个数据库每天能够接受数十万笔新交易，以及更高数量级的余额查询。为了快速提供余额，我需要预先汇总它们。同时，我需要保证余额永远不会与其交易历史相矛盾。

我的选择是：

有一个单独的balances表并执行以下操作之一：
将事务应用于transactions和balances表。在我的存储过程层中使用TRANSACTION逻辑来确保余额和交易始终保持同步。
将交易应用到transactions表中，并有一个触发器balances为我更新表中的交易金额。
将交易应用到balances表中，并有一个触发器transactions为我在表中添加一个带有交易金额的新条目。

我必须依靠基于安全的方法来确保不能在存储过程之外进行任何更改。否则，例如，某些进程可以直接将事务插入transactions表中，并且在方案下1.3，相关余额将不同步。

有一个balances索引视图，可以适当地聚合事务。存储引擎保证余额与他们的交易保持同步，所以我不需要依赖基于安全的方法来保证这一点。另一方面，我不能再强制余额为非负数，因为视图——即使是索引视图——也不能有CHECK约束。
只有一个transactions表，但有一个额外的列来存储在该事务执行后立即生效的余额。因此，用户和货币的最新交易记录也包含他们当前的余额。
您是否设计或管理过这样一个具有高负载配置的数据库？你对这个问题的解决方案是什么？
你认为我做出了正确的设计选择吗？有什么我应该记住的吗？

例如，我知道对transactions表的架构更改将需要我重建balances视图。即使我正在归档事务以保持数据库较小（例如，通过将它们移动到其他地方并用摘要事务替换它们），每次模式更新都必须从数以千万计的事务中重建视图，这可能意味着每次部署的停机时间要长得多。

如果索引视图是要走的路，我怎么能保证没有余额是负数？

归档交易：

让我详细说明一下归档交易和我上面提到的“汇总交易”。首先，在这样的高负载系统中，定期归档将是必要的。我想保持余额及其交易历史之间的一致性，同时允许将旧交易移动到其他地方。为此，我将用每个用户和货币的金额摘要替换每批存档交易。

因此，例如，此交易列表：

user_id    currency_id      amount    is_summary
------------------------------------------------
      3              1       10.60             0
      3              1      -55.00             0
      3              1      -12.12             0

被存档并替换为：

user_id    currency_id      amount    is_summary
------------------------------------------------
      3              1      -56.52             1

通过这种方式，带有归档交易的余额可以保持完整且一致的交易历史。

阅读 96

2022-10-26

共1个答案

一尘不染

要考虑的一种稍微不同的方法（类似于您的第二个选项）是只使用事务表，其定义为：

CREATE TABLE Transaction (
      UserID              INT
    , CurrencyID          INT 
    , TransactionDate     DATETIME  
    , OpeningBalance      MONEY
    , TransactionAmount   MONEY
);

您可能还需要一个交易 ID/订单，以便您可以处理两个具有相同日期的交易并改进您的检索查询。

要获取当前余额，您只需要获取最后一条记录即可。

获取最后一条记录的方法：

/* For a single User/Currency */
Select TOP 1 *
FROM dbo.Transaction
WHERE UserID = 3 and CurrencyID = 1
ORDER By TransactionDate desc

/* For multiple records ie: to put into a view (which you might want to index) */
SELECT
    C.*
FROM
    (SELECT 
        *, 
        ROW_NUMBER() OVER (
           PARTITION BY UserID, CurrencyID 
           ORDER BY TransactionDate DESC
        ) AS rnBalance 
    FROM Transaction) C
WHERE
    C.rnBalance = 1
ORDER BY
    C.UserID, C.CurrencyID

缺点：

当不按顺序插入交易时（即：纠正问题/不正确的起始余额），您可能需要对所有后续交易进行级联更新。
用户/货币的交易需要序列化以保持准确的平衡。

sql -- Example of getting the current balance and locking the -- last record for that User/Currency. -- This lock will be freed after the Stored Procedure completes. SELECT TOP 1 @OldBalance = OpeningBalance + TransactionAmount FROM dbo.Transaction with (rowlock, xlock) WHERE UserID = 3 and CurrencyID = 1 ORDER By TransactionDate DESC;

优点：

您不再需要维护两个单独的表…
您可以轻松地验证余额，并且当余额不同步时，您可以准确地确定它何时失控，因为交易历史成为自我记录。

2022-10-26