数据变化及其对历史数据的影响 [英] Changes in data and their effect on historical data

问题描述

假设您有一个包含客户和订单的简单CRM系统。如果客户更改了姓名，您是否希望旧的历史订单也获得新的名称，还是将其复制到订单上的字符串值以保持准确性？

Say you have an simple CRM system with customers and orders. If a customer changes his name, will you prefer that the old historical orders get the new name as well, or do you copy it to a string value on the order to preserve the accuracy?

我怀疑是否存在涵盖所有情况的答案，但是我在努力确定我可以在哪里接受要更改的历史数据以及在哪里我不能接受它。通常，我一直认为，如果可以打印，则必须稍后再打印，因此它不应更改。

I doubt there is an answer that covers all situations, but I'm struggling with deciding where I can accept the historical data to be changed and where I cannot accept it. Generally I'm using the thought that if it can be printed, it must be printable again later and therefore it should not change.

很高兴听到您的想法。

Happy to hear what you think...

（如果此问题在其他地方，请指明道路...）

推荐答案

您不能更改这样的历史数据，尤其是在某些天可能需要打印并移交给法院的事情上。

You can't allow historical data like that to be changed, especially on things that you might someday be required to print and turn over to a court of law.

通常，我一直认为如果可以打印，则必须稍后可以再次打印
，因此不应更改。 / p>

Generally I'm using the thought that if it can be printed, it must be printable again later and therefore it should not change.

这不是一个不好的开始。但是一些印刷品预计会随着时间而改变。电话簿，交叉目录，帐单邮寄地址。您要允许更改包含当前帐单地址的列，但您不希望更改包含订购时的帐单地址的列。您希望当前价格随时间变化，但定单时的价格不应随时间变化。

That's not a bad start. But some printed things are expected to change over time. Telephone books, criss-cross directories, billing addresses. You want to allow the columns that contain "current billing address" to change, but you don't want columns that contain "billing address at the time of an order" to change. You want "current price" to change over time, but "price at the time of an order" should not change over time.

简而言之，您需要先了解每一列的含义，然后才能决定是否对其进行级联更新。

In short, you need to know what each column means before you can decide whether to cascade updates to it.

如果您允许定购时的价格随时间变化，那么您将随时间更改定单总额-我确信您的会计师会对此感到不满。

If you allow "price at the time of an order" to change over time, you'll change the order totals over time--something I'm sure your accountants would throw a hissy fit about.

有几种方法可以防止更改历史数据。

There are several ways to prevent changes to historical data.

• 不要使用外键引用级联更新或删除。
  这实际上始终是必要条件。是否可以完全使用外键引用取决于应用程序。


• 让用户界面填充在订单的
  时开账单地址之类的内容，也许是通过选择并复制客户帐单地址表来完成的。这不会重复数据，无论如何也不会在关系方面。 重复数据的意思是具有相同含义的相同值。当您将当前的帐单邮寄地址复制到订购时的帐单邮寄地址列中时，即在更改其含义。


• 为源表加上时间戳。例如，客户帐单
  地址表可能还包含表示
  使用此帐单地址处理该日期或之后的订单的列（起始日期为
  ）和将此帐单地址用于在此日期之前
  下的订单（结束日期）。您可以将订单加入该表；订单的日期
  将确定在
  订单时使用的行（例如 ... JOIN customer_billing_addresses c ON c.start_date< = orders.order_date ANDorders.order_date< c.end_date ... ）。
  您需要进行仔细的设计，以确保没有重叠的
  期间，并且严格控制从客户帐单地址中删除行。 （这几乎是完全禁止的。）

• Don't use foreign key references that cascade updates or deletes. This is virtually always a requirement. Whether you can use foreign key references at all is application-dependent.
• Let the user-interface populate things like "billing address at the time of an order", maybe by selecting and copying from a table of customer billing addresses. This doesn't duplicate data, not in the relational sense anyway. Duplicate data means "the same values with the same meaning". When you copy the current billing address to the column "billing address at the time of an order", you're changing its meaning.
• Timestamp your source tables. A table of "customer billing addresses" might, for example, also contain the columns that mean "use this billing address for orders placed on or after this date" (a start date) and "use this billing address for orders placed before this date" (an end date). You'd join orders to this table; the date of the order would determine which row was used at the time of the order (like, ...JOIN customer_billing_addresses c ON c.start_date <= orders.order_date AND orders.order_date < c.end_date...). You'll need careful design to guarantee there are no overlapping periods, and that deleting rows from customer billing addresses is tightly controlled. (That is, almost entirely prohibited.)

无论采用哪种方法，请认真考虑谁应该有权更改历史表中的数据。一旦订单发货，这意味着几乎没有人。

Whatever the approach, think hard about who should have permissions to change data in the historical tables. Once an order ships, that means just about nobody.

这篇关于数据变化及其对历史数据的影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！