Версии в 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. Лицо A вносит изменения и совершает. Это работает, потому что самая последняя версия в базе данных – 4. Теперь она 5. Человек B вносит изменения и совершает. Это не удается, потому что самая последняя версия – 5, а его 4.

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

EDIT 2:

Думаю, я мог бы сделать это, выполнив что-то вроде этого: Person 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. У вас есть отдельная таблица, которая имитирует исходную таблицу + столбец даты / времени для даты ее изменения. Всякий раз, когда запись обновляется, вы вставляете существующее содержимое в таблицу истории непосредственно перед обновлением.

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

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

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

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

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

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

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

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

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

     select * from MyTable where MyReportDate between StartDate and EndDate 

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

     select * from MyTable where EndDate is null 

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

    • Держите объект в двух таблицах:

      1. «employee» хранит идентификатор первичного ключа и любые данные, которые вы не хотите версировать (если они есть).

      2. «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 

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

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

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

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

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

    EDIT :

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

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

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

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

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