Физическая модель хранилища данных: учет влияния транзакций, денормализация таблиц

Разбиение таблиц и ссылочная целостность

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

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

Пример 19.7.

Для разрешения отношения "многие ко многим" между таблицами "Служащий" (EMPLOYEE) и "Проект" (PROJECT) была введена связывающая таблица "Служащий Проект" (EMP_PROJ), которая имеет ограничения ссылочной целостности, с таблицей "Проект" (PROJECT), как показано на рис. 19.11.

Рис. 19.11. Разрешение отношения "многие ко многим" между таблицами "Служащий" (EMPLOYEE) и "Проект" (PROJECT)

Связывающая таблица "Служащий Проект" (EMP_PROJ) была создана с помощью команды SQL, приведенной ниже.

create table EMP_PROJ (
   PROJNO               char(8)              not null,
   EMPNO                integer              not null,
   WORKS                integer              null,
   constraint PK_EMP_PROJ primary key (PROJNO, EMPNO).
   constraint FK_EMP_PROJ_REFERENCE_PROJECT foreign key (PROJNO)
      references PROJECT (PROJNO),
   constraint FK_EMP_PROJ_REFERENCE_EMPLOYEE foreign key (EMPNO)
      references EMPLOYEE (EMPNO)
)
go

Но у нас теперь, после разбиения таблицы "Проект" (PROJECT), появились две новые таблицы: "Текущие проекты" (PROJECT_CUR) вместо таблицы "Проекты" (PROJECT) и "Архивные проекты" (PROJECT_OLD), как показано на рис. 19.12.

Рис. 19.12. Разрешение отношения "многие ко многим" после разбиения таблицы "Проекты" (PROJECT)

Предположим, что для реализации проекта организация нанимает сотрудников на временной основе (на время выполнения проекта), и вопрос: кто, какие проекты, когда выполнял – не интересует руководство организации. В этом случае взаимосвязь между исполнителями и проектами, которые уже завершены, не интересует руководство, и следовательно, в физической модели данных ничего менять не нужно.

Однако если взаимосвязь между исполнителями и завершенными проектами должна отслеживаться (например, руководство будет изучать вопрос: кто, когда, какой проект выполнял), ее следует распространить и на таблицу "Архивные проекты" (PROJECT_OLD), как показано на рис. 19.13.

Чтобы учесть в БД проделанную нами работу, достаточно внести ограничение внешнего ключа в таблицу "Служащий Проект" (EMP_PROJ), как показано ниже:

Рис. 19.13. Разрешение отношения "многие ко многим" таблицы "Архивные проекты" (PROJECT_OLD)

drop table EMP_PROJ
Go

create table EMP_PROJ (
   PROJNO               char(8)              not null,
   EMPNO                integer              not null,
   WORKS                integer              null,
   constraint PK_EMP_PROJ primary key (PROJNO, EMPNO).
   constraint FK_EMP_PROJ_REFERENCE_PROJECT foreign key (PROJNO)
      references PROJECT_CUR (PROJNO),
   constraint FK_EMP_PROJ_REFERENCE_PROJECT_OLD foreign key (PROJNO)
      references PROJECT_OLD (PROJNO),
   constraint FK_EMP_PROJ_REFERENCE_EMPLOYEE foreign key (EMPNO)
      references EMPLOYEE (EMPNO)
)
go

При разбиении таблицы, для которой были определены ограничения ссылочной целостности, можно также рассмотреть возможность поддержки ссылочной целостности установлением взаимосвязи "один к одному" в сторону исходной таблицы от новой таблицы. В этом случае новая таблица будет иметь внешний ключ, идентичный первичному ключу таблицы. Например, связь "один к одному" между таблицами на рис. 19.14.

Рис. 19.14. Взаимосвязь "один к одному" между таблицами "Текущие проекты" (PROJECT_CUR) и "Архивные проекты" (PROJECT_OLD)

Если определено каскадное правило удаления для этого внешнего ключа (см. "Знакомство с CASE инструментом" ), то СУБД будет автоматически удалять строки новой таблицы, когда соответствующая строка исходной таблицы будет удалена, хотя управление вставкой строк придется перенести в приложение ХД. Команда SQL для этого случая приведена ниже.

create table PROJECT_OLD (
   PROJNO               char(8)              not null,
   PROJ_NAME            char(40)             not null,
   BUDGET               decimal(9,2)         not null,
   constraint PK_PROJECT_OLD primary key (PROJNO),
   constraint FK_EMP_PROJ_REFERENCE_PROJECT_OLD_1 foreign key (PROJNO)
      references PROJECT_СUR (PROJNO)
)
go

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

