Статьи Королевства Дельфи

Моделирование данных


Раздел Подземелье Магов

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

Автор: Сергей Королев.

Часть I: Определение нормальных форм.

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

  1. Первая нормальная форма.
    Говорят, что таблица соответствует первой нормальной форме, если в каждом поле каждой ее строки содержится ровно одно значение. Ответ на вопрос что такое «ровно одно значение» может дать только постановка задачи и ее анализ. Например, в одной задаче имя, отчество и фамилия человека являются различными значениями, и тогда хранение их в одном поле таблицы нарушает критерий первой нормальной формы. Если разрабатывается система учета кадров, то - поскольку человек может время от времени менять, например, фамилию, - скорее всего, разумно считать имя, отчество и фамилию различными атрибутами. Однако вполне может случиться, что по условиям задачи допустимо считать эти атрибуты одним значением.

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


    Товар (ключ) Склад (ключ) Количество Адрес склада
    Т001 Склад 1 15 Вокзальная ул. д.2
    Т002 Склад 2 20 Ленинский тупик д.1
    Т003 Склад 2 34 Ленинский тупик д.1
    Т004 Склад 3 22 Придорожный пер. д.3
    Здесь ключ таблицы состоит из двух полей - Товар, Склад, при этом значение поля Адрес склада зависит, очевидно, только от значения поля Склад. В результате применения такой модели может возникнуть рассогласованность данных - у одного и того же склада могут появиться различные адреса, а если склад опустеет, то его адрес вообще будет забыт. Разумно эту таблицу разбить на две - в одной хранить количество товаров на складе, в другой - адреса и прочие данные о складе

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

    Табельный № (ключ) Имя Фамилия Отдел Название отдела
    1001 Василий Чапаев Н-11 Продажи
    1002 Павел Морозов Н-23 Маркетинг
    1003 Иван Гадюкин Н-11 Продажи
    В этой таблице значение поля Название отдела зависит от значения неключевого поля Отдел. Последствия те же, что и в предыдущем примере: возможна рассогласованность данных. Этот пример также лечится разбиением таблицы на две - для данных о сотрудниках и для данных об отделах.

  4. Четвертая нормальная форма.
    Например, в базе данных потребовалось хранить информацию о сотрудниках, в том числе о том, на каких музыкальных инструментах он умеет играть, и какие имеет увлечения (хобби). Эти данные можно расположить в одной таблице:

    Сотрудник (ключ) Муз. инструмент (ключ) Хобби (ключ)
    Иванов Гитара Горные лыжи
    Петров Рояль Подводное плавание
    Сидоров Волынка Компьютерные игры
    Очевидно, таблица соответствует определениям первых трех нормальных форм, однако, каждая запись содержит два независимых факта относительно сотрудника - именно это и обуславливает нарушение правила четвертой нормальной формы. Чтобы модель данных соответствовала четвертой нормальной форме, необходимо эту таблицу разбить на две, в одной хранить информацию о владении музыкальными инструментами, в другой - о хобби. Следует иметь в виду, что в процессе проектирования анализ предметной области может выявить зависимости между фактами, и тогда приведение модели к четвертой нормальной форме окажется нежелательным.

* * * Общее неформальное правило, касающееся нормализации, таково: полученная в результате анализа задачи модель данных нормализуется насколько это возможно, затем, если SQL-запросы для отчетов получаются чересчур сложными и/или слишком низка производительность их обработки, приходится "сдавать позиции" и денормализовывать модель.



Продолжение следует…
В следующей серии: отдельные рецепты денормализации, автоинкремент.

Сергей Королев,


Продолжение
Часть II:



Возможно, эта таблица подойдет для записи всех операций с материалами, но, прежде всего пользователям потребуется отчет об остатках материалов по каждому из имеющихся состояний (в пути, разгружено, оприходовано и пр.) По этой таблице этот отчет строить неудобно: на Inter-base для этого придется написать хранимую процедуру, в которой нужно будет объединить результаты двух запросов, в SQL Server, Oracle, DB2 можно сформулировать всего один запрос для вычисления этих цифр: два запроса объединить конструкцией UNION, а затем с помощью select from select или чего-либо подобного задать окончательные агрегатные вычисления. Этот прием, конечно, сработает, но уже на сотне тысяч записей производительность начнет заметно падать. Вообще, сложные запросы - явный признак неудачной модели данных. В данном случае, нашу таблицу нужно перепроектировать. Каждую операцию перемещения будем кодировать не одной, а двумя записями в таблице: NO - номер операции LN_NO - номер позиции в операции DATE - дата TIME - время MATERIAL - идентификатор материала QUANTITY - количество STATE -состояние

Поле LN_NO будет содержать 0 или 1 и будет частью ключа. В записи для исходного состояния количество запишем с отрицательным знаком (это символизирует тот факт, что материал это состояние покинул), в записи для результирующего состояния знак количества будет положительным.

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

