可以保留一个在表中更新的值吗？

我们正在开发一个用于预付卡的平台，该平台基本上存储有关卡及其余额，付款等的数据。

到目前为止，我们拥有一个卡实体，该实体包含一组帐户实体，每个帐户

团队中现在有一个辩论；每笔存款/取款都会更新。有人告诉我们，这违反了Codd的12条规则，每次付款都更新其值很麻烦。

这真的有问题吗？

如果是的话，我们怎么能解决这个问题？

关于此主题，在DBA.SE上进行了广泛的技术讨论：编写简单的银行架构

您的团队在此引用了科德的哪些规则？规则是他尝试定义关系系统的尝试，没有明确提及标准化。 Codd确实在他的书《数据库管理的关系模型》中讨论了规范化。

#1 楼

是的，这是非标准化的，但出于性能原因偶尔会胜出。

但是，出于安全原因，我可能会略有不同。（免责声明：我目前不在，也从未在金融部门工作过。我只是把它扔在那里。）

有一张桌子，可在卡上显示已过结的余额。这将为每个帐户插入一行，指示每个期间（天，周，月或其他适当时间）结束时的过帐余额。通过帐号和日期为该表编制索引。

使用另一个表来暂挂未决交易，这些交易会即时插入。在每个期间的结束时，运行一个例程，将未过帐的交易添加到帐户的最后一个期末余额中，以计算新的余额。您可以将待处理的交易标记为已发布，也可以查看日期以确定尚待处理的内容。

通过这种方法，您可以按需计算卡余额，而不必汇总所有帐户历史记录，并将余额重新计算放在专用的过帐例程中，可以确保将此重新计算的交易安全性限制在单个位置（并且还限制了余额表的安全性，因此只有过帐例程可以对其进行写入）。

然后只保留审计，客户服务和性能要求所需的尽可能多的历史数据。

只需两个快速笔记。首先，这是对我上面建议的日志聚合快照方法的很好描述，也许比我更清楚。（赞了您）。其次，我怀疑您在这里使用“过帐”一词有点奇怪，意思是“期末余额的一部分”。从财务角度来说，过帐通常表示“显示在当前分类帐余额中”，因此似乎值得解释，因此不会引起混乱。

–克里斯·特拉弗斯（Chris Travers）
13年1月29日在14:15

是的，我可能错过了很多微妙之处。我仅指的是在营业时间结束时交易似乎被“过账”到我的支票帐户中，并相应地更新了余额。但是我不是会计师。我只是和其中几个一起工作。

–db2
2013年1月29日14:32

将来这也可能是对SOX或类似产品的要求，我不确切知道您必须记录哪种微交易要求，但是我会问一个知道以后的报告要求的人。

–jcolebrand♦
13年1月29日在19:06

我倾向于保留永久数据，例如每年年初都有余额，因此“总计”快照永远都不会被覆盖-列表只是添加到该列表中（即使系统使用了足够长的时间，每个帐户每年累积的总数为1000 [非常乐观]，几乎是无法管理的）。保持许多年度总计将使审核代码可以确认最近几年之间的交易对总计产生了适当的影响[个人交易可能会在5年后清除，但届时将经过审查]。

–超级猫
2013年1月30日14:14

#2 楼

另一方面，我们在会计软件中经常遇到一个问题。释义：

我真的需要汇总十年的数据以了解支票帐户中有多少钱吗？

答案当然不，你不会。这里有几种方法。一种是存储计算出的值。我不推荐这种方法，因为导致错误值的软件错误很难跟踪，因此我会避免这种方法。

更好的方法是我称之为日志快照-汇总方法。在这种方法中，我们的付款和使用是插入内容，我们从不更新这些值。我们会定期汇总一段时间内的数据，并插入计算得出的快照记录，该记录代表快照有效时的数据（通常是快照存在之前的一段时间）。

现在，这不会破坏Codd的规则，因为随着时间的推移，快照可能不太完全取决于所插入的付款/使用数据。如果我们有工作快照，我们可以决定清除10年之久的数据，而不会影响我们按需计算当前余额的能力。

我可以存储计算出的运行总计，并且我绝对安全-受信任的约束确保我的数字始终正确：sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/…

–A-K
13年1月29日在14:19

在我的解决方案中没有极端的情况-受信任的约束不会让您忘记任何事情。我看不到在现实生活中需要知道运行总计的NULL数量的任何实际需求-这些东西彼此矛盾。如果您确实有实际需要，请分享您的sceanrio。

–A-K
2013年1月29日14:34

好的，但是这样就不能像允许多个NULL而不破坏唯一性的数据库那样工作了，对吗？如果清除过去的数据，您的保证也会变坏，对吗？

