如何保持现有记录的数据不变 [英] How to keep data for existing records unchanged

问题描述

考虑为流量部门设计一个数据库。有一个表是：违规，合法性和司机。

在法律表中，我们提供了精细的成本，条款号，解释，原因等的属性。

现在，如果我们更改成本&未来的条款号码，我不希望这影响已经存在的记录。但是表违反和合法性已经在一起关系，因此如果查询它们将改变旧记录。

我给了一个简单的例子来说明我的关注，但我想知道解决这种情况的概念。

如何做？

更新1）

我觉得也许我没有解释我想要什么，因为一些答案不是正确的方向。因此，我将解释：
我有实体：

 违规，委员会，决定，会议和法律条款
  

关系1说：委员会 code>会议，并针对违规 x采取决定。
另一个关系说：决定是根据法律条款进行的。

现在，没有详细说明法律条款表，假设它具有罚款，法律条款&参考文献等...如果有一天我们将条款abc的罚款改为$ 100而不是$ 50，那么我们检索一个违规的记录，罚款是基于abc，成本将显示$ 100虽然它应该显示$ 50。​​

解决方案

这是我如何处理这种情况：

1. 首先，我假设您的违规表具有违规发生的日期。


2. 条款和成本实体是分开的所以需要在单独的表格中。


3. Violations 表应该有一个外键引用 主键，用于标识单个子句。这将确保如果一个条款号码发生变化，您仍然会在违规表格中引用正确的条款。


4. cost 外键引用子表表IE每个成本记录与一个子句相关联。


5. Costs 表应该有一个日期列，用于标识价格何时有效。这样一个条款可以有多个价格记录（但是只有一个可以被认为是有效的IE，从最近有效的日期）。


6. 这意味着条款号和价格可以彼此独立地变化，而不影响违规表中的数据的有效性。

模式可能如下所示：

然后当您查询违规行为时，您只需拉出最新条款价格

作为一个例子，假设我有一个单独的子句，其中包含以下数据：

  Id代码说明
 --------------------------- ------ 
 1 101超速
  

该子句可以包含以下成本记录相关联：

  Id成本有效来自ClauseId 
 ------------- ----------------------------------- 
 1 $ 60 01/01/2013 1 
 2 $ 70 01/02/2013 1 
 3 $ 80 01/03/2013 1 
  

所以上述所有费用都与超速条款相关联。如果您想知道当前的成本，您将从与该条款相关联的成本表中选择记录，并且具有最新的有效期，从而给您80美元。

现在想象你有以下违规：

  Id名称ClauseId发生日期
 ------ --------------------------------------------- 
 1本杰明1 16/02/2013 
  

当这次违规发生时，费用将是70美元。因此，为了以正确的价格选择违规记录，您需要构建一个加入违规，子句和成本表的查询。您只会选择从日期起生效的成本小于违规发生日期的记录。

这将给您以下结果：

 名称发生日期描述成本有效从
 ---------------------- -------------------------------------------------- -  
本杰明16/02/2013超速$ 60 01/01/2013 
本杰明16/02/2013加速$ 70 01/02/2013 
  

然后，您只需选择最大有效日期的记录，留下一行，在发生此违规行为时给您正确的价格为70美元。 / p>

Consider designing a database for the traffic department. There is a table for: violations, legalities, and drivers.

In legalities table we have attributes for fine cost, clause number, explanation, reason, etc....

Now if we change the cost & clause number in future, I don't want this to affect the already existing records. But the tables violation and legalities are already in a relationship together, and hence this will change the old records if query for them.

I gave a simple example to illustrate my concern, but I would like to know the concept of solving such a case.

How to do this?

Update 1)

I feel maybe I didn't explain exactly what I want, since some answers are not going in the right direction. Therefore, I will explain: I have entities for:

violation, committee, decision, meeting, and legal terms

Relationship 1 says: committee holds a meeting and takes a decision regarding violation x. The other relationship says: the decision was taken based on the legal terms.

Now, without going into the details of "legal terms" table, assume it has attributes for fine, legal clauses & references, etc... If one day we change the fine for clause "abc" to be $100 instead of $50, then we retrieve a record of an old violation whose fine was based on the clause "abc", the cost will show $100 although it should show $50.

解决方案

Here's how I would handle this situation:

1. First of all I am assuming that your Violations table has a date that the violation occurred. If not it should have.
2. The Clause and Cost entities are separate so need to be in separate tables.
3. The Violations table should have a foreign key reference to the Clauses primary key which identifies the individual clause. This ensures that if a clause number changes, you will still reference the correct clause in your violations table.
4. The Costs table should have a foreign key reference to the clause table I.E. each cost record is associated with a clause.
5. The Costs table should have a date column that identifies when the price is valid from. This way a clause can have multiple price records (but only one would ever be considered 'valid' I.E. the one with the latest valid from date).
6. This means that both the clause number and the price can change independently of each other without affecting the validity of the data in the Violations table.

The schema might look something like this:

Then when querying your violations, you simply pull out the latest clause price that was prior to the date the violation occurred.

As an example, assume I have a single clause with the following data:

Id        Code        Description
---------------------------------
1          101         Speeding

The clause could have the following cost records associated with it:

Id        Cost        Valid From        ClauseId
------------------------------------------------
1          $60         01/01/2013          1
2          $70         01/02/2013          1
3          $80         01/03/2013          1

So all of the above costs are associated with the 'Speeding' clause. If you wanted to know what the current cost was you would select the record from the cost table that is associated with the clause and has the latest valid from date which would give you $80.

Now imagine you had the following violation:

Id        Name        ClauseId        Date Occurred
---------------------------------------------------
1         Benjamin        1            16/02/2013

At the time this violation occurred the cost would have been $70. So in order to select the violation record with the correct price you would want to structure a query that joins the violation, clause and cost tables. You would only select records where the costs valid from date was less than the date the violation occurred.

This would give you the following results:

Name        Date Occurred        Description        cost        Valid From
--------------------------------------------------------------------------
Benjamin      16/02/2013           Speeding          $60        01/01/2013
Benjamin      16/02/2013           Speeding          $70        01/02/2013

You would then simply select the record with the maximum valid from date leaving you with a single row, giving you the correct price of $70 at the time this violation occurred.

这篇关于如何保持现有记录的数据不变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！