在所有情况下，订单项价格的数据快照都比计算结果好吗？ [英] Are data-snapshots of line item prices better than calculations in all cases?

问题描述

我经常看到line_item表的订单或发票复制了其他表中的一个或多个字段，以便在下订单时拍摄客户的产品订单。

I've often seen line_item tables for orders or invoices that copy one or more fields from other tables in order to take a snap-shot of a customer's product order when it was placed.

但是，在我的模式中，我可以生成订单视图而不复制数据。因此，查找订单/产品/价格数据要贵一些，但可以节省复制/插入上的时间，空间和冗余。我知道复制/插入是一次性事务，而查找将需要很多次-但是，我只处理给定表中的成千上万条记录，并且我不希望性能如此一个问题。

In my schema, however, I can generate a view of an order without copying data. So looking up the order/product/price data is a little more expensive, but I save time, space and redundancy on the copy/insert. I understand the copy/insert is a one-time transaction, whereas the look-up will be required many times - however, I'm only dealing with 10s of thousands of records in a given table and I don't expect performance to be an issue.

因此，因为a）我的模式支持无快照的准确查找，并且b）我对外观的需求不强优化，我认为运行计算而不是拍摄快照是有意义的。还是我缺少某些东西，在这种情况下我应该经常拍摄快照吗？

这里是查找内容的一个示例计算如下：

Here's an example of what the look-up calculation would look like:

# display order items for a particular order on a particular date

# get order, products and base prices from order_id

order_products = SELECT * FROM order_has_product ohp
                          INNER JOIN price ON (price.product_id = ohp.product_id)
                          INNER JOIN order ON (order.id = ohp.order_id)
                          WHERE order_id = ?


# calculate price of each product at order.datetime_opened

for op in order_products:

    tax = SELECT SUM(tax.rate) FROM product_has_tax pht
             INNER JOIN tax ON (tax.id = pht.tax_id)
             WHERE pht.product_id = op.product_id
                 AND tax.date_start <= op.datetime_opened
                 AND tax.date_end >= op.datetime_opened

    discount_product = SELECT SUM(discount.rate) FROM product_has_discount phd
             INNER JOIN discount ON (discount.id = phd.discount_id)
             WHERE phd.product_id = op.product_id
                 AND discount.date_start <= op.datetime_opened
                 AND discount.date_end >= op.datetime_opened

    discount_customer = SELECT SUM(discount.rate) FROM customer_has_discount chd 
             INNER JOIN discount ON (discount.id = chd.discount_id)
             WHERE chd.customer_id = op.customer_id
                 AND discount.date_start <= op.datetime_opened
                 AND discount.date_end >= op.datetime_opened
                 AND (chd.date_used_limited IS NULL OR chd.date_used_limited = op.datetime_opened)

    discount = discount_product + discount_customer

    price = op.price * (1-discount) * (1+tax)

推荐答案

在我处理的在线销售应用程序中，我们始终在下订单时将计算出的税金和价格复制到每个订单中;这是您的快照选项。我们从未重新计算过。为什么？

In the online sales application I worked on, we always copied calculated taxes and prices to each individual order when it was placed; this is your "snapshot" option. We never recalculated it. Why?

• 价格变化；快照捕获了订购产品时的价格，而不是现在的价格。


• 表发生了变化。您依靠价格的每个生产者和消费者来了解该视图，并且仅使用该视图。永远。如果提供了快照，将来的数据使用者就不会在乎它的计算方式。


• 模式会发生变化。安大略省正在改变其计算营业税的方式以及对哪些产品征税。这会破坏视图，但不会破坏快照。


• 规则更改。我们必须为客户提供一种替代优惠券和价格匹配等规则的方法。您可以通过覆盖快照的价格来做到这一点，而不能使用视图来实现。

• Prices change; the snapshot captures the price at the time the product was ordered, not what it is now.
• Tables change. You are relying on every producer and consumer of your prices to know about the view and use only the view. Forever. If you provide the snapshot, future data consumers don't care how it was calculated.
• Schemas change. Ontario is changing how it calculates sales tax and what products are taxed. This breaks the view, but not a snapshot.
• Rules change. We had to provide a way for clients to override rules for things like coupons and price-matching. You can do this by overriding the snapshotted price, you can't do it with views.

这篇关于在所有情况下，订单项价格的数据快照都比计算结果好吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！