Подбирайте ключи с подходящими свойствами

Обоснование
Составление списка желаемых свойств ключа — хороший способ проверить дизайн данных.
1. Уникальность первое и наиважнейшее свойство ключа. Без уникальности ключ по определению не ключ. Впрочем, это свойство необходимое, но не достаточное.
Уникальность имеет контекст. Идентификатор может быть уникальным в пределах одной БД, в пределах всех БД предприятия, уникальным вообще. Предпочтителен, конечно, третий вариант.
В промышленности добиться универсальной уникальности просто, например при помощи стандартного кода типа идентификационного номера автомобиля (vehicle identification number, VIN). Уникальность в пределах предприятия обеспечивают коды, подобные местному телефонному номеру или адресу электронной почты. Идентификатор, уникальный в пределах только одной базы данных, особого смысла не имеет, поскольку лишен остальных желаемых свойств.
2. Неизменность второе свойство. Первый тип неизменности — неизменность в пределах схемы. Она необходима как для ключевых, так идля простых столбцов. Одному и тому же элементу данных необходимо одно и то же представление, где бы в схеме он ни появлялся. Он не должен относиться к типу CHAR(n) в одном месте и к типу INTEGER вдругом. К нему должен применяться тот же базовый набор ограничений. Иными словами, если мы используем в качестве идентификатора VIN и наложили на него ограничение, что допустимы только VIN автомобилей “Форд”, то нельзя использовать это ограничение в одной таблице и пренебречь им во всех остальных.
Второй тип неизменности — неизменность во времени. Ключи не должны меняться слишком часто или непредсказуемо. Вопреки распространенному мифу это не означает, что ключи вообще не могут меняться. По мере того как расширяется область их применения, они должны под нее подстраиваться.
К примеру, 1 января 2005 г. США добавили еще одну цифру к штрих-коду UPC, применяемому в розничной торговле. Причинами стали глобализация, снижение уровня американского промышленного превосходства и распространение Европейского артикула (European Article Number, EAN). Тринадцатиразрядным стал и “книжный” код ISBN.
3. Понятность весьма удобно, если пользователь что-то знает о данных. Это еще не контроль корректности, но уже близко к нему. Контроль предполагает, что корректность ключа можно проверить с помощью некоего процесса. Понятность означает, что о корректности кода можно что-то сказать навскидку, поскольку вы осведомлены о контексте. Скажем, кодовое обозначение заболевания ничего не скажет пациенту, но будет вполне понятно медицинскому работнику.
4. Контроль корректности предполагает, что корректность ключа можно проверить без обращения к внешнему источнику. Например, я знаю,что дата 30.02.2004 г. невозможна, поскольку в общепринятом календаре 30 февраля отсутствует. Контрольные разряды и коды фиксированного формата представляют собой два способа проверки корректности.
5. Проверяемость ключа зависит от контекста и уровня доверия. Когда я расплачиваюсь чеком в супермаркете, кассир с готовностью верит, что фотокарточка на водительских правах принадлежит мне, какой бы неудачной она ни оказалась. Но ситуация может измениться: в сети супермаркетов “Kroger” вводится сейчас система сканирования отпечатков пальцев, наподобие той, что уже действует во многих банках. Чтобы получить паспорт, я должен предъявить свидетельство о рождениии пройти процедуру снятия отпечатков пальцев. Тут уровень доверия ниже. Перед предоставлением допуска к секретной информации человека проверяют с особенной тщательностью — уровень доверия существенно ниже.
Ключ без возможности проверки нарушает целостность данных и в конечном итоге ведет к накоплению некачественной информации.
6. Простота. Ключ должен быть максимально простым, но не более того. Чем длиннее ключ, тем больше с ним будет связано ошибок. Правда, хранение и передача длинных ключей уже не представляют такой проблемы, как это было 40 или 50 лет назад.
То, что просто для одного человека, сложно для другого. В качестве примера чрезмерно сложного кода, находящегося в международном применении, можно рассмотреть международный стандартный номер банка (International Standard Bank Number, IBAN). Способ обработки номера IBAN определяется кодом страны в начале строки, которая может содержать до 34 символов (букв и цифр). Почему? Потому что у каждой страны — свои законы, валюта, способ нумерации банковских счетов... По сути, IBAN представляет собой национальный банковский код, спрятанный внутри международного стандарта (см. http://www.ecbs.org/iban/iban.htm).
В наше время все больше становится программистов, которым приходится разрабатывать базы данных, не имея ни малейшего опыта работы с ними. Не зная ничего другого, они старательно имитируют номер записи (пережиток последовательной файловой системы) или идентификатор объекта (последствия знакомства с ООП) при помощи IDENTITY, ROWID и других нестандартных средств автонумерации в SQL-продуктах. Эта магическая, универсальная, безразмерная методика абсолютно не подходит для реляционных БД, зависит от текущего физического состояния оборудования и по сути представляет собой неудачную попытку возрождения магнитной ленты. Опытные дизайнеры БД предпочитают продуманные ключи, основанные на стандартных кодах UPC, VIN, ISBN и т.д. Им известно, что данные необходимо непрерывно поверять реальностью. Проверенный внешний источник для этого незаменим.
Оправданий для халтурного программирования придумано много, приведем основные в виде вопросов и ответов.

Вопрос: Разве естественный составной ключ не может стать очень длинным?
Ответ №1 Ну и что? Размер ключа имел определяющее значение в 1950-х годах, когда в нашем распоряжении были маломощные компьютеры. Но теперь на дворе XXI век! Меня, кстати, всегда забавляет количество идиотов, которые заменяют составной ключ, состоящий из двух-трех целых чисел, на громоздкий код GUID, который не будет понят ни человеком, ни другим компьютером, на том основании, что так легче программировать.
Ответ №2 Эту проблему можно разрешить с помощью подходящей реализации SQL. Например, SQL-продукт компании Teradata предназначен для работы с очень большими БД, и в нем вместо обычных индексов активно применяется хэширование. Фирма гарантирует, что для любого поиска понадобится не более двух просмотров, вне зависимости от размера БД. При использовании древовидного индекса количество просмотров увеличивается по мере роста БД.
Ответ №3 Длинный ключ не всегда отрицательно сказывается на производительности. Я могу, например, с помощью составного ключа получить индекс, включающий все столбцы, необходимые для запроса, так что для выполнения запроса обращение к основной таблице вообще не понадобится.

Вопрос. Разве плохо, что в текущей версии SQL-продукта мое приложение будет работать с максимальной скоростью?
Ответ №1 Я бы, конечно, тоже к этому стремился, если бы хотел потерять все преимущества абстрактной модели данных, копить ненужную информацию и распрощаться с переносимостью кода. Почитайте тематические группы новостей, и вы узнаете, сколько трудностей порождается использованием физических указателей даже в пределах одного продукта.

Не разделяйте атрибуты

Обоснование
Разделение атрибута означает, что вы моделируете один и тот же атрибут в нескольких местах схемы, нарушая правила нормальной формы “доменключ” (Domain-key Normal Form, DKNF) и существенно усложняя программирование. Разделить атрибут можно несколькими способами, описанными в следующих разделах.

Разделение по таблицам

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

Разделение по столбцам

Атрибут моделируется в виде набора столбцов, имеющих смысл лишь совместно (например, в одном столбце стоит число, а в другом — единица измерения). Выход состоит в том, чтобы все числовые данные записывать в едином заранее выбранном масштабе.
Чаще всего подобная неприятность случается с типом данных BIT (см. раздел “Не используйте нестандартные типы данных”). Встретятся вам и попытки форматирования длинных текстовых строк путем разделения, например, одного 100-символьного столбца на два 50-символьных. Обычно таким образом пытаются избавиться от необходимости автоматического разбиения на строки при отображении строки в интерфейсе. Но что вы будете делать, если вам придется печатать тот же текст на устройстве с длиной строки в 25 символов?
Другой распространенный вариант — программировать в таблице динамические изменения домена. При этом один столбец содержит домен (то есть, метаданные) для другого столбца (с данными).
Вот какой показательный пример динамического изменения домена опубликовал в одной из групп новостей по SQL-программированию Гленн Карр (Glenn Carr). Он намеревался вести статистику футбольных матчей; здесь его схема приводится в упрощенном виде. Я убрал из нее около десятка других дизайнерских ошибок, чтобы мы могли сосредоточиться на проблеме динамической смены доменов:

CREATE TABLE Player_Stats

(league_id INTEGER NOT NULL,

player_id INTEGER NOT NULL, -- внутренняя нумерация игроков

game_id INTEGER NOT NULL,

stat_field_id CHAR(20) NOT NULL, -- домен для столбца number_value

number_value INTEGER NULL,

Столбец stat_field_id содержит имя статистики (количество пробежек, число преодоленных ярдов, количество перехватов), значение которого размещено в той же строке, в столбце number_value. Перепишем этот фрагмент:

CREATE TABLE Player_Stats

(league_id INTEGER NOT NULL,

player_nbr INTEGER NOT NULL,

FOREIGN KEY (league_id, player_nbr) REFERENCES Players (league_id, player_nbr) ON UPDATE CASCADE,

game_id INTEGER NOT NULL REFERENCES Games(game_id) ON UPDATE CASCADE,

completions INTEGER DEFAULT 0 NOT NULL CHECK (completions >= 0),

yards INTEGER DEFAULT 0 NOT NULL CHECK (yards >= 0), -- список статистик можно продолжить

PRIMARY KEY (league_id, player_nbr, game_id));

Проверка показывает, что игрок идентифицируется парой параметров (league_id, player_nbr). В исходной таблице Players для идентификации игроков использовался столбец с автонумерацией Player_id типа IDENTITY. Но ведь на форме у каждого игрока есть номер; давайте использовать для идентификации его! Конечно, тут возможна проблема повторного использования номера другим игроком, но я уверен, что в футбольных лигах это делается по определенным правилам, и уж конечно это не те правила автонумерации, что задаются устройствами в компьютере мистера Карра.
В переписанной схеме тривиальным станет вычисление составных статистик, например средней длины пробежки в ярдах. Самой сложной частью кода станет предотвращение деления на ноль. В оригинальном дизайне ту же задачу пришлось бы решать с помощью ресурсоемкого и сложного набора объединений таблицы с самой собой. Оставляю это упражнение читателю.

Исключения
Это не совсем исключение. Можно использовать столбец для изменения единицы измерения (не домена), относящейся к другому столбцу. Допустим, я записываю температуры в градусах Кельвина, Цельсия или Фаренгейта, помещая в соседнем столбце стандартное сокращение единицы измерения. Но мне необходимо отдельное представление для каждой шкалы, чтобы все температуры выражались в градусах Фаренгейта для американцев и в стоградусной шкале для остального мира. Я также хочу, чтобы пользователи могли через представления обновлять температуры в тех единицах, которые им удобны.
Более сложный пример: хранение в базе данных по международным транзакциям денежных сумм и ISO-кодов валют. Домен неизменен; во втором столбце всегда содержится код валюты, а не размер обуви или температура. Необходимо представление, которое переводило бы любую валюту в единую шкалу: евро, йены, доллары или любую другую. Здесь появляется еще зависимость от времени, поскольку курсы обмена все время меняются.

Разделение по строкам

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

CREATE TABLE Events

(event_name CHAR(15) NOT NULL,

event.time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT MULL,

...);

INSERT INTO Events

VALUES (('start running1,'2005-10-01 12:00:00'),

('stop running', '2005-10-01 12:15:13'));

Время — это длительность, а не мгновение. Вот как выглядит правильный DDL -

CREATE TABLE Events

(event_name CHAR(15) NOT NULL,

event_start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

event_finish_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

CHECK (event_start_time < event_finish_time),

..);

INSERT INTO Events

VALUES ('running', '2005-10-01 12:00:00', '2005-10-01 12:15:13');

Исключения
Нет.

Приведенные выше примеры представляют собой просто плохие схемы, возникающие, как правило, в результате невнятного представления данных в виде логической модели. Это случается с программистами старой школы, не избавившимися от прежних привычек. В те давние времена каждая магнитная лента помечалась временем записи, и их обработка основывалась на однозначном соответствии между временем и физическим файлом. Таблицы с именами типа “Зарплата_Январь”, “Зарплата_Февраль” просто имитируют магнитные ленты.
Другой источник ошибок — попытка продублировать в DDL структуру бланка или диалогового окна. Часто оказывается, что в таблицу с информацией о заказе включен номер строки, просто потому что он есть на бумажном бланке заказа. Понятно, что в складской описи заказанный товар идентифицируется по артикулу, коду UPC или другому коду, но никак не по номеру строки на бланке.