№ опер № поз Дата Время Mатериал Кол-во Состояние
2111 1 28.02.2000 12:28 Спички -100 Принят
2111 2 28.02.2000 12:28 Спички 95 Оприходован
2111 3 28.02.2000 12:28 Спички 5 Брак
Такая схема позволит посчитать остатки одним простым запросом: select STATE, SUM(QUANTITY) from operations group by STATE



В реальном приложении этот запрос обрастет множеством дополнительных условий и будет связан с другими таблицами, но его основа останется столь же простой и быстрой. А из двух запросов быстрее обрабатывается тот, что проще.

Задача о курсах валют

Вот простая задача - нужно хранить журнал курсов доллара по отношению к рублю. Казалось бы, все просто - создаем таблицу из двух колонок - дата, курс - и методично ее заполняем. После этого обязательно появится сопутствующая задача: есть таблица с суммами в рублях и датой совершения операции. Нужно одним запросом выдать таблицу, в которой все операции пересчитаны в доллары по курсу на дату совершения операции. Вот очевидное неправильное решение: select op.amount * rt.rate, op.reg_date from operations op, rates rt where op.date = rt.date

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

Как сформулировать запрос правильно, при условии, что мы не храним курсы за все дни? Для такой модели данных это достаточно нетривиальная задача, достойная помещения в рубрику головоломок Джо Селко . Но лучше бороться не с последствиями, а с причинами - поэтому модель данных следует немного изменить.

В таблицу курсов добавим еще одну дату и будем следить за тем, чтобы эти даты отражали срок действия курса. В качестве начального значения дата окончания срока действия будет достаточно отдаленной, например, 31 декабря 9999 (ну или максимальной из представимых в базе данных). Манипуляции с таблицей курсов слегка усложняются - при вставке очередного курса необходимо согласованно пересчитать срок действия курса, в который попал новый курс. Это легко программируется триггером: create trigger ti_rate for rates before insert as begin update rates set rate_date = new.rate_date-1; where new.rate_date between (rate_date and end_date);



/* если есть курсы с более поздней датой */ select rate_date-1 from rates where new.end_date between (rate_date and end_date) into new.end_date; end

Соответствующим образом следует запрограммировать и триггеры, срабатывающие при модификации и удалении записи.

Теперь можно сформулировать запрос: select op.amount * rt.rate, op.reg_date from operations op, rates rt where op.reg_date between

(rt.rate_date and rt.end_date)

Итак, слегка усложнилась работа при «записи» данных - нам пришлось программировать триггеры; в таблице появилось избыточное поле. Но запрос, с помощью которого вычисляются курсы, остался простым, понятным и главное - быстрым.

Суррогатные ключи и автоинкремент

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

Практически все СУБД содержат те или иные средства генерации уникального суррогатного ключа:


  • Interbase - генераторы
  • Oracle - последовательности (sequence)
  • Paradox - автоинкременты
  • MS SQL Server - автоинкременты (identity)
  • DB2 - специальная функция, генерирующая уникальное значение на основе даты и времени на сервере


Автоинкрементное поле обладает несомненными достоинствами для программиста: об его уникальности заботится система - значение увеличивается всякий раз, когда в таблицу вставляется запись. В этом, однако, состоит и недостаток автоинкремента: не вставив в таблицу записи, его очередное значение нельзя получить.

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



То есть алгоритм получается примерно таким:


  1. Пользователь нажимает кнопку «Создать документ»
  2. Старт транзакции
  3. Вставка записи заголовка и получение нового номера документа
  4. Формирование позиций документа
  5. Пользователь нажимает кнопку «Сохранить документ»
  6. Завершение транзакции.


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

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

Наилучшим решением представляется использование механизма получения очередных номеров, независимого от таблиц и транзакций, аналогичного, например, генераторам Interbase. Кстати, если СУБД, на которой вы работаете, не имеет такого механизма, но поддерживает вызов внешних функций, то генераторы a la Interbase достаточно просто разработать самостоятельно. Тогда алгоритм формирования документа станет таким:


  1. Пользователь нажимает кнопку «Создать документ»
  2. Получение очередного номера документа
  3. Формирование записи заголовка и позиций документа
  4. Пользователь нажимает кнопку «Сохранить документ»
  5. Сохранение документа: Старт транзакции, запись в таблицы заголовков и позиций, завершение транзакции.


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

Если вы используете Delphi или C++ Builder, то для реализации подобной схемы подойдут компоненты TClientDataSet и TUpdateSQLProvider.

Сергей Королев

¹ - Здесь и далее используется диалект SQL для СУБД Interbase

² - Joe Celko -SQL-гуру, автор постоянной колонки журнала (бывш. DBMS magazine),
в которой часто публикуются интересные задачи для знатоков SQL.


Содержание раздела