Версии в SQL-таблицах - как ее обрабатывать?

Здесь вымышленный сценарий с некоторыми населенными данными. Для целей налогообложения моя вымышленная компания должна сохранять записи исторических данных. По этой причине я включил столбец версии в таблицу.

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, чтобы увеличить версию для данного идентификатора (или вставить в таблицу версий при создании нового идентификатора).

Накладные расходы на это кажутся нежелательными.

Идея номер 2: Храните таблицу архива и основную таблицу. Перед обновлением основной таблицы вставьте строку, которую я собираюсь переписать в таблицу архива, и использую основную таблицу, как обычно, так, как будто меня не интересовало управление версиями.

Идея номер 3: Найдите запрос, который добавляет что-то вдоль строк SELECT * FROM EMPLOYEE WHERE Position = 'Coder' and version=MaxVersionForId(EMPLOYEE.ID)... Не совсем уверен, как я это сделаю. Мне кажется, это лучшая идея, но на данный момент я действительно не уверен.

Идея номер 4: Создайте столбец для "current" и добавьте "WHERE current = true AND..."

Мне кажется, что люди действительно делали это раньше, сталкиваются с этими же проблемами и имеют представление об этом, чтобы поделиться, и поэтому я пришел, чтобы собрать это!:) Я уже пытался найти примеры проблемы здесь, но они, похоже, специализируются на конкретном сценарии.

Спасибо!

РЕДАКТИРОВАТЬ 1:

Во-первых, я ценю все ответы, и вы все говорили то же самое - DATE лучше, чем VERSION NUMBER. Одна из причин, по которой я шел с VERSION NUMBER, заключалась в том, чтобы упростить процесс обновления на сервере, чтобы предотвратить следующий сценарий

Лицо A загружает запись сотрудника 3 в свою сессию и имеет версию 4. Лицо B загружает запись сотрудника 3 в свою сессию и имеет версию 4. Лицо А вносит изменения и совершает. Это работает, потому что самая последняя версия в базе данных - 4. Теперь она 5. Лицо B вносит изменения и совершает. Это терпит неудачу, потому что самая последняя версия - 5, а его 4.

Как шаблон шаблона EFFECTIVE DATE устранит эту проблему?

ИЗМЕНИТЬ 2:

Я думаю, что смогу сделать это, сделав что-то вроде этого: Лицо A загружает запись сотрудника 3 в свою сессию, и дата вступления в силу 1-1-2010, 13:00, без опыта. Лицо B загружает запись сотрудника 3 в свою сессию, и ее дата вступления в силу 1-1-2010, 13:00, без опыта. Лицо А вносит изменения и совершает. Старая копия переходит к таблице архива (в основном идея 2) с датой проведения тестирования 22.09.2010 13:00. Обновленная версия основной таблицы имеет дату вступления в силу 22.09.2010 13:00. Лицо B вносит изменения и совершает. Конец не выполняется, потому что эффективные даты (в базе данных и сеансе) не совпадают.

Ответ 1

Я думаю, что вы начали с неправильного пути.

Как правило, для управления версиями или хранения исторических данных вы выполняете одну из двух (или обеих) задач.

У вас есть отдельная таблица, которая имитирует исходную таблицу + столбец даты/времени для даты ее изменения. Всякий раз, когда запись обновляется, вы вставляете существующее содержимое в таблицу истории непосредственно перед обновлением.
У вас есть отдельная база данных хранилища. В этом случае вы можете либо изменить его так, как в # 1 выше ИЛИ вы просто снимете его один раз так часто (ежечасно, ежедневно, еженедельно..)

Сохранение номера версии в той же таблице, что и обычная, имеет несколько проблем. Во-первых, размер стола будет расти как сумасшедший. Это будет оказывать постоянное давление на обычные производственные запросы.

Во-вторых, это радикально увеличит сложность запросов для объединений и т.д., чтобы убедиться, что используется последняя версия каждой записи.

Ответ 2

Что вы здесь называете Медленно меняющимся размером (SCD). Существуют некоторые проверенные методы борьбы с ним:

http://en.wikipedia.org/wiki/Slowly_changing_dimension

Думаю, я бы добавил, что, поскольку никто не назвал это по имени.

Ответ 3

Вот мой предложенный подход, который очень хорошо работал у меня в прошлом:

Забудьте номер версии. Вместо этого используйте столбцы StartDate и EndDate
Введите триггер, чтобы гарантировать отсутствие перекрывающихся диапазонов дат для одного и того же ID и что существует только одна запись с NULL EndDate для той же ID (это ваша эффективная в настоящее время запись)
Поместите индексы на StartDate и EndDate; это должно дать вам разумную производительность.

Это легко позволит вам сообщать по дате:

select *
from MyTable 
where MyReportDate between StartDate and EndDate

или получить текущую информацию:

select *
from MyTable 
where EndDate is null

Ответ 4

Подход, который я разработал для недавней базы данных, заключается в том, чтобы использовать ревизии следующим образом:

