如何在SQL数据库有效版本记录 [英] How to efficiently version records in an SQL database

问题描述

在至少一个应用程序，我有必要保持的记录旧版本在关系数据库中。当一些应该更新，而是一个新的副本将增加，旧的行会被标记为不可电流。当一些应该被删除，而应该被标记为不及时或删除

In at least one application, I have the need to keep old versions of records in a relational database. When something should be updated, instead a new copy would be added and the old row would be marked as not current. When something should be deleted, it should instead be marked as not current or deleted.

有就是一个简单的例子：只能添加一条记录的新版本在当前时间，取代每一行。这可以用于保存新的数据归档时，以前的记录。对于这一点，我想下面的列添加到每个表：

There is a simple use case of this: New versions of a record can only be added at the current time, superseding one row each. This can be used for archiving previous records when saving new data. For this, I'd add the following columns to each table:

VersionTime datetime -- Time when this versions becomes effective
IsCurrent bool -- Indicates whether this version is the most current (and not deleted)

这是好，如果你只需要知道一个记录的最新版本是什么，只有单独列举一个记录的早期版本。 。指向即时查询，甚至比与第二个变种更痛苦。

This is good if you only need to know what the most current version of a record is, and only enumerate previous versions of a single record separately. Point-in-time queries are even more painful than with the second variant.

一个更通用的变体是这样的：可以随时添加任何指定的记录版本有效时间范围。所以，我可以宣布一个实体的一些设置有效期至2013年底，和它的另一个版本是有效的2014年，还有一个版本将是有效的，从2015年。这可以用于两者，存档旧数据（如上），并提前计划在将来的某个时间使用不同的数据（并保持这个信息作为记录）。对于这一点，我想下面的列添加到每个表：

A more generic variant is this: Versions of records can be added at any time for any specified validity time range. So I could declare that some setting of an entity is valid until end of 2013, and another version of it is valid in 2014, and yet another version will be valid from 2015 on. This can be used to both, archive old data (as above), and plan ahead to use different data at some time in the future (and to keep this information as an archive). For this, I'd add the following columns to each table:

ValidFrom datetime -- Time when this version becomes valid (inclusive)
ValidTo datetime -- Time when this version becomes invalid (exclusive)

第二种方法基本上可以代表第一为好，但它很难知道什么版本是最新的 - 因为你还可以添加版本的未来。此外，ValidFrom / ValidTo设计，可申报重叠的范围，根据定义，具有最高ValidFrom该行应在这种情况下适用。

The second approach can basically represent the first as well, but it's harder to know what version is the most recent - because you can also add versions for the future. Also, the ValidFrom/ValidTo design is able to declare overlapping ranges, and by definition, the row with the highest ValidFrom shall apply in that case.

现在我想知道如何实现一个有效的解决方案来管理和查询这些数据。通常情况下，你可以只写任何SQL查询与任何一种WHERE，GROUP BY和JOIN得到你想要的记录。但随着版本控制应用，你需要考虑每个记录的正确版本。所以，而不是从另一表加入一个记录的每一个版本，一个适当的条件必须被添加到仅选择是在一个给定的时间有效的版本

Now I'm wondering how to implement an efficient solution to manage and query such data. Normally you can just write any SQL queries with any kind of WHERE, GROUP BY and JOIN to get the records you want. But with versioning applied, you need to consider the correct version of each record. So instead of joining every version of a record from another table, an appropriate condition must be added to only select the version that is valid at a given time.

例：

SELECT a, b, c
FROM t1

必须改成：

SELECT a, b, c
FROM t1
WHERE t1.ValidFrom <= :time AND t1.ValidTo > :time
ORDER BY t1.ValidFrom
LIMIT 1

用更复杂的表连接：

More complex with a table join:

SELECT a, b, c
FROM t1
    LEFT JOIN t2 ON (t2.a = t1.a)

必须改成：

SELECT a, b, c
FROM t1
    LEFT JOIN t2 ON (t2.a = t1.a)
WHERE t1.ValidFrom <= :time AND t1.ValidTo > :time
    AND t2.ValidFrom <= :time AND t2.ValidTo > :time

这仍然不能处理重叠的选择时间跨度的正确版本。我可以补充一点，变平重叠的版本时间段一些清理的方法，但我不知道如何有效的，这将是。

This still doesn't handle selecting the right version of overlapping time spans. I could add some clean-up method that flattens out overlapping version time ranges, but I don't know how efficient that would be.

我在寻找创造类（在我的情况下，C＃），提供方法来读取和写入这样的版本记录。写作是比较容易的，因为查询是简单的，易于使用的交易来控制。但查询需要建立一个接受SQL SELECT查询的每个片段，智能生成SQL查询从执行的API。 Thie查询方法应该只接受指定从获取数据的时间一个额外的参数。根据每个实体的有效范围，不同的版本就可以选择每一个。

I'm seeking to create a class (in C# in my case) that provides methods to read and write such versioned records. The writing is relatively easy because the queries are simple and easy to control with transactions. but querying would require building an API that accepts every fragment of an SQL SELECT query and intelligently builds the SQL query to execute from that. Thie query method should only accept one additional parameter that specifies the time to fetch the data from. Depending on each entity's validity range, different versions would be selected of each.

这基本上是我对版本的数据并提供给管理它的API不完整的想法。你已经做了这样的事情，并想告诉我，你怎么想的呢？你有别的想法，运作良好？你能不能给我提供关于如何实现这个API的任何建议吗？虽然我从理论上知道该怎么做，我认为这是一个大量的工作，我无法估计它会如何工作。

These are basically my incomplete thoughts about versioning data and providing an API to manage it. Have you already done such a thing and would like to tell me what you think of it? Do you have another idea that worked well? Could you offer me any advice on how to implement this API? While I theoretically know how to do it, I think it's a lot of work and I can't estimate how well it will work.

推荐答案

如果你需要的旧数据是你的业务逻辑的一部分，然后：结果

If you need old data being part of your business logic then:

• 保存最新版本在主表（插入和更新，删除只会更改状态栏）


• 拍摄快照时更新的详细信息表发生了（一个快照将被创建的任何更新之前）。

• 另一种选择将是的事件采购格局。搜索结果

• Another alternative will be Event Sourcing pattern.
  
  

如果旧数据的只是一个跟踪日志修改，然后：

• 的的实体 - 属性 - 值的做法可能会派上用场。一个
  实施样本的可以在这里找到

• An Entity–attribute–value approach may come in handy. An implementation sample can be found here.

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