Проектирование баз данных: хронологические данные

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

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

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

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

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

Решения

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

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

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

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

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

Комментарии

я предпочитаю вместо диапазона хранить одну дату

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

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

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

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

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

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

Для второго класса требуется более изощеренные метод хренения (второй или третий), т.к. там типичным запросом является получение значения ДЕЙСТВУЮЩЕГО на дату. IMHO, более удобным является второй способ.

Добавил бы

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

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