Большинство объектов, хранящихся в базе данных, могут меняться с течением времени. Например, человек может сменить фамилию, паспорт, место жительства. У различных объектов учета может поменяться цена, количество, стоимость. К сожалению, почти все современные СУБД не хранят историю этих изменений и не позволяют получить состояние объектов на определенную дату. Об этом должен позаботиться проектировщик базы данных.
В качестве примера, возьмем таблицу 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
Достоинством этого способа является его простота, а также однообразность хранения и получения текущего состояния и истории.
Недостатком будет более сложное получение текущего состояния. Информация по-прежнему остается избыточной — при изменении одного из свойств объекта, он копируется целиком.
Существует масса других способов хранения истории, начиная от простого журнала, в котором, при любом изменении записи в таблице, пишутся прежние значения полей, преобразованные в строку. Но далеко не все эти способы пригодны для легкого и быстрого получения состояния объектов на любую дату. Здесь были рассмотрены лишь наиболее простые и эффективные варианты представления истории, которые вполне подходят для практического применения.