在以下示例中,
Order
-------
ID (int)
CreatedAt (smalldatetime)
....
OrderItems
-------
ID (int)
OrderID (int)
Quantity (int)
UnitPrice (decimal)
CalculationUnit (int)
TotalItemPrice (decimal)
....
我有一个很大的难题,我应该在哪里以及如何跟踪TotalOrderPrice,我主要关心的是速度和数据一致性。
a) TotalOrderPrice可以存储在Orders表中,并应在相关OrderID 的每次OrderItem更改时更新
这是否会导致数据不一致,因为数据是"重复的"?
b) 我可以有一个视图,可以保存汇总的TotalItemPriceValues,如
OrderTotal
------------
ID (int)
TotalOrderprice (decimal)
在扩展应用程序时,这可能是一个潜在的问题吗?
c) 或者,我可以保持原始设计的原样,在业务逻辑中计算OrderTotalPrice。
这是否会降低性能,因为必须检索所有订单项才能获得总订单价格?
我知道没有灵丹妙药,但由于我没有大量的数据可供测试,我只想做事实核查,看看在这里找到解决方案的合适理由是什么?
我建议不要维护需要频繁更新的计算列,而是在应用程序需要时按需计算查询中的订单总额
SELECT t1.ID, t2.OrderTotalPrice
FROM Order t1
INNER JOIN
(
SELECT OrderID, SUM(TotalItemPrice) AS OrderTotalPrice
FROM OrderItems
GROUP BY OrderID
) t2
ON t1.ID = t2.OrderID
这避免了必须维护计算列的问题,这使得管理数据库变得更加容易。反对计算列的一个有力论据是,它并没有真正为数据库节省任何工作。相反,它总是需要维护,而按需计算列只需要在实际需要时完成。
订单不会包含数百万个仓位,所以速度应该不是你必须担心的问题。
表OrderItems
包含Quantity
、UnitPrice
和TotalItemPrice
。这似乎已经是多余的了。TotalItemPrice
不总是Quantity
xUnitPrice
吗?假设UnitPrice
已经是要支付的总价(而不是必须添加增值税才能获得TotalItemPrice
的净价)。只要任何商品折扣已经包括在内。例如,如果有另一列item_discount_percent
,我们可能会得到数字太多的结果,例如105.987002。在本例中,订单是否包含105.98或105.99?我们可能希望将该值存储在TotalItemPrice
中,然后使其清楚。(为了确保新的软件版本仍然会打印完全相同的顺序。)因此,只有在某些计算可能导致价格超过小数点后两位的情况下,才可以使用此列。
对于您的问题和TotalOrderPrice
,我们可以采用相同的想法:如果价格只是订单所有TotalItemPrice
的总和,那么不要存储它。如果需要进行一些计算,导致小数位数过多(例如order_discount_percent
),您可能应该存储该(四舍五入/截断)值。
我会考虑数据的访问模式,因为这决定了相关的利弊。
您需要多长时间:
- 将谓词放在订单总量上(计算密集型,除非存储总量)
- 按订单总额排序(计算密集型,除非存储总额)
- 修改订单总额(计算密集型,如果存储总额,则可能是错误原因)
如果订单在创建后从未被修改,并且您经常在总数上放置谓词或按其排序,那么我有信心将总数存储在订单表中
如果订单经常被修改,但您很少需要在总数上放置谓词或按其排序,那么我有信心不存储总数
对你来说,正确的方法在很大程度上取决于这两个极端之间的平衡,以及你愿意在糟糕的性能或错误的数据中采取的风险。
这是我的第二个答案,与我的第一个答案非常不同。。。
通常您会避免数据库中的冗余,因为这会导致不一致。例如,如果有一天你看到订单存储的TotalOrderPrice
与你根据头寸计算的不匹配,你会怎么办?为了避免这种不一致,我们避免了冗余。
然而,在数据仓库中,为了更快地访问数据,您需要冗余。这意味着您可以有一个包含纯Order
和OrderItems
表的订单系统,并有一个每隔一段时间更新的数据仓库系统,并且有一个带有TotalOrderPrice
列的Order
表。
进一步思考。。。您的系统中的订单是否发生过变化?如果不是,为什么不存储您打印的内容,即冗余存储TotalOrderPrice
。(您可以使用一些数据库机制来防止订单被部分删除或更新,以使其更加安全。)如果稍后TotalOrderPrice
与您根据头寸计算的结果不匹配,则这甚至表明您在编写订单时的软件存在问题。因此,存储TotalOrderPrice
是一个优势,它可能会突然为我们提供检测此类错误并在会计中进行更正的机会。
话虽如此:通常情况下,订单是书面的,之后不会更改。由于不会发生任何更改,您可以轻松地将TotalOrderPrice
存储在订单表中,并具有稍后查看您发送/打印的订单价格和更快地检索价格的优势。
一般来说,我的观点是,在需要之前,应该避免违反规范化规则。这意味着避免数据冗余以避免更新异常,并动态计算。我见过很多糟糕的数据库被创建,因为开发人员担心有一天数据库可能无法应付应用程序负载;事实上,在一个设计良好、索引良好、维护良好的数据库中,这种情况很少见。如果数据库的设计和维护正确,那么RDBMS是处理事务系统中大量规范化数据的一个非常好的工具。
不过,这并不意味着你需要在应用程序逻辑中进行计算——事实上我会避免这种情况。相反,创建一个进行计算的视图(看起来像Tim Biegeleisen在回答中提出的查询)。如果以后某个时候你发现这不太好扩展,你可以更改表和视图,以及填充该表的任何内容-如果需要更改,这将最大限度地减少对应用程序的干扰。如果表是通过存储过程填充的,那么您可能根本不需要对前端应用程序逻辑进行任何更改,就可以从动态计算切换到预先计算。