Ведение периодических сведений в информационных системах

    Все разработчики информационных систем сталкиваются с периодической информацией, т.е. данными изменяющимися во времени. Например:
    — Цены на товары
    — Курсы валют
    — Должности
    и т.п. Также, одна периодическая информация меняется часто, другая — редко. К редко меняющейся информации можно отнести, например:
    — Фамилия и имя
    — Адрес проживания
    — Статус семьи
    Так вот, в большинстве случаев, нет необходимости хранить историю изменения этой редко меняющейся информации, т.к. никто и никогда не будет строить отчет задним числом, чтобы в нем отображалась эта информация, действующая на дату отчета.
    В таких случаях достаточно хранить просто факт изменения информации, предыдущее значение и дату изменения — это понадобиться в самых редких случаях.

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

    Для примера возьмем информацию о человеке.

    Объект "Персона". Реквизиты:
    - Фамилия
    - Имя
    - Отчество
    - Дата рождения
    - Пол
    - Статус семьи
    - Адрес проживания


    Изменяемыми во времени реквизитами здесь теоретически могут быть: Фамилия, Имя, Статус семьи, Адрес проживания и, чего греха таить, Пол.

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

    Выносим изменяемые реквизиты из объекта «Персона» и создаем объект для хранения периодической информации.

    Объект "Персона". Реквизиты:
    - Отчество
    - Дата рождения

    Объект "Сведения о персоне". Реквизиты:
    - Персона
    - Дата изменения сведений
    - Фамилия
    - Имя
    - Пол
    - Статус семьи
    - Адрес проживания


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

    Объект сведений отличает наличие «Даты изменения сведений» и ссылки на ведущий объект Персона.

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

    Казалось бы всё?! Но это только кажется.

    Сложности начинаются сразу же, при попытке вывести список Персон. Попробуем написать запрос осуществляющий это. Включаем фантазию и представляем объектный SQL по-русски:

    select сп.Фамилия, сп.Имя, п.Отчество
    from Персона п, СведенияПерсоны сп
    where п.ID = сп.Персона


    Но тут сразу проблема — нам покажется вся история сведений по персоне. А нам нужен список персон с актуальными сведениями на текущую дату. Адаптируемся к требованиям:

    select сп.Фамилия, сп.Имя, п.Отчество
    from Персона п, СведенияПерсоны сп
    where п.ID = сп.Персона
    and сп.ID = (select top 1 свед.ID from СведенияПерсоны свед where п.ID = свед.Персона and свед.ДатаИзменения <= ТекущаяДата order by свед.ДатаИзменения desc)


    Результат теперь правильный, но запрос выполняется ужасно долго и на больших объемах может напрочь вырубить СУБД, т.к. выполняется куча подзапросов.

    Альтернатива, как говориться, есть — утки! Как и ранее заявлялось — лучше иметь избыточность данных, но не потерять быстродействие при их обработке.

    Дополним регистр одной датой.

    Регистр "Сведения о персоне". Реквизиты:
    - Персона
    - Дата изменения сведений
    - Следующая дата изменения
    - Фамилия
    ...


    Эта дата соответственно будет указывать на дату «соседней» записи сведений по персоне. Важно, чтобы в этом поле всегда было значение. Тогда, если следующая дата изменения неизвестна, то заполняем «максимальной» датой, например, 31.12.9999. В результате чего наш запрос можно переписать так:

    select сп.Фамилия, сп.Имя, п.Отчество
    from Персона п, СведенияПерсоны сп
    where п.ID = сп.Персона
    and сп.СледующаяДата > ТекущаяДата
    and сп.ДатаИзменения <= ТекущаяДата


    Тут уже всё шикарно быстро!

    Заполнять Следующую дату можно например в триггере. Тут необходимо будет учесть возможность изменения «Даты изменения сведений» и «Персона», да и вообще удаления записи. Эти алгоритмы триггеров уже достаточно просты. Итого, при сохранении записи дополнительно может измениться максимально 2 «соседние» записи (одна текущая соседняя, другая новая соседняя). Это незначительная потеря при редких изменениях.

    Посмотрим, как можно хранить еще один вариант периодических сведений, например, места работы всё той же Персоны в разрезе Организаций.

    Начнем с простого. Объект "Работник". Реквизиты:
    - Персона
    - Организация
    - Должность
    - Дата приема
    - Дата увольнения


    Выборка всех работников компании на текущую дату:

    select сп.Фамилия, сп.Имя, п.Отчество, р.Организация, р.Должность
    from Персона п, СведенияПерсоны сп, Работник р
    where п.ID = сп.Персона
    and сп.СледующаяДата > ТекущаяДата
    and сп.ДатаИзменения <= ТекущаяДата
    and п.ID = р.Персона
    and р.ДатаПриема <= ТекущаяДата
    and р.ДатаУвольнения > ТекущаяДата
    and р.Организация = Компания


    Всё хорошо и замечательно. Кажется проблем нет… пока нас не спрашивают посчитать/вывести тех людей, кто поработал в компании с начала года?

    Подумаем, кто из работников это может быть:
    а) тот кто работал на начало года
    б) тот кто работает на текущую дату
    в) тот кто пришел после начала года и уволился до текущего момента

    (для простоты уберем из запроса СведенияПерсоны, т.к. с ними уже все понятно, будем считать что они доступны из поля ФИО Персоны)

    select п.ФИО, р.Организация, р.Должность
    from Персона п, Работник р
    and п.ID = р.Персона
    and р.Организация = Компания
    and (
    (р.ДатаПриема <= НачалоГода and р.ДатаУвольнения > НачалоГода) /* а */
    or (р.ДатаПриема <= ТекущаяДата and р.ДатаУвольнения > ТекущаяДата) /* б */
    or (р.ДатаПриема >= НачалоГода and р.ДатаУвольнения <= ТекущаяДата) /* в */
    )


    В итоге, совсем не подозревая, мы получаем несколько записей по человеку, который менял должность в пределах этого периода или прерывал работу. Как избавиться от повторения человека? Применить distinct? Нам нужна его последняя должность! Можно попробовать подзапрос, но есть вариант лучше.

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

    Еще стоит сказать о понятии «Ключевые реквизиты» — это набор реквизитов, в рамках значений которых не должны пересекаться интервалы (в данном случае с Даты приема по Дату увольнения) записей. Для Работника в этот набор включены реквизиты «Персона» и «Организация», т.е. человек не может в одно и тоже время иметь несколько записей в одной организации, зато может работать в разных организациях.
    Так вот, реквизит «Следующая дата» также работает в рамках Ключевых реквизитов, т.е. при изменении должности эти даты устанавливается на соседнюю запись, а при изменении организации — остаются максимальной.

    Объект "Работник". Реквизиты:
    - Персона
    - Организация
    - Должность
    - Дата приема
    - Дата увольнения
    - Следующая дата приема


    Тот же запрос перепишем:

    select п.ФИО, р.Организация, р.Должность
    from Персона п, Работник р
    and п.ID = р.Персона
    and р.Организация = Компания
    and (
    (р.ДатаПриема <= НачалоГода and р.ДатаУвольнения > НачалоГода and р.СледующаяДата > ТекущаяДата) /* а */
    or (р.ДатаПриема <= ТекущаяДата and р.ДатаУвольнения > ТекущаяДата) /* б */
    or (р.ДатаПриема >= НачалоГода and р.ДатаУвольнения <= ТекущаяДата and р.СледующаяДата > ТекущаяДата) /* в */
    )


    Вот теперь данные верны и всё работает достаточно быстро.
    Также, как со Следующей датой, можно добавить Предыдущая дата, которая будет указывать на предыдущую должности или «минимальную» дату, например 01.01.0001. Предыдущая дата будет использоваться для запросов типа «ближайшие появившиеся работники». Изменять даты можно также в триггере. Максимально дополнительно изменится еще 4 записи.

    Благодаря такой конструкции можно достаточно просто получить ответы на вопросы:
    — список тех, кто когда-либо (или в период) работал в компании
    — список недавно уволившихся на дату
    — список первых устроившихся с даты
    — и т.д.

    P.S. Надеюсь не утомил

    UPD 28.03.2011
    По замечанию MaximKat, обновил тексты запросов про Работника.
    Также добавил примечание о ключевых реквизитах.
    БАРС Груп
    Создаем технологии. Меняем жизнь.

    Комментарии 27

      +2
      Как-то все слишком усложнили тут. Вот простая схема:
      — список сотрудников;
      — список перемещений (прием, перевод, увольнение) с датой документа.

      Всё. На основе этих сведений можно делать любые запросы и отчеты. Этот вариант в начале статьи предлагается как слишком затратный и т.п. Как раз для этих случаев и используется «регистр сведений» (аналог из 1С), где хранится актуальная информация по сотрудниках на текущий момент времени.
        0
        В случае с «регистром сведений» происходит дублирование информации, а здесь попытался избавиться от этого.
        Список перемещений — аналог «Работника», но с ним возникают указанные в статье проблемы.
          0
          Если и дальше проводить аналогии с регистром сведений, то здесь и описано как они могли бы быть устроены внутри для оптимизации запросов.
      • НЛО прилетело и опубликовало эту надпись здесь
        • НЛО прилетело и опубликовало эту надпись здесь
            0
            я же предупредил «включаем фантазию» :)
            • НЛО прилетело и опубликовало эту надпись здесь
            0
            вроде понятно написал о чем
              +2
              тролль детектед.
              0
              group by разве не спасёт отца русской демократии?
                0
                думаю, нет :)
                особенно тогда, когда выборка не ограничивается только 2-мя объектами — чем их больше, тем сложнее будет использовать group by (вплоть до «невозможно»)
                  0
                  ну приведи чтоли какой-нибудь невозможный пример
                    0
                    Например, если в контексте статьи потребуется вывести список Работников и их непосредственных начальников. В Работниках уже будет group by, в Начальниках тоже придется его использовать (т.к. начальник может меняться у работника), итоговый запрос я даже боюсь представить как написать без подзапроса.
                      0
                      странное желание. особенно в свете того, что начальники — тоже работники и у них есть начальники. но даже если заменить начальников на отделы, то что мешает отсортировать на дате, сгруппировать по номерам работников и номерам отделов и выбрать последнюю запись?
                        0
                        так вот, выбор последней записи и будет являться подзапросом.
                        при указанном в статье подходе подзапросов не будет.
                          0
                          с чего бы? лимит не рулит?
                            0
                            Как показать «последнюю» должность и «последнего» руководителя работника без поздапроса с LIMIT?
                            Я пытался это сделать в первой части статьи и пришел к выводу, что добавив одно поле этого можно избежать!
                            Я не говорю, что нельзя сделать без group by и limit, я говорю, что есть способ быстрее!
                +1
                Для хранения истории вы предложили вполне адекватный подход — выносить карточку сущности в отдельную таблицу, используя при это основную таблицу, как связочную с небольшой константной информационной нагрузкой.

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

                Не проще ли было наряду с историей карточек хранить последнюю копию (заполненный соответственно набор полей с аналогичными именами колонок) карточки сущности непосредственно в сущности? В таком случае будет использоваться укороченный индекс для поиска данных — количество записей в таблице сущностей по определению меньше либо равно количеству записей в таблице карточек. Да и запросы приобретают естественный вид.

                Кроме того, в карточку сущности полезно ввести дату создания карточки и ссылку (поле с идентификатором записи) на сущность, а в сущность — ссылку на последнюю карточку. Таким образом упростится обход всей этой структуры, да и можно будет без проблем отказаться от хранения данных карточки в самой сущности, если такое кэширование не применимо в какой-либо ситуации.
                  +1
                  Для большинства случаев, у нас в сущности храниться текстовое наименование объекта, которое и вытаскивается. Я не говорю, что везде необходимо подключать таблицу истории — лишь для отчетов/выборок, ее использующих.

                  Про ссылку из сущности на карточку истории — да, можно сделать и так — я предложил свой вариант, который не встречал ранее.
                  +2
                  В 1с уже реализовано.Вообще как RAD для учетных систем — очень хорошая система (начиная с 8.1)
                    +2
                    согласен, но не все пишут на 1с
                      +2
                      Это верно. Но посмотрев, как 1с хранит, записывает и получает данные — можно многое почерпнуть для себя.
                    +1
                    По работе общаюсь с банковским мейнфреймом на Teradata. Вся история хранится подобным образом. Никакого специального разбиения данных на таблицы нет, но все таблицы имеют по две дополнительные колонки — FromDate и ToDate. Максимальная дата во второй показывает активность записи. А так как Teradata легко переваривает громадные объемы данных, то такая избыточность для нее не помеха, тем более, что минимальная разность в истории обычно равна дню.
                      0
                      Ну вот, что и требовалось доказать! Подобная избыточность полезна для выборок и не сильно нагружает обработку.
                      +1
                      Ваш второй пример во-первых непонятный, а во-вторых неправильный.
                      1. Предыдущая и следующие даты относятся к работе в той же самой компании или любой? Если к той же самой, то как вы будете делать такой же запрос, только без условия на конкретную компанию. Если к любой, то это вообще работать не будет, если человек перевелся в другую компанию в пределах одного года.
                      2. Пример: (ДолжностьА, КомпанияА, 15/02/11-28/02/11), (ДолжностьБ, КомпанияА, 15/03/11-22/03/11). Ваш код не выдаст вообще ни одной записи на сегодняшнюю дату.
                        0
                        На счет непонятности, да есть немного такое. Я рассказывал общий подход к организации регистров сведений, который мы используем, поэтому видимо из-за общности не нашлось нормального примера.

                        1) Вы совершено правы, совсем забыл упомянуть в статье, что в второй части «Следующая» и «Предыдущая» дата должна указывать на запись, относящуюся только к работнику одной компании, т.е. если человек перевелся в другую компанию, то «Следующая дата» остается в максимальной. Сейчас дополню.
                        2) Опять вы правы, там в запросе лишнее условие было. Сейчас поправлю.

                        Спасибо за внимательность!
                        0
                        А разве для того, чтобы посчитать/вывести тех людей, кто поработал в компании с начала года недостаточно «р.ДатаУвольнения > НачалоГода»

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

                        Самое читаемое