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

Материал этой статьи послужил основой для одной из глав книги "СУБД для программиста. Базы данных изнутри". Статья также напечатана в "Мир ПК" №5 2007 г. Журнальный вариант статьи на сайте издания

Содержание

Физический смысл
Примеры использования
Решения
Хранение даты состояния
Хранение интервала
Хранение номера периода (интервала)

Физический смысл

Состояния объекта, зафиксированные в моменты времени.

Примеры использования

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

Решения

Хранение даты состояния

Моделирование пропущенных периодов осуществляется внесением фиктивной записи с датой начала периода и пустыми значениями атрибутов.

Выборка множества объектов по состоянию на заданную дату:

SELECT *
FROM Документы
WHERE "Дата последнего изменения" =
        (SELECT MAX("Дата последнего изменения")
           FROM Документы
           WHERE "Дата последнего изменения" <= Заданная_дата)

Запрос получается относительно "тяжелый", что может привести к некоторым проблемам и необходимости дополнительной и специфичной для конкретной СУБД оптимизации при большом числе записей.

Преимущества

Простота
Отсутствие избыточности
Быстрая вставка новых записей

Недостатки

Необходимость относительно "тяжелых" соединяющихся на себя (self join) вложенных запросов для поиска
Необходимость введения NULL для атрибутов или дополнительного поля-флага при моделировании пустых периодов

Рекомендации: интенсивная вставка записей, данные протоколирования, отсутствие частых массивных запросов по периодам.

Хранение интервала

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

Создает сложности для моделирования открытых периодов. Например, актуальная цена действует с момента ее утверждения и до неизвестного пока момента утверждения новой. Для решения проблемы можно использовать фиктивные значения минимальной и максимальной даты, поддерживаемой СУБД.

Дает возможность извлечения данных простым запросом.

SELECT *
FROM Документы
WHERE Заданная_дата BETWEEN "Начало интервала" AND "Окончание интервала"

Преимущества

Простота и эффективность запросов

Недостатки

Накладные расходы на поддержание непротиворечивости
Более медленная скорость вставки
Дополнительные сложности с открытыми периодами

Рекомендации: интенсивные и/или массивные запросы поиска, невысокие требования к скорости вставки, допустимость использования процедурного расширения конкретной СУБД.

Хранение номера периода (интервала)

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

Запрос в общем случае выглядит следующим образом:

SELECT *
FROM "Хозяйственные операции"
WHERE "Номер периода" =
        (SELECT "Номер периода"
           FROM Периоды
           WHERE Заданная_дата BETWEEN Начало AND Окончание)

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

Преимущества

Меньшая избыточность за счет унификации использования интервалов (периодов) для разных типов сущностей
Простота запросов
Разнесение логики хранения периодов и регистрируемых объектов по разным таблицам

Недостатки

Накладные расходы на поддержание непротиворечивости
Более медленная скорость вставки

Рекомендации: бухгалтерский, управленческий учет.

Сергей Тарасов, март 2005