从一个存储历史记录的表中检索记录的最佳方法是什么？ [英] What is the best approach to retrieve records from one table that stores it's history?
问题描述
我有一个虚拟表 Users，我们称它为U，另一个表是 DataFromUsers，DU。我会不时地将与一个U相关的新记录保存到DU中，现在我们有2个指向同一用户U的记录。这时我将在DU中有很多条目类似于这种情况。
I have one dummy table 'Users', let's call it U, and another table 'DataFromUsers', DU. From time to time i'll save a new record into DU, related to one U, and now we have 2 records pointing to the same user U. It'll come the time when i'll have lots of entries in DU resembling to this condition.
我能看到的解决此问题的最佳方法是始终存储它的插入日期，一旦我关心的是U的最新DU，并且只保留旧的用于分析目的的条目。
The best approach i can see to solve this problem is always storing it's insertion date, once all i care is the most recent DU from U, and am only keeping the old entries for analytical purposes.
另一种方法是让FK指向U，直接指向DU的最新记录，但这将需要始终更新两个表，而且我不知道这是否是一个好主意（在可维护性和性能方面）。
Another way would be having a FK in U pointing straight to the most recent record from DU, but that would create a need to always update two tables, and i don't know whether this is a good idea or not (in matters of maintainability and performance).
推荐答案
在一般情况下，您将在子表中包括一个整数版本字段。 1
In general case, you would include an integer version field in the child table.1
但是在SQL Server的特定情况下，包括显式父表中的外键将允许您创建索引视图，并有效地在父级和最近的子级之间缓存JOIN。请仔细考虑您是否真正需要它，如果需要，请非常小心，不要让此FK不同步！ 2
But in the specific case of SQL Server, including the explicit foreign key in the parent table would allow you to create an indexed view and effectively cache JOIN between the parent and most recent child. Consider carefully whether you actually need that, and if you do, be very careful not to let this FK go out of sync!2
1 如果数据库服务器的时钟不同步，则日期/时间可能会出现问题，但您可以（仅）将其包括在内以提供参考
1 Date/time can be problematic if your database server's clock ever goes out of sync, but you can include it (just) for informative purposes in addition to the integer version.
2 您必须在插入新子项之前锁定父项，以避免在并发环境 中出现竞争条件，并且您必须确保所有客户都遵循该条件。幸运的是，仅尝试更新父行（以设置新的FK值）也将其锁定。
2 You'd have to lock the parent before inserting new child, to avoid race conditions in concurrent environment and you'd have to make sure all clients follow that. Fortunately, the mere attempt to UPDATE the parent row (to set the new FK value) also locks it.
这篇关于从一个存储历史记录的表中检索记录的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
