Сергей Королев
дата публикации 28-02-2000 00:00 Моделирование данных. Часть II: Примеры моделирования данных.
- Группировка по дате
- Движение материалов
- Задача о курсах валют
- Суррогатные ключи и автоинкремент
В нормализованную базу проще записывать данные, однако содержательные запросы к таким базам формулируются достаточно сложно: с вложенными подзапросами, с использованием OUTER JOIN или UNION, с большим количеством таблиц, а это в «боевых» условиях приводит к снижению производительности приложений, отвечающих за «чтение» данных из базы.
Требования к приложениям, отвечающим за запись и чтение данных, достаточно противоречивы, и поэтому в одной системе трудно соединить возможности обработки сотен тысяч транзакций в единицу времени и богатые инструменты анализа данных. По этим причинам системы высшего класса содержат подсистему оперативного накопления данных - для этих целей может использовать нормализованная база данных, и подсистему анализа данных - то, что принято называть хранилищем данных (data warehouse). В информационных системах среднего уровня обе функции - быстрой записи и относительного богатства средств анализа - приходится в той или иной степени совмещать.
Чтобы данные было проще читать, мы их денормализуем. Создание модели данных - процесс творческий, тем не менее, встречается довольно много ситуаций, в которых можно применять стандартные решения, проверенные опытом, хотя тот же опыт показывает, что бывает весьма соблазнительно изобрести собственный велосипед, хоть и без руля. В этом тексте приведено несколько проверенных рецептов для решения задач, часто встречающихся в моделировании данных.
Группировка по дате
Если ваша информационная система работает с экономическими данными, то с вероятностью 100% в запросах вам потребуется группировать значения по неделям, месяцам, годам - какому-нибудь сотруднику отдела продаж обязательно захочется узнать, как изменился уровень продаж в текущем квартале по сравнению с прошедшим. В нормализованной таблице для представления даты вы заводите единственное поле и для того, чтобы сгруппировать значения, например, по месяцам, вам будет необходимо указать выражение в разделе GROUP BY. Однако не все СУБД позволят это сделать, и очевидно, что группировка по выражению будет работать медленно. Возможное решение состоит в добавлении нескольких колонок: для номера года, месяца недели и дня. Значения этих полей можно вычислять внутри триггеров before insert и before update. Группировки будут работать максимально быстро.
Если по условиям задачи необходимо группировать по датам таблицы из миллионов записей, то избыточные поля превратятся в заметный фактор, влияющий на скорость исчерпания дискового пространства, и в этом случае есть смысл создать отдельную таблицу для хранения всех значимых дат:
DATE_ID - идентификатор даты (не дата!)
YEAR - год
QUARTER - квартал
MONTH - месяц
DAY - день в месяце
DAY_OF_WEEK - день недели
<…другие атрибуты даты…>
DATE_ID - это не дата, а абстрактный номер даты (суррогатный ключ), на который будут ссылаться записи из других таблиц. На первый взгляд, хочется в качестве ключа использовать собственно дату, однако у такого решения есть определенный недостаток: в этом случае появляется соблазн интерпретировать эту дату в клиентских приложениях (а не пользоваться атрибутами YEAR, MONTH, DAY и пр.) и тем самым увеличить риск семантической рассогласованности приложений.
В схеме с отдельной таблицей для значимых дат очень важно реализовать единый централизованный и надежный механизм начального заполнения и последующего пополнения этой таблицы: вам придется хранить именно все значимые даты, иначе вы рискуете потерять часть данных - записи с не сохраненной в таблице дат датой будут исключены из результатов запроса. Эту проблему можно решить, введя специальное значение date_id для не установленной даты (хоть тот же «0»), и установив его в качестве значения по умолчанию для всех полей date_id. В таблице дат также нужно ввести запись с соответствующим ключом, которая будет содержать заведомо бессмысленные значения атрибутов.
Движение материалов
Задача о движении материалов на предприятии встречается очень часто. Движение заключается в том, что некоторое количество определенного материала изменяет свое состояние, например, товар был в пути, затем его разгрузили, потом оприходовали на склад. Таким образом, когда регистрируется движение, то обычно записывается исходное состояние материала, идентификатор и количество материала, результирующее состояние и, наконец, время совершения операции. Очевидное решение - создать для движений таблицу OPERATIONS, где каждой операции перемещения будет соответствовать одна строка со следующими полями:
NO - номер операции
DATE - дата
TIME - время
MATERIAL - идентификатор материала
QUANTITY - количество
SOURCE_STATE - исходное состояние
DEST_STATE - результирующее состояние
Возможно, эта таблица подойдет для записи всех операций с материалами, но, прежде всего пользователям потребуется отчет об остатках материалов по каждому из имеющихся состояний (в пути, разгружено, оприходовано и пр.) По этой таблице этот отчет строить неудобно: на 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 - специальная функция, генерирующая уникальное значение на основе даты и времени на сервере
Автоинкрементное поле обладает несомненными достоинствами для программиста: об его уникальности заботится система - значение увеличивается всякий раз, когда в таблицу вставляется запись. В этом, однако, состоит и недостаток автоинкремента: не вставив в таблицу записи, его очередное значение нельзя получить.
Вот иллюстрация. В клиент/серверных приложениях сплошь и рядом встречается задача формирования многопозиционных документов на рабочем месте: счетов, накладных и т.п. Такие документы чаще всего моделируют в базе данных двумя таблицами: первая служит для хранения данных заголовка (даты, общей суммы и пр.), а вторая - для хранения позиций документа. Во второй таблице ко всем ключевым полям первой таблицы добавляется номер позиции. Таким образом, чтобы сформировать запись в таблице позиций документа, необходимо знать ключ записи заголовка, который часто и реализуют с помощью автоинкремента.
То есть алгоритм получается примерно таким:
- Пользователь нажимает кнопку «Создать документ»
- Старт транзакции
- Вставка записи заголовка и получение нового номера документа
- Формирование позиций документа
- Пользователь нажимает кнопку «Сохранить документ»
- Завершение транзакции.
Однако следует учесть, что один документ формируется достаточно продолжительное время (по крайней мере, минуты). Держать открытой транзакцию все это время неэффективно: на это время в базе данных может быть блокирована не только сама запись, но и страница, или даже таблица целиком.
Тогда может быть, в одной короткой транзакции создать запись заголовка, узнав тем самым новый номер документа, а затем - в следующей транзакции - спокойно формировать список позиций? Это очень плохое решение, так как в этом случае вы не сможете гарантировать семантическую целостность базы данных.
Наилучшим решением представляется использование механизма получения очередных номеров, независимого от таблиц и транзакций, аналогичного, например, генераторам Interbase. Кстати, если СУБД, на которой вы работаете, не имеет такого механизма, но поддерживает вызов внешних функций, то генераторы a la Interbase достаточно просто разработать самостоятельно. Тогда алгоритм формирования документа станет таким:
- Пользователь нажимает кнопку «Создать документ»
- Получение очередного номера документа
- Формирование записи заголовка и позиций документа
- Пользователь нажимает кнопку «Сохранить документ»
- Сохранение документа: Старт транзакции, запись в таблицы заголовков и позиций, завершение транзакции.
Преимущества этой схемы достаточно очевидны: транзакция открывается только в момент реальной записи документа (т.е. тогда, когда пользователь нажал кнопку «Сохранить»), время ее работы определяется исключительно объемом данных документа и не зависит от настроения пользователя.
Если вы используете Delphi или C++ Builder, то для реализации подобной схемы подойдут компоненты TClientDataSet и TUpdateSQLProvider.
Сергей Королев
¹ - Здесь и далее используется диалект SQL для СУБД Interbase
наверх
² - Joe Celko -SQL-гуру, автор постоянной колонки журнала IntelligentEnterprise (бывш. DBMS magazine),
в которой часто публикуются интересные задачи для знатоков SQL. наверх
[TComponent] [TDataSet] [TUpdateSQL] [TClientDataSet] [Моделирование БД]
 Обсуждение материала [ 21-01-2003 16:11 ] 8 сообщений |
