Замена длинных каскадных ключей суррогатными

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

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

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

В таком случае стоит рассмотреть возможность ввода в таблицу суррогатного первичного ключа. Его обычно называют <сокращенное_название_таблицы >_ID или просто ID, а значения этого ключа генерируются, как правило, при помощи последовательности.

Таблицы с большими составными первичными ключами часто образуются из длинной иерархии сущностей, у которых ключи наследуются. Другими словами, когда для нескольких уровней иерархии внешний ключ родительской таблицы образует часть первичного ключа дочерней. Это иллюстрируется на рис. 6.3 (обратите внимание на переход от COUNTRY к BUILDING_CONTRACTOR).

Рис. 6.3. "Длинное " наследование компонента первичного ключа

В этом примере сущность COUNTRY ("Страна") имеет простой атомарный ключ — название страны. Сущность REGION ("Область") внутри страны уникально идентифицируется ее отношением с сущностью COUNTRY (столбец внешнего ключа F_CO_NAME) и названием области. Это необходимо потому,что в двух странах могут быть области с одинаковыми названиями. К тому времени, когда мы доберемся до города (TOWN), который идентифицируется комбинацией названия области (REGION) в пределах страны (COUNTRY) и названия города, ключ будет состоять из трех компонентов. Сущность STREET ("Улица") будет иметь четыре компонента, сущность HOUSE ("Дом") — пять и т.д. Чтобы сослаться на конкретный дом в таблице BUILDING CONTRACTOR ("Строительный подрядчик"), мы должны иметь пять компонентов внешнего ключа. Не кажется ли вам, что это многовато? В табл. 6.1 представлены таблицы, которые построены по схеме "сущность-отношение", изображенной на рис. 6.3, и содержат некоторые данные (по одной строке в каждой).

Таблица 6.1. Определения таблиц, демонстрирующие длинный каскадный ключ

COUNTRIES ("Страны ")

NAME	POPULATION	Area	GNP
United Kingdom			$758 billion

REGIONS ("Области ")

F_CO_NAME	NAME	METROPOLITAN_F
United Kingdom

TOWNS ("Города")

F_CO_NAME	F_REG_NAME	NAME
United Kingdom	Surrey	Guildford

STREETS ("Улицы")

F_CO_NAME	F_REG_NAME	F_TN_NAME	NAME
United Kingdom	Surrey	Guildford	The Rise

HOUSES ("Дома")

F_CO_NAME	F_REG_NAME	F_TN_NAME	F_ST_NAME	NO	F_OWN_ID
United Kingdom	Surrey	Guildford	The Rise

(Таблица BUILDING CONTRACTORS ("Строительные подрядчики") не показана.)

Как видно, в результате каждый столбец таблицы HOUSES является компонентом первичного ключа. Как упростить эту ситуацию? Конечно, можно снабдить таблицу HOUSES суррогатным ключом. Но предположим, требуется, чтобы по имени владельца можно было найти (с какой-то неясной целью) самый большой номер дома этого владельца на улице. При использовании представленной в табл. 6.1 структуры необходимый для этoro SQL-запрос будет выглядеть так:

SELECT MAX (hs1.no)
FROM houses hs1
WHERE EXISTS
(SELECT NULL
FROM houses hs2
WHERE hs2.f_own_id = 2061
AND hs1.f st_name = hs2.f_st_name
AND hs1.f_tn_name = hs2.f_tn_name
AND hs1.f_reg_name = hs2.f_reg_name
AND hs1.f_co_name = hs2.f_co_name);

Введем в таблицу STREETS суррогатный ключ и посмотрим, что изменилось. В табл. 6.2 представлены новые определения таблиц (определения, оставшиеся без изменений, не показаны).

Таблица 6.2. Новые определения таблиц. В таблицу STREETS введен суррогатный ключ

STREETS (Улицы)

ID	F_CO_NAME	F_REG_NAME	F_TN_NAME	NAME
	United Kingdom	Surrey	Guildford	The Rise

HOUSES (Дома)

ID	NO	F_OWN_ID

Теперь SQL-запрос для поиска максимального номера дома данного владельца на улице несколько упростился:

SELECT MAX (hs1.no)
FROM houses hs1
WHERE EXISTS
(SELECT NULL
FROM houses hs2
WHERE hs2.f_own_id = 2061
AND hs1.street_id = hs2.street_id);

