Не помещайте все коды в одну таблицу

Бывают случаи, когда некий бездарный способ действия настолько распространен, что получает собственное имя. В особо тяжелых случаях, как и положено болезни, вместо имени применяется аббревиатура. Впервые мне пришлось столкнуться с болезнью OTLT (One True Lookup Table, единая истинная таблица кодов) в 1998 г., в одном из форумов CompuServe, и с тех пор в различных группах новостей я вижу ее ежегодно.
Суть этой болезни в том, что вместо создания собственной таблицы для каждой кодировки, мы все кодировки помещаем в одну огромную таблицу. Схема этой таблицы выглядит так:

CREATE TABLE OTLT (

тип_кода INTEGER MOT NULL,

код VARCHAR(n) NOT NULL,

определение VARCHAR(m) NOT NULL,

PRIMARY KEY (тип_кода, код));

На практике числа m и n обычно равны чему-то вроде 255 или 50 — значениям по умолчанию для данного SQL-продукта.
Оправдать объединение всех кодировок в одну таблицу обычно пытаются тем, что в этом случае для поддержания всех кодировок программисту достаточно написать одну интерфейсную программу. Но это полный маразм, которому ни в коем случае нельзя поддаваться. Прежде чем читать следующие разделы, попробуйте самостоятельно составить список недостатков этой методики, а потом сверьте с моим списком. Не упустил ли я чего-нибудь?
1. Нормализация.Если взглянуть в корень, то этот подход не оправдывает себя, так как представляет собой попытку нарушить требования первой нормальной формы. Я, конечно, вижу, что у таблицы OTLT есть первичный ключ и что все столбцы в базе данных SQL должны быть скалярными и принадлежать к одному типу данных. И тем не менее я уверенно утверждаю, что эта таблица не приведена к первой нормальной форме. Тот факт, что два домена используют один и тот же тип данных, еще не делает их одним и тем же атрибутом. Добавочный столбец “тип_кода” меняет домен других столбцов и тем самым противоречит требованиям первой нормальной формы, поскольку не является атомарным. Таблица должна моделировать один набор сущностей или одно отношение, а не сотни их. Как говорил Аристотель: “Быть чем-то значит быть чем-то конкретным. Не быть чем-то конкретным илибыть чем-то вообще значит быть ничем”.
2. Объем памяти.Для хранения таблицы OTLT требуется больше памяти,чем для отдельных таблиц с кодировками — из-за лишнего столбца стипом кода. Представьте себе, что вы объединили в одной таблице международную классификацию болезней (ICD) и библиотечную классификацию DDC. Каждый раз при переходе к другой кодировке вам придется извлекать таблицу OTLT целиком.
3. Типы данных.Всем кодировкам насильственно приписан один типданных: строка максимальной длины, которая может потребоваться в настоящем или будущем для одной из кодировок таблицы. Тип VARCHAR(n) не всегда представляет собой лучший способ хранения данных. Кто-нибудь обязательно вставит в таблицу огромную строку, которая замечательно выглядит на экране, но в своей невидимой правой части содержит лишние пробелы или другие символы. Во многих SQL-продуктах с точки зрения хранения и доступа к данным предпочтительнее применять тип CHAR(n). С числовыми кодами можно выполнять арифметические операции, проверять диапазоны, контрольные цифры и т.п. с помощью ограничений СНЕСК(). Коды в виде дат можно преобразовывать в названия праздников и других событий. Не существует универсального типа данных, который подходил бы ко всем случаям. Если в одной кодировке допустимы значения NULL, в таблице OTLT они будут допустимы во всех кодировках.
4. Проверка.Единственный способ применить ограничение CHECK() в таблице OTLT — написать огромную конструкцию CASE:

CREATE TABLE OTLT

(тип_кода CHAR(n) NOT NULL

CHECK (тип_кода IN (<тип 1>, .... <тип n>)),

код VARCHAR(n) NOT NULL

CHECK (CASE WHEN тип_кода = <тип 1>

AND <проверка 1>

THEN 1

--предполагаем, что в вашем SQL-продукте размер CASE неограничен

WHEN тип_кода = <тип п>

AND <проверка п>

THEN 1

ELSE О END = 1),

определение VARCHAR(m) NOT NULL,

PRIMARY KEY (тип_кода, код));

Это означает, что проверка будет занимать очень много времени, поскольку любое изменение нужно будет проверять во всех конструкциях WHEN, пока SQL не найдет то из них, что возвращает TRUE. Придется также добавить ограничение CHECKQ в описание столбца “тип_ко-да”, чтобы пользователь не мог создать ошибочную кодировку.
5. Гибкость.В таблице OTLT предусмотрен один столбец для кода, стало быть, ее нельзя использовать для кодов, состоящих из n значений,если n > 1. Допустим, если я хочу преобразовывать в название местности пару координат “широта-долгота”, мне придется добавить в таблицу еще один столбец.
6. Обслуживание.В разных кодировках возможно одно и то же значение кода, поэтому вам придется постоянно следить за тем, с какой именно кодировкой вы работаете. Например, как код 1CD, так и код DDC имеют одну структуру — три цифры, точка, три цифры.
7. Безопасность.Чтобы запретить некоторым пользователям просмотр одной из кодировок, к таблице OTLT придется добавить представления,в которых с группой пользователей сопоставлялся бы тип кода, который им разрешено изменять. В этом случае от оправдания единой таблицы вообще мало что остается: в интерфейсе теперь приходится иметь дело с несколькими представлениями почти так же, как пришлось бы работать с несколькими таблицами.
8. Отображение.В интерфейсную программу приходится передавать все кодировки без исключения, что связано с большими накладными расходами и является потенциальным источником ошибок.

