Версионность и история данных

При разработке баз данных зачастую требуется обеспечить поддержку версионности и хранения истории объектов. Например, у работника может изменяться должность, у должности в свою очередь может меняться оклад - в многомерном моделировании это называется Slowly changing dimensions(далее SCD) - редко изменяющиеся измерения, то есть измерения, не ключевые атрибуты которых имеют тенденцию со временем изменяться. Всего существует 6 основных типов(методов) SCD, которые определяют как история изменений может быть отражена в модели.

Тип 0

Заключается в том, что данные после первого попадания в таблицу далее никогда не изменяются. Этот метод практически никем не используется, т.к. он не поддерживает версионности. Он нужен лишь как нулевая точка отсчета для методологии SCD.

Тип 1

1 тип - это обычная перезапись старых данных новыми. В чистом виде этот метод тоже не содержит версионности и используется лишь там, где история фактически не нужна. Тем не менее, в некоторых СУБД для этого типа возможно добавить ограниченную поддержку версионности средствами самой СУБД(например, Flashback query в Oracle) или отслеживанием изменений через триггеры.

Достоинства:

• Не добавляется избыточность
• Очень простая структура

Недостатки:

• Не хранит истории

Тип 2

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

Пример:

ID	NAME	POSITION_ID	DEPT	DATE_START	DATE_END
1	Коля	21	2	11.08.2010 10:42:25	01.01.9999
2	Денис	23	3	11.08.2010 10:42:25	01.01.9999
3	Борис	26	2	11.08.2010 10:42:25	01.01.9999
4	Шелдон	22	3	11.08.2010 10:42:25	01.01.9999
5	Пенни	25	2	11.08.2010 10:42:25	01.01.9999

