Данная статья рассматривает создание SQL-справочников, в которых требуется хранение истории изменений.
Что это может быть? Ставки налогов, наименование контрагента, ... да мало ли что! То, что может измениться, и при работе в прошедшем периоде требуется видеть именно то состояние справочника, которое было на тот момент.
Возможно как минимум три варианта таких справочников.
Теневая таблица
При реализации этого способа в базе данных создаются две таблицы одинаковой структуры: собственно справочник, и его история. Помимо основных полей (ID - идентификатор записи, Value - значение справочника) в обе таблицы добавляется счетчик версий (Version), а в таблицу истории (или в обе) можно добавить еще поля "Дата изменения" и "Кто изменил".
Как все работает
При вставке/изменении записи в справочнике, триггер увеличивает счетчик версий, после чего копирует запись из справочника в таблицу истории.
В документы вставляется не только идентификатор записи справочника ID, но и его счетчик версии Version. Теперь при выводе документа на экран надо линковать документ не со справочником, а с его историей по полю Version.
Плюсы:
- структура таблицы справочника практически не меняется
- редактирование справочника остается прозрачным для пользователя, он даже не подозревает, что его исправления сохраняются
- в документе отображается именно та строка справочника, которая была на момент создания документа (но в этом есть и минус)
Минусы:
- структура базы получается более сложная
- при добавлении полей в справочник, надо не забыть изменить также таблицу истории и триггер, который ее заполняет
- если пользователь сохраняет изменения несколько раз подряд, то все эти промежуточные версии сохранятся в таблице истории
- в документе отображается именно та строка справочника, которая была на момент создания документа, т.е. если в справочнике была неправильная информация (например, наименование контрагента), то для ее исправления в документе, необходимо после исправления справочника обновить номер версии записи справочника в документе
- сложно отобразить вид справочника на конкретную дату
Все в одной таблице
В таблицу справочника добавляется суррогатный первичный ключ, он же - номер версии, и две даты: начала и окончания действия.
При редактировании справочника пользователь должен помимо своих исправлений указать также дату начала действия внесенных исправлений.
Как все работает
При вставке первой записи в справочник срок окончания ее действия ставится равным максимально допустимой в системе дате (например, 31.12.9999).
Если при сохранении изменений пользователь указывает новую дату действия, то триггер делает копию существующей записи, меняет в ней дату
окончания действия на дату, указанную пользователем, сохраняет измененную пользователем запись и увеличивает в ней номер версии.
При вставке в документ возможны варианты: либо указывается идентификатор справочника ID, тогда при выводе документа система ориентируется на диапазон дат действия записи справочника, либо версию записи справочника, тогда система ориентируется на нее. Последний вариант представляется более грамотным.
Плюсы:
- можно внести изменения, которые начнут действовать в будущем, например, со следующего месяца
- можно показать состояние справочника на любую дату
- промежуточные изменения нигде не сохраняются
- не нужно обновлять версию справочника в документе при исправлении справочника
Все в одной таблице - вариант 2
По сути, это предыдущий вариант, но когда срок действия четко ограничен началом и концом месяца, квартала, года, ...
Тогда вместо двух дат достаточно одной, которую можно хранить в упакованном виде.
Например, если речь идет о месяце, то можно хранить целое число (12 * Год + Месяц).
Плюсы и минусы - как у предыдущего варианта.
