SQL表中的版本控制-如何处理? [英] Versioning in SQL Tables - how to handle it?

问题描述

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

Here's a fictional scenario with some populated data. For tax purposes, my fictional company must retain records of historical data. For this reason, I've included a version column to the table.

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'，因为那样会返回很多历史数据……这很糟糕.

Now, if I wanted to do something like "Get a list of the current coders" I couldn't just do SELECT * FROM EMPLOYEE WHERE Position = 'Coder' because that would return lots of historical data... which is bad.

我正在寻找可以解决这种情况的好主意.我看到了一些选择，但我确定有人会说:哇，那是菜鸟的错误，发亮……试穿这种尺码:"这就是这个地方的全部，对不对? :-)

I'm looking for good ideas to handle this scenario. I see a few options that jump out at me, but I'm sure someone's going to say "Wow, that's a rookie mistake, glow... try this on for size:" which is what this place is all about, right? :-)

想法编号1:保留具有当前版本的版本表

Idea number 1: Keep a version table with the current version like this

TABLE EMPLOYEE_VERSION:

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

尽管我不确定如何通过单个查询来做到这一点，但我敢肯定它可以做到，而且我敢打赌，我可以用很少的精力就可以解决这个问题.

Although I'm not sure how I'd do that with a single query, I'm sure it could be done, and I bet I could figure it out with a rather small amount of effort.

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

Of course, I would have to update this table every time I insert into the EMPLOYEE table to increment the version for the given ID (or insert into the version table when a new id is made).

那的开销似乎是不希望的.

The overhead of that seems undesireable.

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

Idea number 2: Keep an archive table and a main table. Before updating the main table, insert the row I'm about to overwrite into archive table, and use the main table as I normally would as if I wasn't concerned about versioning.

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

Idea number 3: Find a query that adds something along the lines of SELECT * FROM EMPLOYEE WHERE Position = 'Coder' and version=MaxVersionForId(EMPLOYEE.ID)... Not entirely sure how I'd do this. This seems the best idea to me, but I'm really not sure at this point.

想法4:在当前"列中添加"WHERE current = true AND ..."

Idea number 4: Make a column for "current" and add "WHERE current = true AND ..."

在我看来，肯定是人们以前曾经这样做过，遇到过同样的问题，并且对它有见识可以分享，所以我来收集它！ :)我已经尝试在此处找到该问题的示例，但它们似乎专用于特定情况.

It occurs to me that surely people have done this before, run into these same problems, and have insight on it to share, and so I come to collect that! :) I've tried to find examples of the problem on here already, but they seems specialized to a particular scenario.

谢谢！

首先，我感谢所有答案，并且大家都说过同样的话-DATE比VERSION NUMBER好.我选择VERSION NUMBER的原因之一是简化了服务器中的更新过程，以防止出现以下情况

Firstly, I appreciate all answers, and you've all said the same thing - DATE is better than VERSION NUMBER. One reason I was going with VERSION NUMBER was to simplify the process of updating in the server to prevent the following scenario

人员A在其会话中加载员工记录3，并且其版本为4. 人员B在其会话中加载员工记录3，并且版本4. 人员A进行更改并提交.这行得通，因为数据库中的最新版本是4.现在是5. 人B进行更改并提交.之所以失败，是因为最新版本是5，而他的版本是4.

Person A loads employee record 3 in his session, and it has version 4. Person B loads employee record 3 in his session, and it has version 4. Person A makes changes and commits. This works because the most recent version in the database is 4. It is now 5. Person B makes changes and commits. This fails because the most recent version is 5, while his is 4.

EFFECTIVE DATE模式将如何解决此问题?

How would the EFFECTIVE DATE pattern address this issue?

我认为我可以通过执行以下操作来做到这一点: 人员A在其会话中加载员工记录3，其生效日期为2010年1月1日，下午1:00，无加速. B员工在其会话中加载员工记录3，其生效日期为2010年1月1日，下午1:00，无任何费用. 人员A进行更改并提交.旧副本进入存档表(基本上是概念2)，有效期为9/22/2010 1:00 pm.主表的更新版本的生效日期为9/22/2010 1:00 pm. 人B进行更改并提交.提交失败，因为有效日期(在数据库和会话中)不匹配.

I think I could do it by doing something like this: Person A loads employee record 3 in his session, and it's effective date is 1-1-2010, 1:00 pm, with no experation. Person B loads employee record 3 in his session, and it's effective date is 1-1-2010, 1:00 pm, with no experation. Person A makes changes and commits. The old copy goes to the archive table (basically idea 2) with an experation date of 9/22/2010 1:00 pm. The updated version of the main table has an effective date of 9/22/2010 1:00 pm. Person B makes changes and commits. The commit fails because the effective dates (in the database and session) don't match.

推荐答案

我认为您已经走错了路.

I think you've started down the wrong path.

通常，对于版本控制或存储历史数据，您需要执行两项(或两项)操作之一.

Typically, for versioning or storing historical data you do one of two (or both) things.

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

1. You have a separate table that mimics the original table + a date/time column for the date it was changed. Whenever a record is updated, you insert the existing contents into the history table just prior to the update.

您有一个单独的仓库数据库.在这种情况下，您可以像在上面的#1中那样对它进行版本控制，也可以仅每隔一段时间(每小时，每天，每周..)对其快照一次.

You have a separate warehouse database. In this case you can either version it just like in #1 above OR you simply snapshot it once every so often (hourly, daily, weekly..)

将版本号保存在与普通表相同的表中有几个问题.首先，表的大小将变得疯狂.这将对正常的生产查询造成持续的压力.

Keeping your version number in the same table as your normal one has several problems. First, the table size is going to grow like crazy. This will put constant pressure on normal production queries.

第二，这将从根本上增加连接等的查询复杂度，以确保使用每个记录的最新版本.

Second, it's going to radically increase your query complexity for joins etc in order to make sure the latest version of each record is being used.

这篇关于SQL表中的版本控制-如何处理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！