该数据库将存储对用户和货币交易。
每个用户每种货币都有一个余额，因此每个余额就是针对给定用户和货币的所有交易的总和。
余额不能为负。
银行申请我将专门通过存储过程与其数据库进行通信。
我希望该数据库每天接受成千上万的新交易，并平衡更高数量级的查询。要非常快地用完余额，我需要预先对其进行汇总。同时，我需要保证余额永远不会与其交易历史相矛盾。
我的选择是：
具有单独的
balances表并执行以下操作之一：将事务应用于
transactions和balances表。在我的存储过程层中使用TRANSACTION逻辑，以确保余额和交易始终保持同步。 （由Jack支持。）将事务应用于
transactions表，并具有一个触发器，该触发器会使用交易金额为我更新balances表。将事务应用于
balances表并具有添加新的触发器我必须在transactions表中输入交易金额。我必须依靠基于安全性的方法来确保在存储过程之外无法进行任何更改。否则，例如，某些过程可以直接将事务插入到
transactions表中，而在方案1.3下，相关余额将不同步。具有一个
balances索引视图，该视图适当地聚合了事务。存储引擎保证余额与事务保持同步，因此我不需要依靠基于安全性的方法来保证这一点。另一方面，由于视图（甚至是索引视图）不能具有CHECK约束，因此我无法再将余额强制为非负数。 （由Denny支持。）仅具有一个
transactions表，但具有额外的列来存储该交易执行后立即生效的余额。因此，用户和货币的最新交易记录也包含其当前余额。 （下面由Andrew建议；由garik提出。）当我第一次解决此问题时，我阅读了这两次讨论并决定了选项
2。作为参考，您可以在此处看到其基本实现。您如何解决这个问题？ 您认为我做出了正确的设计选择吗？我有什么要记住的吗？
例如，我知道对
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
这样，带有已归档交易的余额将保持完整且一致的交易历史。
#1 楼
我不熟悉会计，但是我解决了库存类型环境中的一些类似问题。我将运行总计与事务存储在同一行中。我正在使用约束，因此即使在高并发情况下，我的数据也不会出错。早在2009年，我就编写了以下解决方案：无论是使用游标还是使用三角形联接，计算运行总计的速度都非常缓慢。进行非规范化，将运行总计存储在列中非常诱人，特别是如果您经常选择它。但是，像往常一样，在进行非规格化时，需要保证非规格化数据的完整性。幸运的是，您可以保证有约束条件的运行总计的完整性–只要所有约束都受信任，您的所有运行总计都是正确的。同样，通过这种方式，您可以轻松地确保当前余额（运行总计）永远不会为负-通过其他方法强制执行也可能非常缓慢。以下脚本演示了该技术。CREATE TABLE Data.Inventory(InventoryID INT NOT NULL IDENTITY,
ItemID INT NOT NULL,
ChangeDate DATETIME NOT NULL,
ChangeQty INT NOT NULL,
TotalQty INT NOT NULL,
PreviousChangeDate DATETIME NULL,
PreviousTotalQty INT NULL,
CONSTRAINT PK_Inventory PRIMARY KEY(ItemID, ChangeDate),
CONSTRAINT UNQ_Inventory UNIQUE(ItemID, ChangeDate, TotalQty),
CONSTRAINT UNQ_Inventory_Previous_Columns
UNIQUE(ItemID, PreviousChangeDate, PreviousTotalQty),
CONSTRAINT FK_Inventory_Self FOREIGN KEY(ItemID, PreviousChangeDate, PreviousTotalQty)
REFERENCES Data.Inventory(ItemID, ChangeDate, TotalQty),
CONSTRAINT CHK_Inventory_Valid_TotalQty CHECK(
TotalQty >= 0
AND (TotalQty = COALESCE(PreviousTotalQty, 0) + ChangeQty)
),
CONSTRAINT CHK_Inventory_Valid_Dates_Sequence CHECK(PreviousChangeDate < ChangeDate),
CONSTRAINT CHK_Inventory_Valid_Previous_Columns CHECK(
(PreviousChangeDate IS NULL AND PreviousTotalQty IS NULL)
OR (PreviousChangeDate IS NOT NULL AND PreviousTotalQty IS NOT NULL)
)
);
-- beginning of inventory for item 1
INSERT INTO Data.Inventory(ItemID,
ChangeDate,
ChangeQty,
TotalQty,
PreviousChangeDate,
PreviousTotalQty)
VALUES(1, '20090101', 10, 10, NULL, NULL);
-- cannot begin the inventory for the second time for the same item 1
INSERT INTO Data.Inventory(ItemID,
ChangeDate,
ChangeQty,
TotalQty,
PreviousChangeDate,
PreviousTotalQty)
VALUES(1, '20090102', 10, 10, NULL, NULL);
Msg 2627, Level 14, State 1, Line 10
Violation of UNIQUE KEY constraint 'UNQ_Inventory_Previous_Columns'.
Cannot insert duplicate key in object 'Data.Inventory'.
The statement has been terminated.
-- add more
DECLARE @ChangeQty INT;
SET @ChangeQty = 5;
INSERT INTO Data.Inventory(ItemID,
ChangeDate,
ChangeQty,
TotalQty,
PreviousChangeDate,
PreviousTotalQty)
SELECT TOP 1 ItemID, '20090103', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
FROM Data.Inventory
WHERE ItemID = 1
ORDER BY ChangeDate DESC;
SET @ChangeQty = 3;
INSERT INTO Data.Inventory(ItemID,
ChangeDate,
ChangeQty,
TotalQty,
PreviousChangeDate,
PreviousTotalQty)
SELECT TOP 1 ItemID, '20090104', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
FROM Data.Inventory
WHERE ItemID = 1
ORDER BY ChangeDate DESC;
SET @ChangeQty = -4;
INSERT INTO Data.Inventory(ItemID,
ChangeDate,
ChangeQty,
TotalQty,
PreviousChangeDate,
PreviousTotalQty)
SELECT TOP 1 ItemID, '20090105', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
FROM Data.Inventory
WHERE ItemID = 1
ORDER BY ChangeDate DESC;
-- try to violate chronological order
SET @ChangeQty = 5;
INSERT INTO Data.Inventory(ItemID,
ChangeDate,
ChangeQty,
TotalQty,
PreviousChangeDate,
PreviousTotalQty)
SELECT TOP 1 ItemID, '20081231', @ChangeQty, TotalQty + @ChangeQty, ChangeDate, TotalQty
FROM Data.Inventory
WHERE ItemID = 1
ORDER BY ChangeDate DESC;
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint
"CHK_Inventory_Valid_Dates_Sequence".
The conflict occurred in database "Test", table "Data.Inventory".
The statement has been terminated.
SELECT ChangeDate,
ChangeQty,
TotalQty,
PreviousChangeDate,
PreviousTotalQty
FROM Data.Inventory ORDER BY ChangeDate;
ChangeDate ChangeQty TotalQty PreviousChangeDate PreviousTotalQty
----------------------- ----------- ----------- ----------------------- -----
2009-01-01 00:00:00.000 10 10 NULL NULL
2009-01-03 00:00:00.000 5 15 2009-01-01 00:00:00.000 10
2009-01-04 00:00:00.000 3 18 2009-01-03 00:00:00.000 15
2009-01-05 00:00:00.000 -4 14 2009-01-04 00:00:00.000 18
-- try to change a single row, all updates must fail
UPDATE Data.Inventory SET ChangeQty = ChangeQty + 2 WHERE InventoryID = 3;
UPDATE Data.Inventory SET TotalQty = TotalQty + 2 WHERE InventoryID = 3;
-- try to delete not the last row, all deletes must fail
DELETE FROM Data.Inventory WHERE InventoryID = 1;
DELETE FROM Data.Inventory WHERE InventoryID = 3;
-- the right way to update
DECLARE @IncreaseQty INT;
SET @IncreaseQty = 2;
UPDATE Data.Inventory
SET
ChangeQty = ChangeQty
+ CASE
WHEN ItemID = 1 AND ChangeDate = '20090103'
THEN @IncreaseQty
ELSE 0
END,
TotalQty = TotalQty + @IncreaseQty,
PreviousTotalQty = PreviousTotalQty +
CASE
WHEN ItemID = 1 AND ChangeDate = '20090103'
THEN 0
ELSE @IncreaseQty
END
WHERE ItemID = 1 AND ChangeDate >= '20090103';
SELECT ChangeDate,
ChangeQty,
TotalQty,
PreviousChangeDate,
PreviousTotalQty
FROM Data.Inventory ORDER BY ChangeDate;
ChangeDate ChangeQty TotalQty PreviousChangeDate PreviousTotalQty
----------------------- ----------- ----------- ----------------------- ----------------
2009-01-01 00:00:00.000 10 10 NULL NULL
2009-01-03 00:00:00.000 7 17 2009-01-01 00:00:00.000 10
2009-01-04 00:00:00.000 3 20 2009-01-03 00:00:00.000 17
2009-01-05 00:00:00.000 -4 16 2009-01-04 00:00:00.000 20
#2 楼
要考虑的另一种略有不同的方法（类似于您的第二个选项）是只拥有事务表，其定义为：CREATE TABLE Transaction (
UserID INT
, CurrencyID INT
, TransactionDate DATETIME
, OpeningBalance MONEY
, TransactionAmount MONEY
);
您可能还需要事务ID / Order，以便您可以在同一日期处理两个事务并改善检索查询。
要获取当前余额，您需要获取的只是最后一条记录。
获取最后一条记录的方法：顺序错误（即：更正问题/起始余额不正确），您可能需要为所有后续交易层叠更新
。
需要对用户/货币的事务进行序列化以保持准确的平衡。
/* 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
/>
您不再需要维护两个单独的表...
您可以轻松地验证余额，并且当余额不同步时，您可以准确确定余额何时消失
编辑：一些示例查询有关检索当前余额并突出显示缺点（感谢@Jack Douglas）
评论
SELECT TOP（1）... ORDER BY TransactionDate DESC的实现非常棘手，因为SQL Server不会不断扫描事务表。亚历克斯·库兹涅佐夫（Alex Kuznetsov）在此处发布了一个解决方案，解决了类似的设计问题，从而完美地补充了这一答案。
–尼克·查玛斯（Nick Chammas）
2012年6月16日18:58
#3 楼
不允许客户的余额少于0是一项业务规则（随着超支之类的费用是银行赚钱的主要方式，这会迅速改变）。当将行插入到事务历史记录中时，您将需要在应用程序处理中处理此问题。尤其是当您最终可能获得一些具有透支保护的客户，一些收取费用的客户以及一些不允许输入负金额的客户时。到目前为止，我喜欢您的处理方式，但是如果对于一个实际的项目（不是学校），需要在业务规则等方面进行大量思考。一旦您建立了银行系统并开始运行，就没有太多重新设计的空间了，因为有非常具体的关于人们有钱的法律。
评论
我可以看到为什么余额约束实际上应该是业务规则。该数据库仅提供事务服务，并且由用户决定如何处理它。
–尼克·查玛斯（Nick Chammas）
2011年9月13日下午16:10
您如何看待Jack的评论，即使用这两个表可使开发人员在更改或实现业务逻辑时更具灵活性？此外，您是否对索引视图有任何直接的经验，可以验证或挑战这些问题？
–尼克·查玛斯（Nick Chammas）
2011年9月13日下午16:17
我不会说拥有两个表使您能够灵活移动是实现业务逻辑。它确实为您提供了更大的数据归档灵活性。但是，作为一家银行（至少在美国是这样），您有法律规定您需要保留多少数据。您将要测试在顶部视图下的性能，并考虑到如果您拥有索引视图，则无法更改基础表的架构。只是要考虑的另一件事。
–mrdenny
2011年9月13日19:12
本文中提到的所有项目都是使用索引视图时的有效关注点。
–mrdenny
2011年9月13日19:14
需要说明的是：IMO事务API为实现业务逻辑（没有两个表）提供了更大的灵活性。在这种情况下，我也会赞成使用两个表（至少考虑到目前为止我们所拥有的信息），这是因为采用索引视图方法提出了折衷方案（例如不能再使用DRI来执行balance> 0业务规则）
–杰克·道格拉斯（Jack Douglas）
2011年9月14日上午8:26
#4 楼
在阅读了这两个讨论之后，我决定了选项2。
也阅读了这些讨论，我不确定为什么您决定在最明智的DRI解决方案上做出决定。您概述的其他选项：
将事务应用于事务表和余额表。
在存储过程层中使用TRANSACTION逻辑来确保
余额和事务为始终保持同步。
如果您可以通过事务性API限制对数据的所有访问，则这种解决方案具有巨大的实际好处。您将失去DRI的一个非常重要的好处，那就是数据库可以保证完整性，但是在任何足够复杂的模型中，都会有一些DRI无法执行的业务规则。建议在可能的情况下使用DRI来执行业务规则，而又不会过度弯曲模型以使之成为可能：
即使我正在归档事务（例如，将它们移动到其他地方，用汇总交易替换它们）
一旦您开始考虑对这样的模型造成污染，我认为您正进入一个DRI的收益被您所面临的困难超过的领域介绍。例如，考虑到归档过程中的错误理论上可能会导致您的黄金法则（余额始终等于事务之和）被DRI解决方案静默破坏。
这里总结了优点我所看到的事务处理方法：
我们应该尽可能地这样做。无论您针对此特定问题选择哪种解决方案，它都将为您提供更多的设计灵活性和对数据的控制。这样，所有访问就根据业务逻辑而成为“事务性”，而不仅仅是数据库逻辑。
您可以使模型保持整洁
您可以“强制执行”范围更广，更复杂的业务规则（请注意，“强制执行”的概念比使用DRI宽松一些）
您仍可以在可行的地方使用DRI为模型提供更强大的基础完整性-这可以作为检查事务逻辑的一种方法
困扰您的大多数性能问题都将消失
引入新的要求可能会容易得多-例如：复杂的交易规则可能迫使您远离纯粹的DRI方法，这意味着大量浪费的工作
对历史数据进行分区或归档变得没有风险和痛苦的多了
--edit
要允许归档而又不增加复杂性或风险，您可以选择将摘要行保留在连续生成的单独摘要表中（从@Andrew和@Garik借用）。
例如，如果每月汇总：
每次都有交易（通过您的API） e是摘要表中的对应更新或插入
摘要表永远不会被存档，但是归档事务变得非常简单a删除（或删除分区？）
摘要表中的每一行都包含“期初余额” '和'金额'
检查约束，例如'期初余额'+'金额'> 0和'期初余额'> 0可以应用于汇总表
汇总行可以按月插入使锁定最新的摘要行变得容易（当前月份总是有一行）
评论
关于您的编辑：因此，您建议在主余额表旁边同时包含此汇总表？然后，余额表是否有效地变为仅包含当月记录的汇总表（因为两者都将存储相同类型的数据）？如果我理解正确，那么为什么不只用汇总表上的适当分区替换余额表呢？
–尼克·查玛斯（Nick Chammas）
2011-09-19 18:58
抱歉，您还说不清楚-我的意思是放弃余额表，因为它始终是摘要表上获取当前余额的关键查询（Andrews建议AFAIK并非如此）。这样做的好处是，以前的余额计算变得更加容易，并且如果出现错误，对余额的审核记录也会更加清晰。
–杰克·道格拉斯（Jack Douglas）
2011年9月19日19:44
#5 楼
尼克。主要思想是将余额和交易记录存储在同一张表中。我认为这是历史上发生的事情。因此，在这种情况下，我们只需查找最后的摘要记录即可获得平衡。
id user_id currency_id amount is_summary (or record_type)
----------------------------------------------------
1 3 1 10.60 0
2 3 1 10.60 1 -- summary after transaction 1
3 3 1 -55.00 0
4 3 1 -44.40 1 -- summary after transactions 1 and 3
5 3 1 -12.12 0
6 3 1 -56.52 1 -- summary after transactions 1, 3 and 5
一个更好的变体是减少摘要记录的数量。我们可以在一天的结束（和/或开始）有一个余额记录。如您所知，每家银行都可以开立
operational day，然后关闭以进行当天的一些汇总操作。它使我们可以使用每日余额记录轻松计算利息，例如：user_id currency_id amount is_summary oper_date
--------------------------------------------------------------
3 1 10.60 0 01/01/2011
3 1 -55.00 0 01/01/2011
3 1 -44.40 1 01/01/2011 -- summary at the end of day (01/01/2011)
3 1 -12.12 0 01/02/2011
3 1 -56.52 1 01/02/2011 -- summary at the end of day (01/02/2011)
运气。
#6 楼
根据您的要求，选项1看起来最好。尽管我的设计只允许在事务表中插入数据。并在事务表上具有触发器，以更新实时余额表。您可以使用数据库权限来控制对这些表的访问。通过这种方法，可以保证实时余额与事务表同步。并且使用存储过程还是使用psql或jdbc都没有关系。如果需要，可以进行负余额检查。性能不会成为问题。为了获得实时平衡，它是一个单例查询。
归档不会影响这种方法。如果需要报表，还可以有每周，每月，每年的摘要表。
#7 楼
在Oracle中，您可以只使用带有快速刷新的实现视图的事务表来执行此操作，以进行汇总以形成余额。您在实例化视图上定义触发器。如果将实现视图定义为“ ON COMMIT”，则它将有效地防止在基表中添加/修改数据。
触发器检测到[无效]有效数据并引发异常，从而回滚事务。
一个很好的例子在这里http://www.sqlsnippets.com/en/topic-12896.html
我不知道sqlserver，但也许它有一个类似的选择？
评论
Oracle中的物化视图与SQL Server“索引视图”相似，但是它们是自动刷新的，而不是以Oracle的“ ON COMMIT”行为之类的显式管理方式刷新的。请参阅social.msdn.microsoft.com/Forums/fi-FI/transactsql/thread/…和techembassy.blogspot.com/2007/01/…
– GregW
2011-09-14 21:52
评论
如果选择选项2（我认为更干净），请查看pgcon.org/2008/schedule/attachments/…如何有效地实现“物化视图”。对于选项1，Haan和Koppelaars的“数据库专业人员的应用数学”的第11章（不必担心标题）将有助于您了解如何有效地实现“转换约束”。第一个链接用于PostgreSQL，第二个链接用于Oracle，但是该技术适用于任何合理的数据库系统。从理论上讲，您想做＃3。进行“运行余额”的正确方法是为每个事务分配余额。确保可以使用序列号（首选）或时间戳确定地订购事务。您真的不应该“计算”运行平衡。