Все разработчики информационных систем сталкиваются с периодической информацией, т.е. данными изменяющимися во времени. Например:
— Цены на товары
— Курсы валют
— Должности
и т.п. Также, одна периодическая информация меняется часто, другая — редко. К редко меняющейся информации можно отнести, например:
— Фамилия и имя
— Адрес проживания
— Статус семьи
Так вот, в большинстве случаев, нет необходимости хранить историю изменения этой редко меняющейся информации, т.к. никто и никогда не будет строить отчет задним числом, чтобы в нем отображалась эта информация, действующая на дату отчета.
В таких случаях достаточно хранить просто факт изменения информации, предыдущее значение и дату изменения — это понадобиться в самых редких случаях.
Дальше пойдет разговор о способе хранения периодической информации в реляционной базе данных для случаев, когда такие отчеты заведомо нужны в системе и они требуют получения актуальных данных на определенную дату.
Для примера возьмем информацию о человеке.
Изменяемыми во времени реквизитами здесь теоретически могут быть: Фамилия, Имя, Статус семьи, Адрес проживания и, чего греха таить, Пол.
Создадим объект хранения истории изменения этих данных. Сразу договоримся, что будем хранить целиком запись со всем набором изменяющихся реквизитов — так удобнее для работы. Пусть потеряем в объеме данных, но выиграем в обработке.
Выносим изменяемые реквизиты из объекта «Персона» и создаем объект для хранения периодической информации.
(по факту, можно перенести все реквизиты из Персоны в Сведения, здесь цель показать что этого делать не обязательно)
Объект сведений отличает наличие «Даты изменения сведений» и ссылки на ведущий объект Персона.
Во всех объектах системы должен использоваться объект «Персона» и ссылки на него, а сведения будут использоваться только как подчиненный объект без непосредственной ссылки на них.
Казалось бы всё?! Но это только кажется.
Сложности начинаются сразу же, при попытке вывести список Персон. Попробуем написать запрос осуществляющий это. Включаем фантазию и представляем объектный SQL по-русски:
Но тут сразу проблема — нам покажется вся история сведений по персоне. А нам нужен список персон с актуальными сведениями на текущую дату. Адаптируемся к требованиям:
Результат теперь правильный, но запрос выполняется ужасно долго и на больших объемах может напрочь вырубить СУБД, т.к. выполняется куча подзапросов.
Альтернатива, как говориться, есть — утки! Как и ранее заявлялось — лучше иметь избыточность данных, но не потерять быстродействие при их обработке.
Дополним регистр одной датой.
Эта дата соответственно будет указывать на дату «соседней» записи сведений по персоне. Важно, чтобы в этом поле всегда было значение. Тогда, если следующая дата изменения неизвестна, то заполняем «максимальной» датой, например, 31.12.9999. В результате чего наш запрос можно переписать так:
Тут уже всё шикарно быстро!
Заполнять Следующую дату можно например в триггере. Тут необходимо будет учесть возможность изменения «Даты изменения сведений» и «Персона», да и вообще удаления записи. Эти алгоритмы триггеров уже достаточно просты. Итого, при сохранении записи дополнительно может измениться максимально 2 «соседние» записи (одна текущая соседняя, другая новая соседняя). Это незначительная потеря при редких изменениях.
Посмотрим, как можно хранить еще один вариант периодических сведений, например, места работы всё той же Персоны в разрезе Организаций.
Выборка всех работников компании на текущую дату:
Всё хорошо и замечательно. Кажется проблем нет… пока нас не спрашивают посчитать/вывести тех людей, кто поработал в компании с начала года?
Подумаем, кто из работников это может быть:
а) тот кто работал на начало года
б) тот кто работает на текущую дату
в) тот кто пришел после начала года и уволился до текущего момента
(для простоты уберем из запроса СведенияПерсоны, т.к. с ними уже все понятно, будем считать что они доступны из поля ФИО Персоны)
В итоге, совсем не подозревая, мы получаем несколько записей по человеку, который менял должность в пределах этого периода или прерывал работу. Как избавиться от повторения человека? Применить distinct? Нам нужна его последняя должность! Можно попробовать подзапрос, но есть вариант лучше.
Тут также можно применить подход к хранению, аналогичный сведениям о персоне.
Еще стоит сказать о понятии «Ключевые реквизиты» — это набор реквизитов, в рамках значений которых не должны пересекаться интервалы (в данном случае с Даты приема по Дату увольнения) записей. Для Работника в этот набор включены реквизиты «Персона» и «Организация», т.е. человек не может в одно и тоже время иметь несколько записей в одной организации, зато может работать в разных организациях.
Так вот, реквизит «Следующая дата» также работает в рамках Ключевых реквизитов, т.е. при изменении должности эти даты устанавливается на соседнюю запись, а при изменении организации — остаются максимальной.
Тот же запрос перепишем:
Вот теперь данные верны и всё работает достаточно быстро.
Также, как со Следующей датой, можно добавить Предыдущая дата, которая будет указывать на предыдущую должности или «минимальную» дату, например 01.01.0001. Предыдущая дата будет использоваться для запросов типа «ближайшие появившиеся работники». Изменять даты можно также в триггере. Максимально дополнительно изменится еще 4 записи.
Благодаря такой конструкции можно достаточно просто получить ответы на вопросы:
— список тех, кто когда-либо (или в период) работал в компании
— список недавно уволившихся на дату
— список первых устроившихся с даты
— и т.д.
P.S. Надеюсь не утомил
UPD 28.03.2011
По замечанию MaximKat, обновил тексты запросов про Работника.
Также добавил примечание о ключевых реквизитах.
— Цены на товары
— Курсы валют
— Должности
и т.п. Также, одна периодическая информация меняется часто, другая — редко. К редко меняющейся информации можно отнести, например:
— Фамилия и имя
— Адрес проживания
— Статус семьи
Так вот, в большинстве случаев, нет необходимости хранить историю изменения этой редко меняющейся информации, т.к. никто и никогда не будет строить отчет задним числом, чтобы в нем отображалась эта информация, действующая на дату отчета.
В таких случаях достаточно хранить просто факт изменения информации, предыдущее значение и дату изменения — это понадобиться в самых редких случаях.
Дальше пойдет разговор о способе хранения периодической информации в реляционной базе данных для случаев, когда такие отчеты заведомо нужны в системе и они требуют получения актуальных данных на определенную дату.
Для примера возьмем информацию о человеке.
Объект "Персона". Реквизиты:
- Фамилия
- Имя
- Отчество
- Дата рождения
- Пол
- Статус семьи
- Адрес проживания
Изменяемыми во времени реквизитами здесь теоретически могут быть: Фамилия, Имя, Статус семьи, Адрес проживания и, чего греха таить, Пол.
Создадим объект хранения истории изменения этих данных. Сразу договоримся, что будем хранить целиком запись со всем набором изменяющихся реквизитов — так удобнее для работы. Пусть потеряем в объеме данных, но выиграем в обработке.
Выносим изменяемые реквизиты из объекта «Персона» и создаем объект для хранения периодической информации.
Объект "Персона". Реквизиты:
- Отчество
- Дата рождения
Объект "Сведения о персоне". Реквизиты:
- Персона
- Дата изменения сведений
- Фамилия
- Имя
- Пол
- Статус семьи
- Адрес проживания
(по факту, можно перенести все реквизиты из Персоны в Сведения, здесь цель показать что этого делать не обязательно)
Объект сведений отличает наличие «Даты изменения сведений» и ссылки на ведущий объект Персона.
Во всех объектах системы должен использоваться объект «Персона» и ссылки на него, а сведения будут использоваться только как подчиненный объект без непосредственной ссылки на них.
Казалось бы всё?! Но это только кажется.
Сложности начинаются сразу же, при попытке вывести список Персон. Попробуем написать запрос осуществляющий это. Включаем фантазию и представляем объектный 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, обновил тексты запросов про Работника.
Также добавил примечание о ключевых реквизитах.