Храните коды в базе данных

Таблицы с кодами должны быть частью БД. Их можно использовать для проверки вводимых данных, для преобразования кодов, для создания документации.
В 1993 г. я был потрясен, увидев, как в одной из крупнейших больниц Лос-Анжелеса служащая по старинке искала коды болезней в огромной амбарной книге, вместо того чтобы вывести их на экран терминала. В больнице все еще работали старые мэйнфреймы IBM с терминалами 3270, на которых оператор даже справочную систему не мог вызвать. В системе “клиент-сервер” таблицу с кодами для уменьшения сетевого трафика можно загрузить на отдельные рабочие станции. Еще лучше сделать на основе этой таблицы раскрывающийся список, чтобы уменьшить вероятность опечаток. Изменения в кодах в этом случае не заставят переписывать приложение. Если коды меняются со временем, в таблицу можно включить столбцы с датами начала и окончания действия кода. Это позволит корректно обращаться не только к актуальным, но и к старым данным.

Многосимвольные кодировки

Некоторые СУБД поддерживают кодировки ASCII, EBCDIC и Unicode. Вам необходимо знать об этом, чтобы корректно настраивать сравнения и нормализацию текста.
Предикат “<строка> IS [NOT] NORMALIZED” в стандарте SQL-99 констатирует, относится ли строка Unicode к одной из четырех нормальных форм (т. е. D, С, KD и КС). Термин нормальная форма имеет здесь значение, отличное от его применения в реляционном контексте. В модели Unicode один символ может составляться из нескольких других символов. Над некоторыми латинскими буквами могут вставляться диакритические знаки.
Определенные последовательности букв заменяются лигатурами. В некоторых языках, например корейском и вьетнамском, иероглифы составляются в результате объединения символов как по вертикали, так и по горизонтали. В некоторых языках одна и та же буква отображается по-разному в зависимости от положения в слове, например сигма в греческом или и с акцентом в чешском. Говоря коротко, писать — это не просто ставить буквы друг за другом.
В стандарте Unicode определен порядок таких конструкций в соответствующих нормальных формах. Один и тот же видимый результат можно получить с помощью разного расположения символов или даже с помощью разных наборов символов, но при организации поиска по тексту вам удобнее будет знать, что текст нормализован, чем пытаться анализировать буквы, лигатуры, иероглифы “на лету”. За подробностями и бесплатным ПО обращайтесь на сайт www.unicode.org.

ГЛАВА 6.
Выбор подхода к написанию кода

Цезарь: Прости его, Теодот. Он варвар и полагает,
что обычаи его острова суть законы природы.
Цезарь и Клеопатра, Дж. Бернард Шоу, 1898
{Перевод М. Богословской и С. Боброва.
Полн. собр. пьес в 6 т. Т. 2 - Л.: Искусство, 1979}

В этой главе обсуждается написание хороших DML-выражений в стандартном SQL. Это означает, что они переносимы и поддаются оптимизации на большинстве диалектов SQL. Под переносимостью я понимаю одно из нижеследующего. Либо код стандартизован и может запускаться без редактирования на других диалектах SQL стандарт подразумевает переносимость. Либо для использования в другой реализации SQL код нуждается лишь в простой механической правке, поскольку использованные в нем функции настолько универсальны, что в том или ином виде имеются на большинстве платформ: переносимость не подразумевает стандартности. Описание этой концепции вы найдете в руководстве по созданию переносимых кодов “X/Open SQL Portability Guides”.
Главная проблема людей, начинающих программировать на SQL, состоит в том, что они не могут забыть процедурное или объектно-ориентированное программирование, которому учились раньше. Они не учатся мыслить в терминах наборов данных и предикатов, а попросту воспроизводят решения, к которым привыкли в своем первом языке программирования. Джерри Вайнберг (1978) отметил этот факт еще 25 лет назад в классической книге Psychology of Computer Programming. Он преподавал язык PL/I (справка для самых молодых читателей: разработанный IBM и некогда очень популярный язык PL/I являлся гибридом Фортрана, Кобола и Алгола). Так вот, Вайнберг обнаружил, что легко может определить первый язык программирования, изученный студентом, глядя на то, как он работает с PL/I. Я сам в 1989 г. столкнулся с тем, что могу угадать национальность студента по его программе на Си или Паскале.
Вторая проблема заключается в том, что человек учится программировать на определенном SQL-диалекте и думает при этом, что его диалект является стандартом. В 2004 г. я проходил собеседование по поводу приема на работу. Мне предложили оценить различные платформы с точки зрения предстоящего существенного увеличения объема баз данных компании. На протяжении всего собеседования интервьюер задавал мне “общие” вопросы по SQL, основанные на архитектуре хранения данных в том единственном SQL-продукте, который он знал сам.
Его продукт не предназначался для работы с очень большими базами данных (Very Large Database, VLDB), сам он ничего не знал о Nucleus, Teradata и других продуктах для VLDB. На протяжении всей карьеры он разбирался в тонкостях одной версии одного продукта и не мог переключиться на что-либо другое даже концептуально. Понятно, что перспектив служебного роста у него нет.
Конечно, всегда найдется поле деятельности и для специалистов по программированию на определенном диалекте, но использование нестандартного диалекта должно быть последним средством, к которому следует прибегать лишь в исключительных случаях и никогда — при первом знакомстве с SQL. Это все равно как хирургия: опухоль, которая не поддается лечению другими способами, нужно вырезать, но нельзя хвататься за скальпель, когда пациент приходит к вам с обыкновенным волдырем.