sql - SQL 表中的版本控制 - 如何处理？

Question

这是一个包含一些填充数据的虚构场景。出于税收目的，我虚构的公司必须保留历史数据记录。出于这个原因，我在表格中包含了一个版本列。

TABLE EMPLOYEE: (with personal commentary)

|ID | VERSION | NAME       | Position | PAY |
+---+---------+------------+----------+-----+
| 1 |    1    | John Doe   | Owner    | 100 | Started company
| 1 |    2    | John Doe   | Owner    |  80 | Pay cut to hire a coder
| 2 |    1    | Mark May   | Coder    |  20 | Hire said coder
| 2 |    2    | Mark May   | Coder    |  30 | Productive coder gets raise
| 3 |    1    | Jane Field | Admn Asst|  15 | Need office staff
| 2 |    3    | Mark May   | Coder    |  35 | Productive coder gets raise
| 1 |    3    | John Doe   | Owner    | 120 | Sales = profit for owner!
| 3 |    2    | Jane Field | Admn Asst|  20 | Raise for office staff
| 4 |    1    | Cody Munn  | Coder    |  20 | Hire another coder
| 4 |    2    | Cody Munn  | Coder    |  25 | Give that coder raise
| 3 |    3    | Jane Munn  | Admn Asst|  20 | Jane marries Cody <3
| 2 |    4    | Mark May   | Dev Lead |  40 | Promote mark to Dev Lead
| 4 |    3    | Cody Munn  | Coder    |  30 | Give Cody a raise
| 2 |    5    | Mark May   | Retired  |   0 | Mark retires
| 5 |    1    | Joey Trib  | Dev Lead |  40 | Bring outside help for Dev Lead
| 6 |    1    | Hire Meplz | Coder    |  10 | Hire a cheap coder
| 3 |    4    | Jane Munn  | Retired  |   0 | Jane quits
| 7 |    1    | Work Fofre | Admn Asst|  10 | Hire Janes replacement
| 8 |    1    | Fran Hesky | Coder    |  10 | Hire another coder
| 9 |    1    | Deby Olav  | Coder    |  25 | Hire another coder
| 4 |    4    | Cody Munn  | VP Ops   |  80 | Promote Cody
| 9 |    2    | Deby Olav  | VP Ops   |  80 | Cody fails at VP Ops, promote Deby
| 4 |    5    | Cody Munn  | Retired  |   0 | Cody retires in shame
| 5 |    2    | Joey Trib  | Dev Lead |  50 | Give Joey a raise
+---+---------+------------+----------+-----+

现在，如果我想做类似“获取当前编码人员列表”的操作，我不能只做 SELECT * FROM EMPLOYEE WHERE Position = 'Coder'因为这会返回大量历史数据......这很糟糕。

我正在寻找处理这种情况的好主意。我看到一些选项让我眼前一亮，但我敢肯定有人会说“哇，这是一个新手错误，发光......试试这个尺寸:”这就是这个地方的全部意义，对吧？ :-)

想法 1:像这样保留当前版本的版本表

TABLE EMPLOYEE_VERSION:

|ID |VERSION|
+---+-------+
| 1 |   3   |
| 2 |   5   |
| 3 |   4   |
| 4 |   6   |
| 5 |   2   |
| 6 |   1   |
| 7 |   1   |
| 8 |   1   |
| 9 |   2   |     
+---+-------+

尽管我不确定如何使用单个查询来做到这一点，但我确信它可以完成，而且我敢打赌我可以通过相当小的努力来解决这个问题。

当然，每次我插入 EMPLOYEE 表以增加给定 ID 的版本时，我都必须更新此表(或在创建新 ID 时插入到版本表中)。

这样做的开销似乎是不可取的。

想法 2:保留一个存档表和一个主表。在更新主表之前，将我将要覆盖的行插入存档表，然后像往常一样使用主表，就好像我不关心版本控制一样。

想法 3:查找一个查询，该查询添加了类似 SELECT * FROM EMPLOYEE WHERE Position = 'Coder' and version=MaxVersionForId(EMPLOYEE.ID) 的内容。 ...不完全确定我会如何做到这一点。这对我来说似乎是最好的主意，但目前我真的不确定。

想法 4:为“当前”创建一列并添加“WHERE current = true AND ...”

在我看来，肯定有人以前这样做过，遇到过同样的问题，并有洞察力来分享，所以我来收集它! :) 我已经尝试在这里找到问题的示例，但它们似乎专门针对特定场景。

谢谢!

编辑 1:

首先，我感谢所有的答案，你们都说了同样的话 - DATE优于 VERSION NUMBER .我要去的原因之一 VERSION NUMBER是为了简化服务器中的更新过程，以防止出现以下情况

人 A 在他的 session 中加载员工记录 3，它有版本 4。
人 B 在他的 session 中加载员工记录 3，它有版本 4。
人员 A 进行更改并提交。这是有效的，因为数据库中的最新版本是 4。现在是 5。
人员 B 进行更改并提交。这失败了，因为最新版本是 5，而他的版本是 4。

怎么会 EFFECTIVE DATE模式解决这个问题？

编辑 2:

我想我可以通过做这样的事情来做到这一点:
人员A在其 session 中加载员工记录3，其生效日期为1-1-2010，下午1:00，无实验。
B 人在其 session 中加载员工记录 3，其生效日期为 1-1-2010，下午 1:00，无实验。
人员 A 进行更改并提交。旧副本进入存档表(基本上是想法 2)，实验日期为 2010 年 9 月 22 日下午 1:00。主表的更新版本生效日期为 9/22/2010 下午 1:00。
人员 B 进行更改并提交。提交失败，因为有效日期(在数据库和 session 中)不匹配。

Answer 1

我认为你已经走上了错误的道路。

通常，对于版本控制或存储历史数据，您可以执行以下两项(或两项)操作之一。

您有一个单独的表来模拟原始表 + 更改日期的日期/时间列。每当更新记录时，就在更新之前将现有内容插入到历史记录表中。

您有一个单独的仓库数据库。在这种情况下，您可以像上面的 #1 一样对其进行版本化，或者您只需每隔一段时间(每小时、每天、每周...)快照一次即可

将您的版本号与正常的版本号放在同一张表中会有几个问题。首先，表的大小会像疯了一样增长。这将对正常的生产查询施加持续的压力。

其次，它会从根本上增加连接等查询的复杂性，以确保使用每条记录的最新版本。