Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 23 September 2019 12:50:04


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

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

Brian Walker (оригинал: A surrogate key architecture to perform powerful database operations)
Перевод Моисеенко С.И.

Часть 1    Часть 2   Часть 3   Части 4,5  

Часть 1: Зачем нужно использовать суррогатные ключи
Часть 2: Архитектура суррогатных ключей
Часть 3: Хранимые процедуры: создание и удаление ограничений и индексов
Часть 4: Хранимая процедура: проверка наличия подчиненных строк
Часть 5: Хранимая процедура: проверка связанных данных

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

Часть 1. Зачем нужно использовать суррогатные ключи

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

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

Представим приложение с сеткой родительских строк. Щелкаем на родительской строке, чтобы ввести новую дочернюю строку. Форма ввода отображает соответствующие данные родительской строки и принимает вход данных дочерней строки. Щелчок на " Save (сохранить)", и все в порядке, правильно? Ё! Разработчик допустил ошибку в коде. Приложение готовится сохранить дочернюю строку и случайно захватывает данные родительской строки непосредственно выше правильной строки в сетке. Новая дочерняя строка содержит внешний ключ, соединяющий ее с неправильной родительской строкой! Есть ли вообще какая-нибудь разница в том, что захваченные неправильно данные представляют собой естественный 4-столбцовый ключ, а не одностолбцовый суррогатный ключ?

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

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

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

Я полагаю, что суррогатные ключи имеют стабильность и преимущество в производительности по сравнению с естественными ключами для большинства современных систем реляционных баз данных. Однако именно последовательность в универсальности применения суррогатных ключей делает эту архитектуру столь привлекательной для меня. Некий запрос в архитектуре естественных ключей может соединять восемь таблиц со структурой соединения, являющейся радикально различной для каждой пары таблиц, в зависимости от того, как определены связи (ссылки внешних ключей). Тот же самый запрос в архитектуре суррогатных ключей мог бы соединять 10 таблиц, но структура соединения будет короткой и предсказуемой для каждой пары таблиц.

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

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

Представьте себя администратором баз данных компании. Клиентами вашей компании являются другие компании. Каждый клиент имеет несколько контактов. Каждый контакт имеет несколько адресов и телефонных номеров. Каждый телефонный номер имеет журнал звонков. Каждый клиент делает много заказов. Каждый заказ включает товары. Некоторые товары имеют журнал обслуживания. Каждый элемент журнала может включать нескольких технических специалистов. Каждый специалист … Достаточно, Вы получили представление. Вообразите теперь, что есть 10 уровней каскадных таблиц, исходящих от клиентов в вашей базе данных. Далее предположим, что одна из клиентских компаний выходит из бизнеса, и Вы хотите скопировать ее данные в архивную базу данных и удалить ее из рабочей базы данных. Как вам выполнить копирование? Я задал такой вопрос нескольким профессионалам в области баз данных, и получил массу ответов. Некоторые предложили использовать DTS. Другие - написать код на SQL. Третьи используют генератор операторов INSERT. Все эти варианты достаточно болезненны. Я знаю только об одном коммерческом продукте, о котором утверждается, что он в состоянии выполнить такое копирование, и этот продукт является чрезвычайно дорогим. Вот то, что я сделал бы с нашей корпоративной базой данных:

EXECUTE CopyBranch 'Archive','Production','Customer','CustomerID',@CustomerID

Хранимая процедура CopyBranch – процедура собственного производства, которая может скопировать любую ветвь нашей корпоративной базы данных. В этом примере она стартует с единственной строки в таблице Customer и копирует все связанные с ней строки из всех нисходящих таблиц. При этом не имеет значения, сколько всего таблиц вовлечено в этот процесс или насколько глубоко простирается структура. Эта процедура представляет для нас очень мощный инструмент. Мы можем легко скопировать подмножество связанных данных в архивную базу данных, базу данных разработки или демонстрационную базу данных. Эта способность стала возможной при наличии очень последовательной архитектуры ключей.

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

Часть 1    Часть 2   Часть 3   Части 4,5  

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.