Создание физической модели базы данных. Учет влияния транзакций

Горизонтальное разбиение таблиц

На практике горизонтальное разбиение применяется для изоляции одной группы строк таблицы от другой, когда такие группы строк редко используются в одной транзакции. Наиболее типичный пример, когда этот метод оказывается полезным, есть изоляция текущих данных от архивных данных. Рассмотрим систему обработки заказов. Менеджеры и продавцы работают с текущими заказами. Обработка выполненных заказов (архивные данные) выполняется при подготовке разного рода отчетов. Даже если готовится ежедневный отчет с использованием архивных данных, то в организациях среднего размера частота использования текущих данных все равно превышает частоту использования архивных данных на 2-3 порядка, а отношение объема текущих данных к архивным данным может составлять менее 0,001.

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

Пример. Для нашей учебной базы данных таблицей - кандидатом на такое горизонтальное разбиение является таблица PROJECT, поскольку в ней имеются архивные данные - выполненные проекты. Предположим, что число выполненных проектов в год в организации где-то около 1000. Данные в таблице нужно хранить 10 лет (10000). Средняя продолжительность проекта равна 2 месяцам, т.е. число незавершенных проектов в каждый момент времени не превышает 200. Через 5 лет отношение числа текущих проектов к архивным проектам достигнет 0,04. Следовательно, проектировщик данных может рассмотреть вопрос о горизонтальном разбиении этой таблицы.

CREATE TABLE PROJECT 
(
	PROJNO  	char(8) NOT NULL,
	PNAME  	char(25),
	BUDGET  	dec(9,2),
	PRIMARY KEY (PROJNO)
);

CREATE TABLE PROJECT_OLD 
(
	PROJNO  	char(8) NOT NULL,
	PNAME  	char(25),
	BUDGET  	dec(9,2),
	PRIMARY KEY (PROJNO)
);

А для совместного использования двух этих таблиц предусмотреть представление

CREATE VIEW ALL_PROJECT
AS
SELECT * FROM PROJECT
UNION
SELECT * FROM PROJECT_OLD;

Замечание. Далее под исходной таблицей понимается и сама таблица, и то, что от нее осталось после разбиения.

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

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

Пример. Для нашей учебной базы данных для разрешения отношения "многие-ко-многим" между таблицами EMPLOYEE и PROJECT была введена связывающая таблица EMP_PRJ, которая имеет ограничения ссылочной целостности с таблицей PROJECT. Но у нас появилась еще таблица PROJECT_OLD.

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

CREATE TABLE EMP_PRJ
( 
	EMPNO 	integer NOT NULL,
	PROJNO 	char(8) NOT NULL,
	WORKS 	number,
	PRIMARY KEY (EMPNO, PROJNO),
	FOREING KEY (EMPNO) REFERENCES EMPLOYEE,
	FOREING KEY (PROJNO) REFERENCES PROJECT,
	FOREING KEY (PROJNO) REFERENCES PROJECT_OLD
);

При разбиении таблицы, для которой были определены ограничения ссылочной целостности, можно также рассмотреть возможность поддержки ссылочной целостности установлением взаимосвязи "один-ко-одному" в сторону исходной таблицы от новой таблицы. В этом случае новая таблица будет иметь внешний ключ, идентичный первичному ключу таблицы. Если определено каскадное правило удаления для этого внешнего ключа, то СУБД будет автоматически удалять строки новой таблицы, когда соответствующая строка исходной таблицы будет удалена, хотя управление вставкой строк придется перенести в приложение базы данных.

Пример. Для нашего примера окончательный код может быть следующим:

CREATE TABLE PROJECT 
(
	PROJNO  	char(8) NOT NULL,
	PNAME  	char(25),
	BUDGET  	dec(9,2),
	PRIMARY KEY (PROJNO)
);

CREATE TABLE PROJECT_OLD 
(
	PROJNO  	char(8) NOT NULL,
	PNAME  	char(25),
	BUDGET  	dec(9,2),
	PRIMARY KEY (PROJNO)
	FOREING KEY (PROJNO) REFERENCES PROJECT
);

CREATE TABLE EMP_PRJ
( 
	EMPNO 	integer NOT NULL,
	PROJNO 	char(8) NOT NULL,
	WORKS 	number,
	PRIMARY KEY (EMPNO, PROJNO),
	FOREING KEY (EMPNO) REFERENCES EMPLOYEE,
	FOREING KEY (PROJNO) REFERENCES PROJECT,
);

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

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

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

   или

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

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

   или

   Если поступить, как в первой части пункта 3, то новые таблицы не следует объявлять как родительские в других взаимоотношениях. Исходная таблица поддерживает все взаимосвязи, в которых она выступает родителем.
5. Следует прописать для разработчиков приложений все команды INSERT для полученных в результате разбиения таблиц, или указать правила, которым должна следовать вставка строк в эти таблицы.
6. Следует изменить все представления, которые основывались на исходной таблице, и, если нужно, рассмотреть создание новых представлений для доступа к новым таблицам.

Литература: [7], [20], [23], [42], [45].