Реляционно-ретроспективная модель с историей изменения данных

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

Если у вас вдруг возникла необходимость обеспечить ретроспективную доступность данных - не спешите сразу переходить к технической реализации. Как не странно, но здесь самое главное не техническое решение на уровне модели данных и API, а интерфейс пользователя. Ведь основная проблема в том, чтобы заставить оператора осознано различать логическую нагрузку близких по смыслу действий. Например, при обновлении данных важно уверенно различать исправление ошибки и актуализацию данных. Представьте, что изначально название улицы, фамилия или e-mail были введены неправильно и оператор хочет исправить ошибку - этот процесс не имеет никакого отношения к историчности. А вот если название улицы или статус члена группы изменились вследствие естественных процессов, то это уже предмет ретроспективной функциональности. Приблизительно такая же ситуация и с удалением, если участник выходит из группы, то в этом случае не нужно удалять запись состава группы, нужно проставить дату его выхода из группы. Условно, можно выделить два случая, явно влияющих на реализацию UI:

1. … когда пользователь полностью управляет историческими периодами. Например, при учете пребывания участника в составе группы.

2. … когда пользователь определяет только дату ввода в учет нового значения. Например, при изменении состояния или роли участника в составе группы.

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

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

Теперь подошла очередь рассмотреть более интересный случай, как быть, если требуется поддерживать историческое значение сразу нескольких исторических атрибутов какой-то сущности?

Вариант №1

Для каждого исторического атрибута сделать отдельную инфраструктуру: таблица + UI + API.

Рекомендуется применять при частом изменении значений. Есть несколько лайфхаков, которые помогут сделать систему более технологичной:

• Cделайте универсальную форму для работы с историческими записями, которая может работать с любым типом данных и API.

• Разместите копию актуальных значений исторических атрибутов в записи основной таблицы, в этом случае обращение к историческим таблицам понадобится только в ретроспективных запросах.

• Чтобы ретроспективные запросы стали более компактными, можно попробовать собрать все исторические атрибуты в отдельное view или процедуру с параметром, но это может снизить скорость обработки запросов.

Плюсы:

• Простота реализации.

• Минимальные издержки для хранения данных.

• Максимальная понятность для оператора.

Минусы:

• Порождает большое количество таблиц и соединений в ретроспективных запросах.

• Для каждого нового исторического атрибута всегда требуется доработка модели данных.

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

Вариант №2

Сделать одну таблицу для всего набора исторических атрибутов сущности и одну форму. То есть, исторические атрибуты сущности будут располагаться не в отдельных таблицах (как в варианте 1), а в общей. Таким образом для каждой таблицы (сущности) основного учета, где есть исторические атрибуты, понадобится всего 1 историческая таблица, что приведет к некоторой денормализации данных.

Этот вариант более сложный. Рассматривается как промежуточный для перехода к третьему варианту. В этом варианте вы столкнетесь со следующими моментами:

• Также, как и в варианте 1 нужно разместить копию актуальных значений исторических атрибутов в основной таблице.

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

Плюсы (по сравнению с вариантом 1):

• Меньше ретроспективных таблиц.

• Значительно более удобное построение ретроспективных запросов и выше их производительность.

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

Минусы (по сравнению с вариантом 1):

• Больше издержки на хранение данных.

• Для каждого нового исторического атрибута по-прежнему требуется доработка модели данных, но уже на уровне полей, а не таблиц.

• Сложнее логика работы с историческими записями в части исправления ошибок.

• Возможно, смысл формы для работы с историческими записями (наборами атрибутов) будет менее понятен оператору, но тут все зависит от пояснений, они должны быть крайне понятными.

• Универсальную форму работы с историческими записями сделать сложнее.  

Вариант №3

Организовать хранение исторических записей в таблице основного учета. В этом варианте не требуется создавать исторические таблицы, все данные хранятся в основных таблицах. Ретроспективные запросы не усложняются. Все запросы выполняются максимально быстро, быстрее, чем в варианте 1 и 2. Самый главный плюс – не нужно менять модель данных, если вы захотите какой-то атрибут сделать историческим. Есть в этом варианте и минус – издержки для хранения данных могут быть значительными при частых изменениях исторических атрибутов. Этому варианту присущи такие моменты:

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