Держите объект в двух таблицах:
- "employee" хранит идентификатор первичного ключа и любые данные, которые вы не хотите версировать (если они есть).
- "employee_revision" хранит все важные данные о сотруднике с внешним ключом таблицы employee и внешним ключом "RevisionID" в таблицу под названием "ревизия".
Создайте новую таблицу под названием "ревизия". Это может использоваться всеми объектами в вашей базе данных, а не только служащими. Он содержит столбец идентификатора для первичного ключа (или AutoNumber, или того, что ваша база данных вызывает такую вещь). Он также содержит столбцы EffectiveFrom и EffectiveTo. У меня также есть текстовый столбец таблицы - entity_type - для удобства чтения человеком, которые содержат имя первичной таблицы изменений (в данном случае "сотрудник" ). Таблица ревизий не содержит внешних ключей. Значение по умолчанию для EffectiveFrom - 1 января-1900, а значение по умолчанию для EffectiveTo - 31 декабря-9999. Это позволяет мне не упрощать запрос даты.

Я уверен, что таблица ревизий хорошо проиндексирована (EffectiveFrom, EffectiveTo, RevisionID), а также (RevisionID, EffectiveFrom, EffectiveTo).

Затем я могу использовать объединения и простые сравнения, чтобы выбрать подходящую запись для любой даты. Это также означает, что отношения между объектами также полностью версируются. На самом деле, мне полезно использовать табличные функции SQL Server для очень простого запроса любой даты.

Вот пример (предполагая, что вы не хотите указывать имена сотрудников, чтобы, если они меняют свое имя, это изменение исторически).

--------
employee
--------
employee_id  |  employee_name
-----------  |  -------------
12351        |  John Smith

-----------------
employee_revision
-----------------
employee_id  |  revision_id  |  department_id  |  position_id  |  pay
-----------  |  -----------  |  -------------  |  -----------  |  ----------
12351        |  657442       |  72             |  23           |  22000.00
12351        |  657512       |  72             |  27           |  22000.00
12351        |  657983       |  72             |  27           |  28000.00

--------
revision
--------
revision_id  |  effective_from  |  effective_to  |  entity_type
-----------  |  --------------  |  ------------  |  -----------
657442       |  01-Jan-1900     |  03-Mar-2007   |  EMPLOYEE
657512       |  04-Mar-2007     |  22-Jun-2009   |  EMPLOYEE
657983       |  23-Jun-2009     |  31-Dec-9999   |  EMPLOYEE

Одно из преимуществ хранения метаданных ревизии в отдельной таблице состоит в том, что ее легко применять к всем вашим объектам. Другим является то, что его легче расширить, включив в него другие вещи, такие как ветки или сценарии, без необходимости изменять каждую таблицу. Моя основная причина в том, что он сохраняет ваши основные сущности таблицы ясными и незагроможденными.

(Данные и пример выше являются вымышленными - моя база данных не моделирует сотрудников).

Ответ 5

Идея 3 будет работать:

SELECT * FROM EMPLOYEE AS e1
WHERE Position = 'Coder'
AND Version = (
    SELECT MAX(Version) FROM Employee AS e2
    WHERE e1.ID=e2.ID)

Вы действительно хотите использовать что-то вроде даты, которое намного проще программировать и отслеживать, и будет использовать ту же логику (что-то вроде столбца EffectiveDate)

ИЗМЕНИТЬ

Крис абсолютно прав в том, чтобы переносить эту информацию из вашей производственной таблицы на производительность, особенно если вы ожидаете частых обновлений. Другим вариантом было бы сделать VIEW, который отображает только самую последнюю версию каждой персоны, которую вы строите из этой таблицы.

Ответ 6

Вы вызывающе делаете это неправильно. Хранение базы данных сладко требует, чтобы у вас был минимальный объем данных в ваших производственных таблицах, которые вам нужны. Неизбежно сохраняя исторические данные с помощью live, добавляет избыточность, которая будет усложнять запросы и медленную производительность, плюс ваши преемники будут выглядеть действительно искаженными, прежде чем отправлять их в DailyWTF!

Вместо этого создайте копию таблицы - например, EmployeeHistorical, но с идентификационным столбцом, не установленным как идентификатор (вы можете добавить дополнительный новый столбец идентификаторов и столбец с отметкой даты). Затем добавьте триггер в таблицу Employee, которая запускается при обновлении и удалении, и выписывает копию полной строки в таблицу "Историческая". И пока вы набираете идентификатор пользователя, который делает редактирование, часто пригодится для целей аудита.

Как правило, когда я делаю это в активной таблице, я пытаюсь создать историческую таблицу в другой базе данных, так как, среди прочего, это уменьшает фрагментацию (и, следовательно, обслуживание) в вашей первичной базе данных и упрощает обработку резервных копий - в качестве архивов может расти очень большой.

Ваши проблемы, связанные с обработкой изменений, должны обрабатываться с помощью обычных транзакций транзакций и механизмов блокировки. Кодирование adhoc hacks для эмуляции такого себя всегда занимает много времени и подвержено ошибкам (какое-то краевое условие, о котором вы не думали всегда всплывали, и правильно писать блокировки, вам действительно нужно grok sempahores, который явно не является тривиальным)