Еще раз о логировании изменений и версионировании данных в БД

Question

[Godless]

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

На работе встала задача изобразить софт для филиалов. Не буду вдаваться в детали. Суть примерно такая:
Есть *цать справочников. Некоторые справочники иерархические. Есть таблицы агрегаторы (далее ХХХ), в которых будут данные накапливаться.

Специфика предметной области такова, что справочники могут довольно часто меняться. Причем изменения могут быть как критичные (значит нужно заносить в историю), так и не очень (не заносить). А данные нужно в ХХХ иметь актуальные на дату внесения. Решается это версионированием данных в справочниках со ссылками в ХХХ на данные определенной версии. Еще есть один нюанс — опять же из-за специфики, довольно часто (4-6 раз в год) набор полей в справочниках может измениться (в ХХХ тоже).

Никак не можем определить как же лучше все это хозяйство хранить. Хочется чтобы и кодить было удобно, работало шустро (база заполнится довольно быстро), и для юзеров был простор по функционалу. Не хочется втыкать в программу промежуточный слой для обработки истории и логирования (для филиалов, когда для себя будем разрабатывать, там уже можно — объемы данных больше на порядок). Может быть, мы много хотим? Тогда помогите вычеркнуть лишнее…

Пользователи будут авторизовываться из таблицы в БД (иногда под одной учеткой в АД работают несколько человек). Права на разделы тоже в таблице.

Пока смотрим на такое решение:
1 таблица на справочник — текущие актуальные сведения
2я таблица на справочник — данные с версиями (ну сюда же можно добавить кто менял последний и когда) для ссылок из ХХХ.
+ триггеры или промежуточный слой в программе между софтом и БД.

Поделитесь, пожалуйста, опытом. кто как делает? Это действительно единственный правильный путь в такой меняющейся среде?

ЗЫ: БД будет MS SQL (в филиалах Express).

Answer 1

[Павел Назаров]

Если вы хотите хранить меняющийся справочник — так храните его! Примерно как по первой ссылке:

Справочник (иерархично) -> Элементы справочника (уникальный код элемента, ссылка_на_справочник) -> Значения элементов во временном разрезе, т.е. версии по вашей терминологии (дата_начала, дата_окончания, реальное_значение, ссылка_на_элемент, + аудит по вкусу).

Вот последнюю сущность, собственно значения, и меняйте как вам угодно, имхо. Хотите — только значение (т.е. иногда это будет изменение «задним числом»), а хотите — заводите новую запись с датами.
В таком случае, запросом всегда можно получить реальное значение на любой момент времени.

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

Не совсем понятно, зачем вам текущие актуальные сведения — в чем профит? Ну т.е. если жесткий лоад — проще в памяти поднять, имхо, врядли там такая бесконечность справочников.

Comments

[Godless]

Спасибо за ответ. От таблицы избавимся дополнительной. Одной вроде как достаточно + триггеры.
Про денормализацию думали. Решили, что все таки это зло)

Answer 2

[noonesshadow]

Скорей всего велосипед уже изобретен.

Answer 3

[vlivyur]

Разрабатывал на двух способах.
1. Все изменения в одной таблице: одна строчка-один реквизит (обычно справочники меняют по 1-3 реквизиту за раз, реже — всё). Хранилось: когда, кто, какой реквизит, значение до изменения (varchar, которое в клиенте превращалось в нужное значение, в том числе если там были ссылки на документы/справочники отображалось текущее значение этого об'екта и значение на момент изменения) + в оригинальной таблице было два поля: кто и когда создал (в основном пользователей эта информация интересовала, а история — только при разборках). Вся структура БД была описана метаданными: справочники, документы и их физическое представление. Отсюда при изменении физической структуры генерировался триггер на изменение, который и вносил в таблицу истории значение до изменения. Физического удаления не было — признаком «удалён» и кнопка «показать удалённые». Решение было простым, но сложнее было когда требовалась история по документу: по шапке получалось так же быстро, а по зависимым таблицам — только открыв об'ект и просмотрев построчно. Плюс некоторые неудобства для пользователя (хранится кто когда изменил и значение «до изменения», а не «после изменения»).
2. Немного видоизменённый вариант: В данных хранится текущая строка + кто и когда последний её изменял. При изменении этой строки значение до изменения кто, когда, какой объект теперь уже предпоследним изменил и весь объект на момент изменения (вся строка таблицы в XML). Также всё описано метаданными: по нужным реквизитам просто проставляются признаки «хранить историю», остальное додумывает клиент: генерирует запрос сохранения текущих данных в XML и сохраняет их в таблицу истории, а потом изменяет данные (и обновляет две колонки: кто и когда изменил). Думал будет сложнее первого варианта (с XML да ещё и на SQL не работал), но вроде получилось компактнее + легче было реализовать историю по документам с зависимыми таблицами (всё отображается в одном окне). Справочные значения разворачиваются и хранится текущее значение, соответственно получить на какой объект ссылался тогда документ (и может проследить уже его историю) затруднительно. Отсюда прожорливость (при изменении одного битового поля в таблице с 30 полями сохранит все 30 полей, причём сджойнив все зависимости), ну и XML тоже отнюдь не для скорости был придуман.
В обоих вариантах при реализации можно потерять одно из изменений: создание, последнее изменение или удаление.
Лично мне понравился первый вариант — гибче и проще (кроме запроса на извлечение исторических данных — там очень высокая этажерка), но пользователям оказалось не очень удобно видеть пореквизитные изменения — хотели видеть всю строку (неудобно когда при очередном изменении меняется новый реквизит и приходится текущее значение держать в уме), но вредителей так было легче искать. Во втором варианте придётся думать над отображением каши в зависимых таблицах (все строчки будут в куче и сложно разобрать какая строка истории к какой строке данных относится) и раскраской изменений по сравнению с предыдущим вариантом (хотя может подумать как хранить только изменившиеся реквизиты без остальных и существенных затрат времени на реализацию? там вроде куча проблем автоматически отпадёт).
Про остальные способы хранения изменений — очень хорошая статья.