• определить, какие колонки исходной таблицы в какие новые таблицы будут перемещены;
• создать новые таблицы с первичным ключом, идентичным первичному ключу исходной таблицы;
• если СУБД будет управлять декларативной ссылочной целостностью для новых таблиц таким же образом, как и для исходной таблицы, в случае если она является дочерней таблицей во взаимосвязи, следует добавить колонку внешнего ключа каждой родительской таблицы во взаимосвязи в новую таблицу, т.е. новая таблица должна содержать ограничение внешнего ключа, идентичное родительской таблице, для каждой взаимосвязи. Альтернативой этому решению является создание связи "один к одному" между новой таблицей и исходной таблицей, определение внешнего ключа обратно к исходной таблице тождественным первичному ключу;
• если СУБД будет управлять ссылочной целостностью для новых таблиц таким же образом, как и для исходной таблицы, в случае если она является родительской таблицей во взаимосвязи, то следует добавить внешний ключ в каждую дочернюю таблицу исходной таблицы, чтобы идентифицировать эту новую таблицу как дочернюю. Можно так же поступить, как в первой части предыдущего пункта, при этом новые таблицы не следует объявлять как родительские в других взаимоотношениях. Исходная таблица поддерживает все взаимосвязи, в которых она выступает родителем;
• следует прописать для разработчиков приложений все команды INSERT для полученных в результате разбиения таблиц или указать правила, которым должна следовать вставка строк в эти таблицы;
• следует изменить все представления, которые основывались на исходной таблице, и, если нужно, рассмотреть создание новых представлений для доступа к новым таблицам.

Объединение таблиц базы данных

Объединение таблиц (Table collapsing) является процессом перемещения строк нескольких таблиц в одну, новую таблицу для ограничения числа соединений таблиц БД и улучшения производительности запросов.

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

Рассмотрим примеры.

Пример. 13.8.

Предположим, что было принято решение об объединении таблиц "Покупатель" (Customer) и "Заказ" (Order), — их необходимо объединить, чтобы исключить операцию соединения в запросах к этим таблицам. Физическая модель данных до объединения приведена на рис. 19.15.

увеличить изображение
Рис. 19.15. Таблицы "Покупатель" (Customer) и "Заказ" (Order) до их объединения

В результате объединения таблиц будет создана одна таблица "Покупатель Счет" (Cust_Order), содержащая все колонки объединяемых таблиц ( рис. 19.16).

Рис. 19.16. Таблица "Покупатель Счет" (Cust_Order), объединяющая таблицы "Покупатель" (Customer) и "Заказ" (Order)

Команда SQL для создания объединяющей таблицы приведена ниже.

create table Cust_Order (
   Order_ID             bigint               not null,
   Cust_ID              bigint               not null,
   Amount               decimal(8,2)         null,
   Delivery             char(40)             null,
   Name                 char(20)             null,
   Address              char(30)             null,
   constraint PK_CUST_ORDER primary key (Order_ID)
)
go

Для ХД целесообразно рассматривать объединение нормализованных таблиц иерархии — например, таблиц измерений, представляющих измерение "Время" ( Time ), как на рис. 19.17. Денормализованная таблица измерений для иерархии "Время" приведена на рис. 19.18.

увеличить изображение
Рис. 19.17. Иерархия таблиц измерения "Время"

Рис. 19.18. Денормализованная таблица иерархии "Время"

Денормализация колонок

Денормализация колонок (Column denormalization) является процессом для ограничения числа часто встречающихся соединений таблиц БД и улучшения производительности запросов.

Рассмотрим следующий пример.

Пример 19.9.

Пусть задана физическая модель данных ( рис. 19.19).

Рис. 19.19. Таблицы физической модели данных

Допустим, что в отчете необходимо напечатать колонки "Название отдела" (Div_name) на платежной расписке из таблицы "Платежная расписка" (Pay Slip) каждого служащего. Для решения этой задачи можно рассмотреть вопрос о денормализации колонок таблицы, чтобы иметь колонку "Название отдела" (Div_Name) в таблице "Платежная расписка" (Pay Slip). Перенесем для этого колонку "Название отдела" (Div_Name) ( нисходящая денормализация ) в таблицу "Платежная расписка" (Pay Slip). Физическая модель данных таблицы "Платежная расписка" (Pay Slip) приведена на рис. 19.20.

Рис. 19.20. Нисходящая денормализация таблицы "Платежная расписка" (Pay Slip)

Таким образом, мы сократим число соединений при совместном использовании в отчетах колонок таблицы "Платежная расписка" (Pay Slip) и колонки "Название отдела" (Div_Name).

Резюме

В настоящей лекции мы рассмотрели процесс денормализации таблиц физической БД на уровне моделирования физической модели данных, как для БД, так и для ХД.

Денормализация представляет собой набор методов настройки физической модели БД, используемой для реализации ХД, с целью удовлетворения требований к производительности ХД. Эти методы представляют собой набор рекомендаций и эвристических правил по изменению физической структуры БД, которая была получена в результате первой итерации создания физической модели данных ХД. Применение этих методик носит рекомендательный характер.

В этой лекции были описаны различные типы денормализации и методы ее реализации. Кроме того, было рассмотрено, как при денормализации обеспечить целостность данных, не прибегая к созданию дополнительного кода.

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

В большинстве случаев необходимость денормализации становится очевидной лишь на этапе проектирования приложений ХД или его эксплуатации. Другими словами, нельзя принять решение о денормализации на основании одной только модели данных. Обычно стараются найти в приложениях ХД критичные процессы и принимать решения о денормализации в основном в пользу этих процессов. Критичные процессы, как правило, определяют по высокой частоте, большому объему, высокой изменчивости или явному приоритету. Качественное описание транзакций БД позволяет определить наличие таких критических процессов.