Здравствуйте!

Интересует, доводилось ли кому-нибудь проектировать базу данных таким образом, чтобы в программе можно было выбрать дату и посмотреть полное состояние всей системы на это время без артефактов, естественно без возможностей изменения данных? Как будто был произведён откат, но без использования отката, т.е. на уровне данных.

При чём в режиме "исторического просмотра" должны полноценно работать все функции системы (просмотр, фильтрация, сортировки и т.д.), кроме создания/изменения данных.

Кот Матроскин,

Жаль... А я думал, может есть готовая к употреблению методология.

Dimitry SibiryakovЕсть. "Flashback query" называется.

Не, ретроспективные запросы -- это возможности конкретной СУБД, хотя было бы круто, если можно было бы включить "ретроспективный режим" на всё подключение (например, указать временной штамп прям в строке соединения), то это бы решало задачу.

Но в целом интересует именно решение на уровне схемы базы данных, в отрыве от конкретной СУБД (может с некоторыми оговорками, допустим, чтобы это работало как минимум в MS SQL, Oracle, Postgre SQL).

Я себе это как вижу. В каждой таблице добавить поле типа HistoryId, которое ссылается на оригинальную запись. При любом изменении записи, создаётся копия со значением HistoryId, указывающую на оригинал. На уровне организации доступа к данным, записи с заполненным полем HistoryId игнорируются. В режиме ретроспективы, всегда берётся самая свежая запись, с датой изменения меньше указанного временного штампа. Не понятно только как толком восстанавливать связи, с учётом ретроспективы.

SERG1257Бакапы, логи. Вы платите только за лишнее дисковое пространство.

Вопросы использования дискового пространства и производительности в данном случае второстепенны. Озвучена задача, чтобы в программе в любой момент времени можно было посмотреть состояние системы в указанный момент времени. Совсем старые записи можно архивировать (допустим, годовые). Больше интересует просмотр состояния в течение года/полугода.

А бекапы, логи, -- это совсем другое.

Dimitry SibiryakovhVosttхотя было бы круто, если можно было бы включить "ретроспективный режим" на
всё подключение (например, указать временной штамп прям в строке соединения), то это бы
решало задачу.
Oracle ConceptsPackaged applications, like report generation tools that only do
queries, can run in Flashback Query mode by using logon triggers . Applications can
run transparently without requiring changes to code .

О, круто! Жаль, что только для Oracle. В моём случае это наименее предпочтительная СУБД :(

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

Нужен способ спроектировать базу данных так, чтобы задача решалась без бекапа. У бекапа совершенно другие задачи.

prog123Вот оно это путешествие в прошлое

Слабо выставить дату и увидеть совокупное состояние всей системы в этот момент? А не тыкать в каждую запись, выясняя её прошлое.

guest_20040621Ревизия закрытого периода. А связанные ревизии - локальная задница. :)

Ревизии по опыту себя вообще не оправдывают. Неудобная бесполезная нашлёпка. Чем дата/время не ревизия?

guest_20040621Вы не готовы к решению этой задачи. Ревизии и представляют основной аналитический интерес.

Могу ошибаться, или понятие "ревизия" мы понимаем по-разному.

prog123Нарисуйте (можно от руки) достойный экранный интерфейс и если он придется по вкусу собравшимся, то я его реализую.

Как выглядит экранный интерфейс, не важно :)

prog123Моя фантазия не идет дальше трёх вкладок: "Вчера" "Сегодня" "Завтра".

Допустим, пункт меню "Рестропектива", по нажатию открывается форма ввода даты, указываешь дату, нажимаешь ОК, и программа "в прошлом", за исключением какой-нибудь надписи, обозначающей дату. Все функции доступны, кроме создания или изменения. Также, если программа логгирует просмотры, она должна перестать это делать.

Собственно, пока вижу решение на основе исторических записей (копий) и сохранении периодов актуальности. Стоит ли бросаться "в омут" с таким решением, или у кого-то есть ещё предложения?

SERG1257,

Спасибо за подробный ответ. Это не доделка. Есть работающая версия 1 (но без ретроспективы). Надо делать версию 2 практически с нуля, но с оглядкой на 1 (т.е. бизнес-процессы, данные -- те же).

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

Объём устаревших версий большой, наверное надо будет предусмотреть чистку, или сжатие (т.е. частые изменения со временем надо будет упаковывать в большие, пока даже не знаю как мы это будем делать).

Придётся хранить версии в отдельных таблицах, так как к "живым" данным по перфомансу предъявляются высокие требования.

Да, две даты, надёжность обеспечивается слоем доступа к данным.

Что ж, будем делать. Спасибо за просвещение!

Dimitry SibiryakovОдно с другим связано слабо. Архивную аналитику выносят в отдельную базу по другим причинам.

Предлагаете хранить историю в одной таблице?

SERG1257Добавьте в расписание запрос на предмет поиска строк у которых дата конца не совпадает с датой начала при том же ай-ди. В нормальных условиях запрос не должен возвращать данные, ну а если кто накосячил то отловите вы, а не пользователь.
Я к тому что верить никому нельзя.

Благодарю за совет!

guest_20040621> Путешествия в прошлое планируются не частые, производительность в режиме ретроспективы не критична

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

Ну хорошо, давайте рассмотрим такой вариант. В приложении есть кнопка "Ретроспектива". Как решить задачу по нажатию на кнопку? Где-то в подвале загорается красная лампочка и бригада админов начинает интенсивно шевелиться?

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

когда в руках молоток, все проблемы выглядят как гвозди. (с)

Именно. А как ещё можно решать эту задачу?

АнатоЛойhVosttТакже, если программа логгирует просмотры, она должна перестать это делать.
Эт-то почему же? :)

Нет нужды для ретроспективы.

Arm79Вопрос сформулирован некорректно. Все элементы в БД в любой момент времени должны находиться в согласованном состоянии. Так что в общем случае при ведении истории для ВСЕЙ БД нужно при любом изменении любой сущности делать копию всех остальных связанных сущностей. Соответственно, решение ужасно затратное как по дисковому пространству, так и по производительности.

Как раз не вижу причин делать копию всех связанных сущностей. Достаточно иметь прозрачный идентификатор.

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

Исключено. Все операции с данными производятся на стороне приложения в слое доступа к данным.

Arm79В принципе, это решение позволит иметь снепшот данных на любой время: нужно просто выбирать из исторических таблиц самые близкие записи к указанному времени, а если их нет - брать значения из актуальных таблиц.

Да, придётся вести копии изменённых данных в исторической таблице, прозрачный ИД и пару дат актуальности (от сих, до сих). Усложняются выборки зависимых данных. Ещё более усложняются выборки отчётных данных, с расчётами. Вот как раз сейчас оцениваем сложность. Время и финансирование конечно есть, но и они имеют свои пределы. Если слишком сложно, надо искать другой путь.

17-77* связи идут по сквозному ИД, а конкретная редакция сведений достается по результатам расчета текущей даты актуальности -вот тут и требуется оптимизация запросов

Если есть пара дат, то запрос достаточно тривиальный.