Но получается, что ввод суррогатного ключа не дал нам ничего, кроме упрощения SQL-запроса. Однако не следует отказываться от преимуществ, которые дает такое упрощение, — этот запрос сжат и поэтому прост для чтения и понимания, его легче разбирать, и вероятность ошибок при вводе ниже.

Если использовать в качестве внешних ключей длинные, а не суррогатные ключи, то строки в дочерних таблицах будут длиннее. "Ну и что, ведь диски стоят дешево", — скажете вы. Однако следует учитывать, что, как бы ни были дешевы диски, на чтение данных с диска в память все равно нужно время. Чем длиннее строки, тем больше блоков придется просмотреть при сканировании. Это же можно сказать и об индексах, а диапазонное сканирование индекса является важной частью любой операции соединения, в которой используется внешний или частичный ключ. Кроме того, при более коротких ключах у некоторых индексов сокращается число уровней в В*-дереве, что приводит к еще большему росту производительности.

Итак, запомните простое правило: чем короче ключи, тем быстрее работают приложения.

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

SELECT MAX (hs1.no)
FROM houses hs1
WHERE hs1.f_co_name = 'United Kingdom';

Уменьшить степень влияния каскадных ключей можно с помощью сокращенных названий — например, в предыдущем примере вместо United Kingdom в качестве значения внешнего ключа использовать UK. Создание эквивалентного запроса для модели с суррогатными ключами оставляем вам в качестве упражнения, но поверьте, что изящным его не назовешь.

При использовании длинных каскадных ключей возникает еще одна проблема — в случае, когда ключевые столбцы где-то в цепочке нужно обновить. Естественно, чем выше уровень иерархии, на котором происходит это изменение, тем больше объем каскадно распространяющихся обновлений. В наше время ни в коем случае нельзя зависеть от статических вещей! Изменяются даже страны - об этом свидетельствует распад Советского Союза! Давайте рассмотрим не сложный пример, связанный с распадом или объединением, а более простой, когда страна изменяет свое название: Персия стала Ираном, Родезия - Зимбабве и т.д. В нашей модели данных нельзя обойтись простым обновлением таблицы COUNTRIES, обновление необходимо каскадно распространить на таблицы REGIONS, TOWNS STREETS, HOMES и т.д.

Реляционные пуристы скажут, что нельзя обновлять столбцы первичного ключа, поэтому нужно удалить все адреса в Родезии и вставить их в Зимбабве. Предоставляем вам право самим решить, можно обновлять первичный ключ или нет. У обеих сторон в этом споре есть достаточно веские аргументы достаточно сказать, что Oracle7 позволяет осуществить обновление первичного ключа без каскадного распространения изменений во внешние ключи некоторые другие реляционные системы управления базами данных обновление первичного ключа попросту запрещают. Если в таблице есть возможные первичные ключи (т.е. более одного обязательного уникального ключа), то, конечно, мы считаем, что в качестве первичного следует, выбрать тот, который не изменяется.

Естественно, распадаться, объединяться и изменять названия могут не только страны, но и области, города и улицы. Поэтому если давать всем им, которым из них или только некоторым из них суррогатные ключи, то каким образом их выбрать? Конечно, чем выше уровень в иерархии, на котором происходит изменение, тем больше объем каскадно распространяющихся изменений. Использование суррогатного ключа означает что мы будем свободны от реляционных ограничений, если захотим изменить исходный уникальный идентификатор.

Когда мы имеем дело с иерархией, которая подобна вышеописанной иногда кажется, что решение о том, где вводить суррогатный ключ, может быть произвольным. Однако вспомните, что проектирование базы данных тесно связано с функциональными требованиями. Для реализации некоторых функций может потребоваться "подъем" по иерархии. Если каскадные ключи ведутся, то такой подъем может быть трансформирован в "прыжок" при котором убираются посредники и выполняется более оптимальный запрос. Это можно продемонстрировать путем соединения таблицы HOUSES непосредственно с таблицей REGIONS (минуя таблицы STREETS и TOWNS).

SELECT MAX (hs.no)
FROM houses hs
,regions reg
WHERE hs1.f_reg_name = reg.name
AND hs.metropolitan_f = 'N';

Цель этого запроса — найти самый большой номер дома в немуниципальной области (еще один запрос из архива фирмы "Бесцельные запросы"),

Дата добавления: 2015-08-09