Ретроспективная функциональность в реляционной среде

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

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

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

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

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

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

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

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

• Сделайте универсальную форму для работы с историческими записями, которая может работать с любым типом данных и API.
• Разместите копию актуальных значений исторических атрибутов в записи основной таблицы, в этом случае обращение к историческим таблицам понадобится только в ретроспективных запросах (жертвуем возможностью вносить изменения будущим временем, но оно того стоит).
• Чтобы ретроспективные запросы стали более компактными, можно попробовать собрать все исторические атрибуты в отдельное view или процедуру с параметром, но это может снизить скорость обработки запросов.

Плюсы:

• Простота реализации.
• Минимальные издержки для хранения данных.
• Максимальная понятность для оператора.

Минусы:

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

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

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

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

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

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

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

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

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

• Текущие актуальные значения исторических атрибутов теперь будут находится только в основной записи, исторические значения в исторических записях. Т.е. в форме управления историческими записями, последняя историческая запись будет представлена основной записью.
• Также, как и в варианте 2, при исправлении оператором ошибки (изменение неисторического атрибута всегда считается исправлением ошибки) в основной или исторической записи нужно исправлять её ещё и в более ранних исторических записях.
• Нужно добавить в таблицу специальный атрибут (original_id), в котором будет находиться идентификатор (id) основной записи. Атрибут original_id нужен, чтобы пометить исторические записи идентификатором основной записи (у основной записи original_id = 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 оператор не может изменить). Желание управлять этой датой в первой записи или датой конца интервала актуальности говорит о том, что вам нужны свободные периоды (см. начало статьи).
• Уникальные индексы: original_id + state_from и original_id + state_to.
• Когда какая-то сущность создается – не нужно использовать специальную форму для указания значений исторических атрибутов, их можно указывать в общей форме.
• При изменении оператором даты 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, а в основной записи будет state_from = today и state_to = 3000.01.01. Все остальные атрибуты, за исключением id и измененного атрибута, будут такими же, как в основной записи.

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

Если есть партиция для актуальных данных, то можно обращаться к ней напрямую.

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

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

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