• Также, как и в варианте 2, при исправлении оператором ошибки (уже любого атрибута) в основной или исторической записи нужно исправлять её ещё и в более ранних исторических записях.

• Нужно добавить в таблицу специальный атрибут (hid), в котором будет находиться идентификатор (id) основной записи. Атрибут hid нужен, чтобы пометить исторические записи идентификатором основной записи (у основной записи hid = id), и он будет использоваться в ретроспективных запросах вместо id.     

Прочие технические подсказки

Для удобства изложения технических деталей, определим поля, определяющие срок актуальности исторических атрибутов: state_from и state_to и две константы: MIN = 2000.01.01 и MAX = 3000.01.01.

• Дефолтное значение для state_from рекомендуется задать MIN, для state_to - MAX.

• Значения MIN и MAX всегда должны присутствовать в пуле исторических записей. В самой ранней исторической записи нельзя изменять state_from (т.е. значение MIN оператор не может изменить). Желание управлять этой датой в первой записи или датой конца срока актуальности говорит о том, что вам нужны свободные периоды (см. начало статьи).

• В варианте 1 и 2, уникальные индексы: original_id + state_from и original_id + state_to, в варианте 3 - hid + state_from и hid + state_to.

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

• В теории, можно допустить что некоторые значения будут вводится в учет будущей датой. Но в варианте 3 это нужно исключить, поскольку не существует записи с дублем актуальных значений. Опять же в теории, такую запись можно иметь, но это ещё больше увеличит издержки на хранение данных.

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

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

• При удалении исторической записи, а также при изменении state_from необходимо перестыковывать сроки актуальности текущей и предыдущей записи.

• При удалении актуальной записи, её исторические слепки тоже нужно удалить.

• Самую раннюю историческую запись или запретите удалять или переносите её state_from в последующую историческую запись.

• Удаление последней исторической записи (с текущими актуальными значениями) должно приводить к обновлению актуальных значений в основной таблице, а state_to предыдущей исторической записи (которая после удаления станет последней) должен стать равен MAX. Для варианта 3, удаление последней записи, в форме исторических записей, (т.е. основной записи) выполняется по-другому, основная запись наследует от последней исторической записи state_from и значения исторических атрибутов, после этого эта историческая запись удаляется. Обратите внимание, в основной форме, запись может быть удалена обычным образом (с каскадным удалением, в т.ч. исторических записей), а в форме управления историческими записями её удаление имитируется, т.е. обрабатывается особым образом.    

Примеры для варианта 3.

Представим, что когда-то была создана запись, тогда у неё state_from будет равно 2000.01.01 и state_to - 3000.01.01. Сегодня требуется актуализировать значение исторического атрибута (выполняется через специальную форму, дата ввода нового значения в учет - today). В результате должна появиться историческая запись с state_from = 2000.01.01 и state_to = today – 1, а в основной записи будет state_from = today и state_to = 3000.01.01. Все остальные атрибуты, за исключением id и измененного атрибута, будут такими же, как в основной записи.

Запрос, выделяющий текущие актуальные записи мог бы выглядеть так:

select *
from users
where id = hid

Если сделать view и использовать их в запросах, построенных на актуальных данных (коих большинство), то никаких дополнительных условий, для построения рабочих запросов на актуальных данных, учитывать бы не пришлось.

Запрос с соединением и обращением к историческому периоду мог бы выглядеть так:

select
		*
from
		visits vst
		inner join users usr on usr.hid = vst.user_id 
								and vst.date between usr.state_from and usr.state_to

Ниже приведен пример одновременной комбинации свободных и состыкованных периодов.

Есть таблица group_members в которой задается срок пребывания участника в группе атрибутами from и to (управляемыми оператором) и историческое обеспечение. Требуется определить на дату date состав группы и актуальное значение исторического атрибута status. Делается это так …

select
		*
from
		group_members
where
		date >= start and (date <= finish or finish is null)
		and date between state_from and state_to

Ну вот и все, если кому нужна помощь в проектировании ретроспективной модели welcome.