История изменений объектов

Автор: Виноградов С. А.

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

В качестве примера, возьмем таблицу Objects, которая содержит код объекта, его наименование, примечание и другие поля (здесь и далее — синтаксис MS SQL):

create table[Objects]
(
  [Id]int not null identity primary key,
  [Code]varchar(120) not null,
  [Name]varchar(120) not null,
  [Note]varchar(250),
  ...
  )

Рассмотрим варианты хранения истории для этой таблицы.

По многим соображениям, бывает необходимо оставить таблицу объектов на текущий момент в неизменном виде, а историю вести в отдельной таблице. Самый простой способ хранения истории — при любом изменении в объекте, добавлять в таблицу запись с новыми значениями объекта и приписывать к этой записи дату изменения. Тогда, структура таблиц Objects и ObjectsHistory будет примерно такой:

create table[Objects]
(
  [Id]int not null identity primary key,
  [Code]varchar(120) not null,
  [Name]varchar(120) not null,
  [Note]varchar(250),
  ...
  )

create table[ObjectsHistory]
(
  [object]int not null references[Objects]([Id]),
  [Code]varchar(120) not null,
  [Name]varchar(120) not null,
  [Note]varchar(250),
  ...
  [DateBegin]datetime not null default(getdate()),
  [DateEnd]datetime not null default('3000-01-01')
  constraint[ObjectHistory]primary key([object], [DateBegin])
  )

Первичный ключ в таблице истории состоит из двух полей: идентификатора объекта из основной таблицы (Object) и даты начала действия истории объекта (DateBegin). Дата окончания действия истории (DateEnd) всегда равна дате начала следующей истории объекта, и используется исключительно для более простых условий выборки.

При вставке записи в основную таблицу, необходимо добавить такую же запись в таблицу истории, где Object = идентификатор_объекта, DateBegin = текущая_дата, DateEnd = максимальная_дата.

При модификации записи в основной таблице, нужно вставить измененную запись в таблицу истории, где Object = идентификатор_объекта, DateBegin = текущая_дата, DateEnd = максимальная_дата. При этом у предыдущей записи с таким же идентификатором в таблице истории необходимо установить DateEnd = текущая_дата.

При удалении записи в основной таблице, у последней записи с таким же идентификатором в таблице истории надо установить DateEnd = текущая_дата.

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

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

заданная_дата beetwen DateBegin and DateEnd

Основным достоинством данного метода является отсутствие изменений для хранения и получения последнего состояния объектов.

Недостаток — повышенное дублирование информации (к примеру, последнее состояние будет одинаково в обеих таблицах) и, соответственно, большой объем базы данных.

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

При использовании автонумеруемого поля в качестве идентификатора объекта, это поле придется вынести в отдельную таблицу ключей Objects, так как в таблице ObjectsHistory оно не будет уникальным:

create table[Objects]
(
  [Id]int not null identity primary key
  )

create table[ObjectsHistory]
(
  [object]int not null references[Objects]([Id]),
  [Code]varchar(120) not null,
  [Name]varchar(120) not null,
  [Note]varchar(250),
  ...
  [DateBegin]datetime not null default(getdate()),
  [DateEnd]datetime not null default('3000-01-01')
  constraint[ObjectHistory]primary key([object], [DateBegin])
  )

Первичный ключ в таблице истории состоит из двух полей: идентификатора объекта из таблицы ключей (Object) и даты начала действия истории объекта (DateBegin). Дата окончания действия истории (DateEnd) равна дате начала следующей истории объекта, и используется для более простых условий выборки.

При вставке объекта, необходимо вначале добавить запись в таблицу ключей и получить идентификатор объекта. Затем надо произвести запись в таблицу истории, где Object = идентификатор_объекта, DateBegin = текущая_дата, DateEnd = максимальная_дата.

При модификации объекта, нужно вставить измененную запись в таблицу истории, где Object = идентификатор_объекта, DateBegin = текущая_дата, DateEnd = максимальная_дата. При этом, у предыдущей записи с таким же идентификатором необходимо установить DateEnd = текущая_дата.

При удалении объекта, для последней записи с таким же идентификатором надо установить DateEnd = текущая_дата.

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

текущая_дата beetwen DateBegin and DateEnd

Условие для выборки на определенную дату:

заданная_дата beetwen DateBegin and DateEnd

Достоинством этого способа является его простота, а также однообразность хранения и получения текущего состояния и истории.

Недостатком будет более сложное получение текущего состояния. Информация по-прежнему остается избыточной — при изменении одного из свойств объекта, он копируется целиком.

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