–克里斯·特拉弗斯（Chris Travers）
13年1月30日在1:29

例如，如果我在PostgreSQL中对（a，b）有一个唯一约束，则我可以为（a，b）具有多个（1，null）值，因为每个null都被视为潜在唯一，我认为从语义上来说这是正确的值.....

–克里斯·特拉弗斯（Chris Travers）
13年1月30日在2:24

关于“我在PostgreSQL中对（a，b）有一个唯一约束，我可以有多个（1，null）值”-在PostgreSql中，我们需要在（a）上使用唯一的局部索引，其中b为null。

–A-K
2014年2月3日，下午1:31

#3 楼

出于性能原因，在大多数情况下，我们必须存储当前余额-否则，即时计算它可能会变得异常缓慢。

我们确实将预先计算的运行总计存储在系统中。为了保证数字始终正确，我们使用约束。从我的博客复制了以下解决方案。它描述了一个清单，该清单本质上是相同的问题：

无论是使用游标还是使用三角形联接，计算运行总计都非常缓慢。进行非规范化，将运行总计存储在列中非常诱人，特别是如果您经常选择它。但是，像往常一样，在进行非规格化时，需要保证非规格化数据的完整性。幸运的是，您可以保证有约束条件的运行总计的完整性–只要所有约束都受信任，您的所有运行总计都是正确的。同样，通过这种方式，您可以轻松地确保当前余额（运行总计）永远不会为负-通过其他方法强制执行也可能非常缓慢。以下脚本演示了该技术。

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))
);
GO
-- 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

在我看来，这种方法的最大局限之一是，要计算特定历史日期的帐户余额仍需要汇总，除非您还假设所有交易都按日期顺序输入（通常是不好的做法）。假设）。

–克里斯·特拉弗斯（Chris Travers）
2014年2月2日，下午1:39

@ChrisTravers对于所有历史日期，所有运行总计始终是最新的。约束保证了这一点。因此，任何历史日期都不需要汇总。如果我们必须更新一些历史行或插入一些过时的行，则我们将更新以后所有行的运行总计。我认为这在postgreSql中要容易得多，因为它具有延迟的约束。

–A-K
2014年2月2日，下午1:47

#4 楼

这个问题问得好。

假设您有一个存储每个借方/贷方的交易表，则设计没有问题。实际上，我曾经使用过以这种方式工作的预付费电信系统。

您需要做的主要事情是确保您在进行借方/贷方SELECT ... FOR UPDATE余额的同时进行余额的INSERT。如果出现问题，这将保证正确的余额（因为整个交易将被回滚）。

正如其他人指出的那样，您需要在特定时间段的余额快照以进行验证给定期间内的所有交易总和与期间开始/结束期间的余额正确无误。编写一个批处理作业，该作业在期末（月/周/日）的午夜运行，以执行此操作。

#5 楼

余额是根据某些业务规则计算得出的金额，因此，是的，您不想保留余额，而是从卡上的交易以及帐户中的余额中进行计算。

您要保持跟踪卡上用于审核和对帐单报告的所有交易，甚至以后来自不同系统的数据。

底线-计算需要在需要时计算的任何值

即使可能有数千笔交易？所以我需要每次重新计算？难不难有表现吗？您能否补充一下为什么会出现这样的问题？

–密瑟
13年1月29日在13:42

@Mithir因为它违反了大多数会计规则，因此使问题无法跟踪。如果您仅更新运行总计，您如何知道已应用了哪些调整？发票被记入一次或两次吗？我们已经扣除了付款金额吗？如果您跟踪交易，您知道答案，但如果跟踪总数，您就不知道答案。

– JNK
13年1月29日在13:58

对Codd规则的引用是它打破了常规形式。假设您以某种方式跟踪交易（我认为这是必须的），并且您有单独的运行总计，如果它们不同意，这是正确的吗？您需要一个单一版本的事实。在/除非它确实存在，否则请先解决性能问题。

– JNK
13年1月29日在13:59

@JNK就是现在的样子-我们会保留交易和总计，因此可以根据需要完美跟踪您提到的所有内容，余额总计只是为了防止我们重新计算每个操作的金额。

–密瑟
13年1月29日在14:01

现在，如果旧数据只能保留例如5年，这不会违反Codd的规则，对吗？那时的余额不仅是现有记录的总和，而且是清除后的先前存在的记录，还是我错过了什么？在我看来，如果我们假设无限的数据保留，那只会违反Codd的规则，这是不可能的。出于我下面要说的原因说，我认为存储一个不断更新的值会带来麻烦。

–克里斯·特拉弗斯（Chris Travers）
2013年1月29日14:03

编程黑洞网