В этом примере в качестве даты конца версии по умолчанию стоит '01.01.9999', вместо которой можно было бы указать, скажем, null, но тогда возникла бы проблема с созданием первичного ключа из ID,DATE_START и DATE_END, и, кроме того, так упрощается условие выборки для определенной даты("where snapshot_date between DATE_START and DATE_END" вместо "where snapshot_date>DATE_START and (snapshot_date < DATE_END or DATE_END is null)".
При такой реализации при увольнении сотрудника можно будет просто изменить дату конца текущей версии на дату увольнения вместо удаления записей о работнике.

Достоинства:

• Хранит полную и неограниченную историю версий
• Удобный и простой доступ к данным необходимого периода

Недостатки:

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

Тип 3

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

	ID	UPDATE_TIME	LAST_STATE	CURRENT_STATE
1	1	11.08.2010 12:58:48	0	1
2	2	11.08.2010 12:29:16	1	1

Достоинства:

• Небольшой объем данных
• Простой и быстрый доступ к истории

Недостатки:

• Ограниченная история

Тип 4

История изменений содержится в отдельной таблице: основная таблица всегда перезаписывается текущими данными с перенесением старых данных в другую таблицу. Обычно этот тип используют для аудита изменений или создания архивных таблиц(как я уже говорил, в Oracle этот же 4-й тип можно получить из 1-го используя flashback archive). Подтипом или гибридом этого варианта(со вторым типом), как мне кажется, следует считать секционирование по признаку текущей версии с разрешенным перемещением строк, но это уже за гранью моделирования и скорее относится к администрированию.

Пример:

Select * from emp


ID	NAME	POSITION_ID	DEPT
1	Коля	21	2
2	Денис	23	3
3	Борис	26	2
4	Шелдон	22	3
5	Пенни	25	2

Select * from emp_history


ID	NAME	POSITION_ID	DEPT	DATE
1	Коля	21	1	11.08.2010 14:12:13
2	Денис	23	2	11.08.2010 14:12:13
3	Борис	26	1	11.08.2010 14:12:13
4	Шелдон	22	2	11.08.2010 14:12:13

Достоинства:

• Быстрая работа с текущими версиями

Недостатки:

• Разделение единой сущности на разные таблицы

Гибридный тип/Тип 6(1+2+3)

Тип 6 был придуман Ральфом Кимболлом(Ralph Kimball) как комбинация вышеназванных методов и предназначен для ситуаций, которые они не учитывают или для большего удобства работы с данными. Он заключается во внесении дополнительной избыточности: берется за основу тип 2, добавляется суррогатн атрибут для альтернативного обзора версий(тип 3), и перезаписываются одна или все предыдущие версии(тип 1).
Пример:

VERSION	ID	NAME	POSITION_ID	DEPT	DATE_START	DATE_END	CURRENT
1	1	Коля	21	2	11.08.2010 10:42:25	01.01.9999	1
1	2	Денис	23	3	11.08.2010 10:42:25	01.01.9999	1
1	3	Борис	26	2	11.08.2010 10:42:25	11.08.2010 11:42:25	0
2	3	Борис	26	2	11.08.2010 11:42:26	01.01.9999	1

В данном примере, например, добавление суррогатного ключа добавляет возможность ссылаться из таблиц фактов на конкретную версию измерения, которая может не принадлежать времени существования самого факта, а индикатор текущей версии может помочь секционировать по текущим версиям(хотя правильнее было бы назвать секционированием по последней версии,т.к. версия может устареть без изменения самой записи). Впрочем индикатор текущей версии можно создать и как виртуальное вычислимое поле, не ухудшая нормализации, если это необходимо именно в таблице(если СУБД поддерживает такие поля, в Oracle они появились в 11-й версии), и как поле в представлении из этой таблицы.
В целом же любая комбинация основных типов SCD относится к гибридному типу, поэтому как их недостатки так и достоинства зависят от конкретной Вашей реализации, но безусловно одно - выбор гибридного типа может быть обусловлен только сложностью Вашей модели и практически всегда(во всяком случае я не знаю случаев, когда может быть иначе) можно обойтись основными 4-мя типами.

Позволю себе добавить несколько советов по реализациям SCD:

• Старайтесь реализовывать механизм изменения записей в хранимых процедурах - категорически нежелательно, чтобы код изменений был разбросан по разным местам, даже если код изменений у вас хранится в четко определенных местах Вашего внешнего приложения;
• Если Вы хотите произвести плавный переход от 1-й модели ко второй, Вы можете поступить так:
  1) изменить таблицу по типу 2 SCD с переименованием, допустим, в table_name_scd2
  2) создать обновляемое представление с названием старой таблицы, которая будет выдавать данные в той же структуре что и старая таблица;
  3) если Вы не все изменения проводите в хранимых процедурах(надеюсь, это временно :) ), которые уже изменили, то создать триггеры, которые будут заполнять новые поля в случаях, если они не устанавливаются запросом(when :new.start_date is null...) и логгировать это, чтобы затем удостовериться, что Вы все изменили
• В случаях использования полей начала и конца версии, помимо использования первичного ключа, включающего в себя идентификатор объекта и даты начала и конца версии, Вам нужно будет для контроля целостности - создать ограничение на непересечение дат версий. Очень хорошо, если Ваша СУБД поддерживает check constraints основанные на недетерминированных функциях, позволяющие сделать это(хотелось бы, кстати, узнать какие СУБД это поддерживают), но если это не так, то Вы можете проверять условие в триггере перед созданием или изменением и вызывать исключение, в случае нарушения. Пример для Oracle:
  

  create or replace trigger T_EMP_CHECK
 before insert or update on emp 
 for each row
declare
    f_ok number;
begin
 select count(1) into f_ok
 from emp e
 where
 e.id=:new.id
 and e.date_start <= :new.date_end
 and e.date_end >= :new.date_start and rownum=1;
 if f_ok>0 then
    raise DUP_VAL_ON_INDEX;
 end if;
end T_EMP_CHECK;

* This source code was highlighted with Source Code Highlighter.

• При переходе с тип 1 на тип 4, Вам достаточно создать триггер before update, в котором будете складировать записи в новую таблицу для архивных записей