Версионность и история данных

    При разработке баз данных зачастую требуется обеспечить поддержку версионности и хранения истории объектов. Например, у работника может изменяться должность, у должности в свою очередь может меняться оклад — в многомерном моделировании это называется Slowly changing dimensions(далее SCD) — редко изменяющиеся измерения, то есть измерения, не ключевые атрибуты которых имеют тенденцию со временем изменяться. Всего существует 6 основных типов(методов) SCD, которые определяют как история изменений может быть отражена в модели.



    Тип 0


    Заключается в том, что данные после первого попадания в таблицу далее никогда не изменяются. Этот метод практически никем не используется, т.к. он не поддерживает версионности. Он нужен лишь как нулевая точка отсчета для методологии SCD.

    Тип 1


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

    Достоинства:
    • Не добавляется избыточность
    • Очень простая структура

    Недостатки:
    • Не хранит истории


    Тип 2


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

    Пример:
    ID NAME POSITION_ID DEPT DATE_START DATE_END
    1 Коля 21 2 11.08.2010 10:42:25 01.01.9999
    2 Денис 23 3 11.08.2010 10:42:25 01.01.9999
    3 Борис 26 2 11.08.2010 10:42:25 01.01.9999
    4 Шелдон 22 3 11.08.2010 10:42:25 01.01.9999
    5 Пенни 25 2 11.08.2010 10:42:25 01.01.9999


    В этом примере в качестве даты конца версии по умолчанию стоит '01.01.9999', вместо которой можно было бы указать, скажем, null, но тогда возникла бы проблема с созданием первичного ключа из ID,DATE_START и DATE_END, и, кроме того, так упрощается условие выборки для определенной даты("where snapshot_date between DATE_START and DATE_END" вместо "where snapshot_date>DATE_START and (snapshot_date < DATE_END or DATE_END is null)".
    При такой реализации при увольнении сотрудника можно будет просто изменить дату конца текущей версии на дату увольнения вместо удаления записей о работнике.

    Достоинства:
    • Хранит полную и неограниченную историю версий
    • Удобный и простой доступ к данным необходимого периода

    Недостатки:
    • Провоцирует на избыточность или заведение дополнительных таблиц для хранения изменяемых атрибутов измерения
    • Усложняет структуру или добавляет избыточность в случаях, если для аналитики потребуется согласование данных в таблице фактов с конкретными версиями измерения и при этом факт может быть не согласован с текущей для данного факта версией измерения.(Например, у клиента изменились ревизиты или адрес, а нужно провести операцию/доставку по старым значениям)


    Тип 3


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

        ID UPDATE_TIME LAST_STATE CURRENT_STATE
    1 1 11.08.2010 12:58:48 0 1
    2 2 11.08.2010 12:29:16 1 1
    Достоинства:
    • Небольшой объем данных
    • Простой и быстрый доступ к истории

    Недостатки:
    • Ограниченная история


    Тип 4


    История изменений содержится в отдельной таблице: основная таблица всегда перезаписывается текущими данными с перенесением старых данных в другую таблицу. Обычно этот тип используют для аудита изменений или создания архивных таблиц(как я уже говорил, в Oracle этот же 4-й тип можно получить из 1-го используя flashback archive). Подтипом или гибридом этого варианта(со вторым типом), как мне кажется, следует считать секционирование по признаку текущей версии с разрешенным перемещением строк, но это уже за гранью моделирования и скорее относится к администрированию.

    Пример:
    select * from emp
    

    ID NAME POSITION_ID DEPT
    1 Коля 21 2
    2 Денис 23 3
    3 Борис 26 2
    4 Шелдон 22 3
    5 Пенни 25 2


    select * from emp_history
    

    ID NAME POSITION_ID DEPT DATE
    1 Коля 21 1 11.08.2010 14:12:13
    2 Денис 23 2 11.08.2010 14:12:13
    3 Борис 26 1 11.08.2010 14:12:13
    4 Шелдон 22 2 11.08.2010 14:12:13

    Достоинства:
    • Быстрая работа с текущими версиями

    Недостатки:
    • Разделение единой сущности на разные таблицы


    Гибридный тип/Тип 6(1+2+3)


    Тип 6 был придуман Ральфом Кимболлом(Ralph Kimball) как комбинация вышеназванных методов и предназначен для ситуаций, которые они не учитывают или для большего удобства работы с данными. Он заключается во внесении дополнительной избыточности: берется за основу тип 2, добавляется суррогатный атрибут для альтернативного обзора версий(тип 3), и перезаписываются одна или все предыдущие версии(тип 1).
    Пример:
    VERSION ID NAME POSITION_ID DEPT DATE_START DATE_END CURRENT
    1 1 Коля 21 2 11.08.2010 10:42:25 01.01.9999 1
    1 2 Денис 23 3 11.08.2010 10:42:25 01.01.9999 1
    1 3 Борис 26 2 11.08.2010 10:42:25 11.08.2010 11:42:25 0
    2 3 Борис 26 2 11.08.2010 11:42:26 01.01.9999 1


    В данном примере, например, добавление суррогатного ключа добавляет возможность ссылаться из таблиц фактов на конкретную версию измерения, которая может не принадлежать времени существования самого факта, а индикатор текущей версии может помочь секционировать по текущим версиям(хотя правильнее было бы назвать секционированием по последней версии, т.к. версия может устареть без изменения самой записи). Впрочем индикатор текущей версии можно создать и как виртуальное вычислимое поле, не ухудшая нормализации, если это необходимо именно в таблице(если СУБД поддерживает такие поля, в Oracle они появились в 11-й версии), и как поле в представлении из этой таблицы.
    В целом же любая комбинация основных типов SCD относится к гибридному типу, поэтому как их недостатки так и достоинства зависят от конкретной Вашей реализации, но безусловно одно — выбор гибридного типа может быть обусловлен только сложностью Вашей модели и практически всегда(во всяком случае я не знаю случаев, когда может быть иначе) можно обойтись основными 4-мя типами.

    Позволю себе добавить несколько советов по реализациям SCD:
    • Старайтесь реализовывать механизм изменения записей в хранимых процедурах — категорически нежелательно, чтобы код изменений был разбросан по разным местам, даже если код изменений у вас хранится в четко определенных местах Вашего внешнего приложения;
    • Если Вы хотите произвести плавный переход от 1-й модели ко второй, Вы можете поступить так:
      1) изменить таблицу по типу 2 SCD с переименованием, допустим, в table_name_scd2
      2) создать обновляемое представление с названием старой таблицы, которая будет выдавать данные в той же структуре что и старая таблица;
      3) если Вы не все изменения проводите в хранимых процедурах(надеюсь, это временно :) ), которые уже изменили, то создать триггеры, которые будут заполнять новые поля в случаях, если они не устанавливаются запросом(when :new.start_date is null...) и логгировать это, чтобы затем удостовериться, что Вы все изменили
    • В случаях использования полей начала и конца версии, помимо использования первичного ключа, включающего в себя идентификатор объекта и даты начала и конца версии, Вам нужно будет для контроля целостности — создать ограничение на непересечение дат версий. Очень хорошо, если Ваша СУБД поддерживает check constraints основанные на недетерминированных функциях, позволяющие сделать это(хотелось бы, кстати, узнать какие СУБД это поддерживают), но если это не так, то Вы можете проверять условие в триггере перед созданием или изменением и вызывать исключение, в случае нарушения. Пример для Oracle:
      create or replace trigger T_EMP_CHECK
       before insert or update on emp 
       for each row
      declare
          f_ok number;
      begin
       select count(*) into f_ok
       from dual
       where exists( select null 
                     from emp e 
                     where 
                          e.id         =  :new.id
                      and e.date_start <= :new.date_end
                      and e.date_end   >= :new.date_start
                   );
       if f_ok>0 then
          raise DUP_VAL_ON_INDEX;
       end if;
      end T_EMP_CHECK;
      

    • При переходе с тип 1 на тип 4, Вам достаточно создать триггер before update, в котором будете складировать записи в новую таблицу для архивных записей


    PS. Хабралюди, поделитесь, какие интересные гибридные реализации Вы встречали?
    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 49
    • –16
      «Хабралюди, поделитесь, какие интересные гибридные реализации Вы встречали?»

      — кэш Google. он всегда всё помнит
      • 0
        Не увидел варианта хранения истории изменений на уровне колонки, на не таблицы. Очень помогает когда в таблице много полей, а изменения затрагивают небольшое количество колонок.
        Можно получить простое решение для аудита изменений всех таблиц в бд только с одной таблицей истории, особенно если orm/dal позволяет расширить поведение при сохранении записи.
        • 0
          Дело в том, что это уже получится таблица фактов второго типа, а не измерений.
        • 0
          ID, Key, Value, Time.
          Храню в мускуле данные как графы.

          Видел еще.
          «ID+Timestamp», Key1, Key2, Key 3
          • 0
            Особый интерес вызывает внезапное изменение структуры таблиц. Как без головной боли обновить их историю? Интересно, умеет-ли оракл сам разруливать такие ситуации.
            • +2
              как совет: используйте сразу SCD6(1+2+3)
              с ним очень удобно да методы работы отработаны, могу пару приемчиков показать.
              будет вторая статья про SCD в рунете ;)
              • 0
                Покажите, пожалуйста ;).
                • 0
                  например техника ACT-Views
                  каждый SCD-дименшен имеет ACT-вьюху
                  ACT-ВЬЮХИ НЕ ОТФИЛЬТРОВЫВАЮТ ТОЛЬКО НЕАКТИВНОЙ ЗАПИСИ! Они показывают значения
                  активных записей для активных и неактивных записей.
                  Таким образом вы не ломаете структуры таблицы. Но в отчете для записей у которых несколько версий будет выведены актуальные данные!
                  Т.е. вьюшка заменяет реальную таблицу с версионированием, подставляя во все записи актуальные данные.

                  CREATE OR REPLACE FORCE VIEW «TRAINING_SAL».«DIM_AGENTS_ACT» («AGENT_SURR_ID», «AGENT_ID», «AGENT_CODE», «AGENT_DESC», «AGENT_FIRST_NAME», «AGENT_LAST_NAME», «AGENT_EMPLOYEE_LOGIN», «AGENT_EMPLOYEE_EMAIL», «AGENT_TEAM_ID», «AGENT_TEAM_DESC», «AGENT_TEAM_MANAGER», «AGENT_STANDORT_ID», «AGENT_STANDORT_DESC», «IS_ACTIVE», «VALID_FROM», «VALID_TO», «INSERT_DT», «UPDATE_DT»)
                  AS
                  SELECT
                  — All columns from the table with the active records
                  — Only SURR_ID from the table with all records is taken
                  b.agent_surr_id,
                  a.agent_id,
                  a.agent_code,
                  a.agent_desc,
                  a.agent_first_name,
                  a.agent_last_name,
                  a.agent_employee_login,
                  a.agent_employee_email,
                  a.agent_team_id,
                  a.agent_team_desc,
                  a.agent_team_manager,
                  a.agent_standort_id,
                  a.agent_standort_desc,
                  a.is_active,
                  a.valid_from,
                  a.valid_to,
                  a.insert_dt,
                  a.update_dt
                  FROM
                  — Self-Join table by id key
                  dim_agents_scd a,
                  dim_agents_scd b
                  WHERE a.agent_id = b.agent_id
                  AND
                  — Filter for active records only
                  a.is_active = 'Y';

                  вот такая идея ;)
                  • 0
                    Забыл))
                    Достоинства:
                    1 в приложение не нужно внедрять сложную логику
                    2 вьюшка оптимизирована на чтение
                    3 сохранение данных в SCD-дменшен производиться добавлением новой записи и все!

                    недостатки:
                    избыточность)) но на практике дименшены не сильно распухают
                    • 0
                      Во втором совете я сказал о создании простых обновляемых представлений и они вполне благополучно могут использоваться и во втором типе.
                      • 0
                        будем считать что я вас дополнил ;)
              • +2
                Я прямо сейчас (решил кофейку попить и хабр почитать, а тут статья — телепатия :) ) историю состояний объектов реализую так:
                таблица объектов object (id, atr1, atr2, atr3, object_state_id)
                таблица состояний объектов object_state (id, object_id, atr4, atr5, atr6, date)
                где atr1-3 атрибуты 0 и 1 типа, не требующие версионности, а atr4-6 требующие, то есть история значений атрибутов хранится в отдельной таблице, а в объекте ссылка на последнее состояние (по сути и она избыточна, но искать каждый раз максимальное значение date точно не тру). Остальные таблицы ссылаются на object_state, к которой при необходимости джойнится object для получения атрибутов 0 и 1 типа.

                Можно сказать, наверное, что это спровоцированный тип 2 :) Не исключено, что в таблице object введу atr4-6 с текущими значениями, чтобы не делать джойн каждый раз для получения текущих значений, но это уже после нагрузочного тестирования — это получится практически тип 4. В этом случае предвижу трудности с разделением ссылок из других таблиц на те, которым нужны атрибуты 1-3 (0 и 1 типа), и на те, которым нужны 4-6(с версиями). Возможно даже в некоторых таблицах придётся вводить две ссылки на объект вообще (чтобы можно было получить текущие значения) и на его конкретную версию (чтобы получать значения на момент создания/изменения).

                • 0
                  А выборка данных нормально реагирует на постоянные джойны таблиц? Хотя внутренний джойн, конечно, относительно быстро работает.
                  • 0
                    Без нагрузочного тестирования и профилирования пока приемлемо. Собственно остановился на таком варианте, как на компромиссе между полностью нормализованной БД и почти полностью (за исключением «словарей») денормализованной. Джойны 1:1 (объект->текущее состояние) работают достаточно шустро по сравнению с полностью нормализованными связями (объект->все состояния->условие на текущее), а забот насчёт поддержания целостности меньше по сравнению с полностью денормализованными.
                • 0
                  используем связку из трех таблиц для мониторинга изменений сложных объектов

                  1 таблица: сессия. какой объект был изменен, кем и когда
                  2 таблица: список затронутых таблиц и первичных ключей. связана с первой по ключу сессии
                  3 таблица: список измененных полей в строке таблицы. связана со второй по ключу изменения. в этой таблице лежит имя поля, старое значение и новое значение. для удобства, во второй таблице добавлено избыточное поле с типом операции: вставка, изменение или удаление данных

                  бенифиты:
                  — полная история изменений, в том числе у объектов, которые включают данные из многих таблиц
                  — возможность откатиться до нужной версии
                  — возможность запланировать изменение в будущем и «накатить» изменения вручную либо по крону
                  — возможность выбирать какие объекты логировать, а какие нет

                  недостатки:
                  — логика сохранения изменений ложится на приложение
                  — внешние изменения не отображаются в логе (хотя их можно вычислить)
                  • +1
                    Я использую тип 3 в одном своем приложении, чтобы запомнить предыдущее состояние выполнения job'а. Результаты выполнения job'ов хранятся в отдельной таблице, но чтобы не делать лишнюю выборку, быстрее было запоминать предпоследнее значение в самой записи.

                    Еще в одном проекте над которым я работаю (c#) версионность реализована как в типе 4: при любом изменении записи ее копия вставляется в другую похожую таблицу (эта табличка находится в другой базе, правда в том же экземпляре СУБД). Похожую, потому что во-первых не все оригинальные колонки записи копируются, а во-вторых есть дополнительные колонки:
                    — primary key из оригинальной таблицы копируется в обычную колонку новой таблице
                    — у новой таблицы есть свой primary key (чтобы работало с linq2sql)
                    — дополнительная колонка в новой таблице содержит тип изменения (удаление, update)
                    — и есть колонка с датой, когда было изменение

                    Копирование происходит не на уровне базы, а из кода c# достаточно универсально: тот же самый код используется для копирования разных сущностей. Также на уровне работы с данными есть контроль над тем, для каких сущностей вести историю (и для каких полей: по соответствию названий колонок архивной и оригинальной табличек), для каких нет.

                    С БД на данный момент работает только один клиент.

                    Поддерживать такую схему не очень удобно — нужно поддерживать две одинаковые таблички. И при bulk update все очень медленно работает. В последнем случае мы просто не поддерживаем архивирование, потому что по предметной области нет смысла вести историю этих записей.
                    • 0
                      при любом изменении записи ее копия вставляется в другую похожую таблицу (эта табличка находится в другой базе, правда в том же экземпляре СУБД)

                      Не первый раз уже встречаю, что история ведется в соседней базе. А в чем преимущество такого подхода? Не распухает основная база -> быстрей работает?
                      • 0
                        Архитектуру придумали до того как я попал на проект, не могу сказать какая была задумка. Но могу сказать, что быстрее точно не работает.
                        Зато есть один неприятный момент: из-за того что работа может вестись одновременно с двумя соединениями, то может начаться распределенная транзакция и если на сервере она не сконфигурирована (а на наших dev-серверах она не настроена), получаем exception. Основное неудобство — с тестами. Обычно, если тесту нужна база, просто оборачиваем тело метода в TransactionScope и в конце делаем rollback. А с двумя базами так не получается.
                        • 0
                          >Не первый раз уже встречаю, что история ведется в соседней базе.
                          >А в чем преимущество такого подхода? Не распухает основная база -> быстрей работает?
                          ага. для истории, например, можно сменить тип Recovery model и не хранить здоровый transaction log (это я про MS SQL), наверняка в других СУБД тоже можно найти в этом некоторый профит
                      • 0
                        В СУБД Cache использую что-то наподобии типа 2, только данные храню не в таблице — а в глобале (дереве):

                        1. все текущие версии объектов имеют 0ю версию
                        2. 1я версия (если она есть) — самая старая
                        3. по умолчанию все запросы к БД оперируют с нулевыми версиями
                        4. по умолчанию индексы для ненулевых версий не строятся
                        5. в случае регулярной работы с историей — строится дополнительный специальный индекс под те объекты историю которых необходимо мониторить


                        Пример глобала с объектом CommercialIntention (комерческое намерение) 45537379, текущая версия нулевая, количество предыдущих версий 4:
                        ^Media("CommercialIntention",45537379,0,"LicenseID")=71
                        ^Media("CommercialIntention",45537379,0,"Type")="CommercialIntention"
                        ^Media("CommercialIntention",45537379,0,"UpdateTime")="61950,40226.87313"
                        ^Media("CommercialIntention",45537379,0,"baseOrderList")=45999067
                        ^Media("CommercialIntention",45537379,0,"classifiedList")=45999066
                        ^Media("CommercialIntention",45537379,0,"editionId")=6
                        ^Media("CommercialIntention",45537379,0,"editorLicenseID")=71
                        ^Media("CommercialIntention",45537379,0,"statusId")=2
                        
                        ^Media("CommercialIntention",45537379,1,"LicenseID")=45
                        ^Media("CommercialIntention",45537379,1,"Type")="CommercialIntention"
                        ^Media("CommercialIntention",45537379,1,"UpdateTime")="61944,39328.740813"
                        ^Media("CommercialIntention",45537379,1,"baseOrderList")=45537378
                        ^Media("CommercialIntention",45537379,1,"classifiedList")=45537377
                        ^Media("CommercialIntention",45537379,1,"editionId")=6
                        ^Media("CommercialIntention",45537379,1,"sourceTypeId")=50140
                        ^Media("CommercialIntention",45537379,1,"statusId")=1
                        
                        ^Media("CommercialIntention",45537379,2,"LicenseID")=71
                        ^Media("CommercialIntention",45537379,2,"Type")="CommercialIntention"
                        ^Media("CommercialIntention",45537379,2,"UpdateTime")="61944,39807.398159"
                        ^Media("CommercialIntention",45537379,2,"baseOrderList")=45537575
                        ^Media("CommercialIntention",45537379,2,"classifiedList")=45537574
                        ^Media("CommercialIntention",45537379,2,"editionId")=6
                        ^Media("CommercialIntention",45537379,2,"editorLicenseID")=71
                        ^Media("CommercialIntention",45537379,2,"statusId")=2
                        
                        ^Media("CommercialIntention",45537379,3,"LicenseID")=71
                        ^Media("CommercialIntention",45537379,3,"Type")="CommercialIntention"
                        ^Media("CommercialIntention",45537379,3,"UpdateTime")="61950,37803.280408"
                        ^Media("CommercialIntention",45537379,3,"adTypeId")=30202
                        ^Media("CommercialIntention",45537379,3,"baseOrderList")=45996926
                        ^Media("CommercialIntention",45537379,3,"classifiedList")=45996925
                        ^Media("CommercialIntention",45537379,3,"editionId")=6
                        ^Media("CommercialIntention",45537379,3,"editorLicenseID")=55
                        ^Media("CommercialIntention",45537379,3,"sourceTypeId")=50140
                        ^Media("CommercialIntention",45537379,3,"statusId")=3
                        
                        ^Media("CommercialIntention",45537379,4,"LicenseID")=71
                        ^Media("CommercialIntention",45537379,4,"Type")="CommercialIntention"
                        ^Media("CommercialIntention",45537379,4,"UpdateTime")="61950,40193.904669"
                        ^Media("CommercialIntention",45537379,4,"baseOrderList")=45999039
                        ^Media("CommercialIntention",45537379,4,"classifiedList")=45999038
                        ^Media("CommercialIntention",45537379,4,"editionId")=6
                        ^Media("CommercialIntention",45537379,4,"editorLicenseID")=71
                        ^Media("CommercialIntention",45537379,4,"statusId")=8
                        
                        
                        

                        • +1
                          Как-то ковырял Oracle Workflow, который целиком написан на PL. Фишка как раз была в том, что бизнес процессы могли меняться, но запущенный процесс должен следовать версии, актуальной на момент запуска, даже если впоследствие процесс изменился.

                          Реализация была как раз по «типу 2». Однако структура данных была «продублирована» views, которые выдавали текущую версию процессов.

                          P.S. впринципе поле DATE_START не нужно, хватит одного DATE_END
                          • +1
                            Вообще-то нужно, иначе любой запрос на версию от конкретной даты будет возвращать курсор с кучей значений, которые надо еще отсортировать по DATE_END, и выдать только первый вариант.

                            А теперь, допустим у Вас есть Oracle, в котором нет LIMIT, и надо связать 3 таблицы с такой версионностью без DATE_START. Тяжело будет написать и долго будет обрабатываться.
                            • 0
                              LIMIT нет, зато есть более продвинутый rownum, с помощью которого можно легко вернуть самую последнюю запись.
                              • 0
                                в Oracle никакого аналога LIMIT нет (не считая вложенных select`ов с row_number() over (трампампам)) и rownum вам не поможет если есть сортировка, а без сортировки гарантировать что строки всегда будут в правильном порядке — нельзя. Долго такой баг ловить будете.
                                • 0
                                  чтобы использовать rownum, надо это дело оборачивать в подзапрос. А теперь представьте, что джойните три такие таблицы. И с этими rownum начинается такая свистопляска, что мама не горюй.
                            • 0
                              Использую дополнительные поля vn(INT), is_latest( 'Y','N'),
                              соответственно номер версии и флаг, последняя ли она.

                              На все запросы к таблицам с версиями добавляется автоматом суффикс AND is_latest='Y'.
                              • 0
                                Используем 4й тип. Только в доп. таблице еще есть запись кто и когда что менял. Помогает находить злостных вредителей.
                                • 0
                                  Если я не ошибаюсь, то 6-й тип — это bitemporal database. У нас например так хранится дерево (граф) объектов. Собственно, какие там есть мысли DATE_{START,END}, VERSION_{START,END}, а также для каждой записи ID и GLOBALID — то есть, ид текущий (DS,DE,VS,VE) и немутирующий идентификатор. При этом достигается гибкость в плане использования будущих периодов и фиксации старых значений (историчность). Версионность — имхо, это конечно костыль. Как написано на вики есть transaction time — его и надо учитывать, и отдельно вести список версий, то есть список точек времени с историей.
                                  • 0
                                    Transaction time тут не при чем, т.к. может быть изменена не текущая дата и не текущим временем -> дата транзакции совершенно не валидна с точки зрения объекта.
                                  • +1
                                    Я бы еще добавил, что правильная историчность — это один шаг для репликации на другую базу. Взаимное применение изменений баз одной на другую, их пересечение и будет актуальным состоянием базы.
                                    • –1
                                      А как насчет такого способа:
                                      Для таблицы %TABLENAME% имеем суррогатный ключ ID+VERSION_NUMBER плюс стандартные поля CREATED, UPDATED.
                                      Создаем таблицу %TABLENAME%_CURRENT_VERSION(ID, VERSION_NUMBER). Для каждой новой версии записи — создаем дубликат в %TABLE_NAME%, в нем инкрементируем VERSION_NUMBER, присваиваем новое значение UPDATED, и обновляем соответствующее значение VERSION_NUMBER в таблице %TABLENAME%_CURRENT_VERSION для данного ID.
                                      Выборка актуальных версий на конкретную дату производим по дате UPDATED,
                                      Актуальную версию выбираем через INNER JOIN к таблице %TABLENAME%_CURRENT_VERSION
                                      При необходимости можно зашедулить перенос устаревших версий в таблицу дубликат, если размеры таблиц начнут сказываться на производительности.
                                      • 0
                                        у нас каждая таблица(которая должна иметь историчность) имеет следующие дополнительные поля seq (0 — для текущей записи, primary key является связка (seq,id)), плюс поля кем и когда создано, кем и когда изменено. для выборки текущих значений создаем view. при обновлении записи, сначала генерим новый seq, создаем копию текущего состояния с новым seq, кто и когда обновил, потом обновляем значения в записи с seq=0.
                                        • 0
                                          Я еще использовал такой способ — для полей, которые часто меняются создавал отдельную таблицу. Плюс в том, что уменьшается объем памяти, требуемый для хранения истории. Если создавать для каждого поля, то база начинает страшно тормозить из-за многочисленных join.
                                          • +1
                                            Поля, которые часто меняются, по сути уже не совсем SCD, это уже практически таблицы фактов.
                                          • 0
                                            У нас используется метод 2. Для аудита всех таблиц ведется одна отдельная большая партиционированная таблица с инфо о том, кто изменил (ip, машина, логин, и т.д.), имеющая ссылки на измененную таблицу и id измененного объекта. По прошествии времени старые партиции отправляются в архив.
                                            • 0
                                              Тоже используем такую же таблицу аудита с собственными пакетами, которые помимо самих процедур изменения объектов, содержат процедуры для восстановления измененных данных.
                                            • +2
                                              Я недавно как раз на эту тему вот такую несложную библиотечку для PostgreSQL писал:
                                              github.com/DmitryKoterov/dklab_rowlog

                                              Можно подключить к любой таблице логирование по типу 4 за одну команду, причем сами данные хранятся в hstore. Пример использования (в README на github-е есть еще):

                                              — Example: monitor change of column «a» and «c». Add an entry to public.rowlog
                                              — table if and only if one of these columns are changed.
                                              CREATE TRIGGER t_rowlog
                                              AFTER INSERT OR DELETE OR UPDATE ON test_src1 FOR EACH ROW
                                              EXECUTE PROCEDURE rowlog.t_rowlog_aiud('diff=>a', 'diff=>c', 'rowlog=>public.rowlog');

                                              Только надо учитывать 2 вещи при использовании:
                                              1. Библиотека не рассчитана на ультравысокие нагрузки, т.к. там внутри несколько EXECUTE SQL-ей.
                                              2. Хранение измененных данных в hstore удобно тем, что быстро новые поля можно добавлять, однако есть и недостаток — если табличка-источник сильно меняется (например, там поля удаляются или переименовываются), в hstore останутся старые версии со старой структурой.

                                              Поэтому на практике я бы порекомендовал ее применять в основном для целей подсчета статистики различной по живой базе (KPI).
                                              • 0
                                                Оформил этот комментарий в виде топика: habrahabr.ru/blogs/sql/101616/
                                                • 0
                                                  Оригинальная мысль, но почему в библиотеке не создать процедуру, которая и будет создавать необходимые триггеры с необходимым кодом?
                                              • 0
                                                Некая разновидность 4-го типа реализована в модуле Hibernate-Envers.
                                                • 0
                                                  Я как раз сейчас пишу подобную штуку для MySQL, чтобы автоматом создавать лог-таблицы, триггеры, а так-же мониторить изменения схемы…
                                                  • +1
                                                    Используем второй способ. Именно так реализована MVCC в InnoDB (MySQL) — оттуда о нём и узнали. Только там вместо дат используются внутренние номера транзакций.

                                                    У нас же реализация полностью на ПХП (MySQL еще недорос для перекладывания на неге какой-либо логики) как extension к Yii.

                                                    Было интересно узнать про другие способы, спасибо.
                                                    • 0
                                                      Использую вариацию 4-го метода. Вариация заключается в том, что храню только разницу по сравнению с предыдущей записью, следовательно объем хранимой информации сильно меньше (при том что таблица широкая и за раз изменяется только порядка 10% полей). Если поля в текущей и предыдущей записях эквивалентны, в таблицу логов записывается NULL. Впрочем, есть свои хитрости чтобы отличить в таблице лога реальный NULL от повторения значения. Состояние записей на любой прошлый момент времени собираются через аналитику, можно просмотреть шаги изменений, уточнить кто производил изменения и т.п… Текущее состояние видно сразу без всяких хитростей.
                                                      Полагаю, я изобрел велосипед. До сих пор не знаю как такой метод называется, но он работает не хуже flashback и позволяет более предсказуемо управлять объемами, плюс хранить дополнительную информацию по изменениям.
                                                      • 0
                                                        На случай если кто-то нагуглит этот топик, даю ссылку на описание своего гибрида.
                                                      • +1
                                                        DateEnd = 01.01.9999
                                                        Программисты будущего в новый 9999 год будут материть нас за это =)
                                                        • +1
                                                          А куда 5й тип дели, ироды?
                                                          • –1
                                                            «Старайтесь реализовывать механизм изменения записей в хранимых процедурах — категорически нежелательно, чтобы код изменений был разбросан по разным местам, даже если код изменений у вас хранится в четко определенных местах Вашего внешнего приложения;»
                                                            Это еще почему? То есть, значит, если я использую DAL в любом его варианты, и до появления истории хранимые процедуры мне нафиг не были нужны, то для добавления истории я должен похоронить принцип «все в одном месте» и разнести код?

                                                            По-моему, ровно наоборот. Если в DAL есть свои способы поддержи историчности данных (не важно, каким образом), они на его стороне и должны быть реализован. Это как раз обеспечит униформность работы историчности (и заодно оптимальность работы самого DAL, потому что он знает, с чем работает).
                                                            • 0
                                                              В случаях использования полей начала и конца версии

                                                              как один из дополнительных подходов — контролировать непрерывность интервалов с помощью констрейнтов, например так:

                                                              drop table scdtest;
                                                              create table scdtest(s_code varchar2(10), s_value varchar2(10), s_start_date date not null, s_end_date date, s_latest varchar2(10) as (decode(s_end_date, null, s_code, null)));
                                                              alter table scdtest add constraint unq_s_start_date unique (s_code, s_start_date);
                                                              alter table scdtest add constraint unq_s_latest unique (s_latest) deferrable INITIALLY DEFERRED;
                                                              alter table scdtest add constraint fk_s_s foreign key (s_code, s_end_date) references scdtest (s_code, s_start_date) deferrable INITIALLY DEFERRED;
                                                              
                                                              -- открываем интервал 
                                                              insert into scdtest (s_code, s_value, s_start_date, s_end_date) values ('A' ,'A1', to_date('01.01.2000', 'DD.MM.YYYY'), null);
                                                              commit;
                                                              
                                                              -- продложаем интервал
                                                              update scdtest set s_end_date = to_date('01.01.2001', 'DD.MM.YYYY') where s_latest = 'A';
                                                              insert into scdtest (s_code, s_value, s_start_date, s_end_date) values ('A' ,'A2', to_date('01.01.2001', 'DD.MM.YYYY'), null);
                                                              commit;
                                                              


                                                              хотелось бы, кстати, узнать какие СУБД это поддерживают

                                                              Подозреваю, что в чистом виде никакие, ибо накладно это :) Плохо себе представляю консистентную ревалидацию например 10 террабайт данных по недетерминистической функции.

                                                              Хотя, если логика функции проверки укладывается в SQL scope, то теоретически можно построить матвьюху и навешать констрейнтов на неё, но:
                                                              — при сильно замороченной логике возможно матвьюху не удастся сделать fast refresh on commit, там вроде требования к детерминистичности те же если не жёстче
                                                              — ексепшены на нарушения придуманной логики будете ловить только при завершении транзакции ровно как с отложенными констрейнтами, ибо refresh on commit
                                                              — в 10ой версии распределенные транзакции, приводящие к обновлению матвьюхи приводят к ора-600
                                                              — двойной расход места
                                                              Не смотря на всё это я таким костылем пользовался и он оказался вполне себе живуч.

                                                              Ну и отложенную асинхронную проверку можно конечно навелосипедить в любой момент, но это уже другая опера.
                                                              • 0
                                                                Все верно и насчет констрейнтов, это старый известный трюк, например, тут обсуждалось: www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=305675&msg=2791319 и насчет матвьюх. Но и у того и другого с конкурентностью и нагрузкой очень будет хреново :)

                                                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.