© А. Тенцер
Статья была опубликована на сайте "КомпьютерПресс"

• Связи
• Расширение БД
  • Налаживаем учет
  • OperationId
  • ContainerId
  • Amount
  • Цены
• Проблемы и пути их решения
  • Ослабленный контроль за целостностью данных
  • Пониженное быстродействие при выборке данных
  • Сложность в понимании структуры и выборке данных
• Построение клиентской части

Связи

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

Первая из них описывает возможные типы связей, которые могут быть установлены между объектами:

Например, сотрудник может быть связан с отделом, в котором он работает, связью типа «EMPDEPART» (сотрудник в отделе). В то же время он может быть связан с отделом связью «BOSS» (начальник отдела). Со сторонней фирмой сотрудник может быть связан как менеджер, а с документом — как его создатель. Сами связи хранятся в таблице:

Таким образом, чтобы найти менеджера, прикрепленного к фирме, необходимо выполнить запрос:

SELECT O.ItemName

FROM Objects O

INNER JOIN Links L ON O.Id = L.ParentId
INNER JOIN LinkType LT ON LT.Id = L.TypeId

WHERE LT.Code = ‘MANAGER’

AND L.ChildId = :FirmId -- Первичный ключ объекта-фирмы

А всех сотрудников отдела поставок можно найти запросом:

SELECT Employee.ItemName

FROM Objects Employee

INNER JOIN Links L ON L.ChildId = Employee.Id
INNER JOIN LinkType LT ON L.TypeId = LT.Id
INNER JOIN Objects Departments ON L.ParentId = Departments.Id
INNER JOIN ObjType OT ON Departments.TypeId = OT.Id

WHERE OT.Code = ‘DEPARTMENT’

AND LT.Code = ‘EMPDEPART’
AND Department.ItemName = ‘Отдел поставок’

Опытные разработчики, видимо, уже заметили слабость предлагаемой схемы — связи никак не контролируются и ничто не гарантирует, что «сотрудником» отдела не окажется счет-фактура. Поскольку описание связей между объектами является «сердцем» разрабатываемой БД, необходимо ввести в нее механизм, контролирующий допустимость устанавливаемой связи. Таким механизмом служит таблица допустимых связей:

На вставку и изменение записей в Links создается несложный триггер, который проверяет типы объектов для создаваемой связи и их допустимость по AllowedLinks. Поскольку разрешенные типы связей хранятся в таблице, мы получаем гибкий механизм для настройки БД под требования конкретной задачи. Если требуется задать новый тип связи между объектами — достаточно лишь добавить в AllowedLinks запись с этим типом связи и типами объектов.

Замечу, что предлагаемая модель позволяет легко хранить иерархические данные. Для этого надо лишь задать связь типа «находится в».

Выбирая, какой тип объекта должен быть первым (ParentId), а какой вторым (ChildId) в связи, рекомендуется придерживаться единой системы. Для БД это безразлично, однако для уменьшения путаницы лучше в качестве первого выбирать объект, который бы оказался на стороне «один» отношения «один-ко-многим» при проектировании БД по классической методике. Если объекты связаны по принципу «многие-ко-многим», надо просто выработать для себя единую методику и в дальнейшем ей следовать.

Расширение БД

Итак, полученная БД может хранить описание произвольных сущностей, позволяет динамически определять и устанавливать между ними произвольные связи. Для многих задач (например, отдел кадров), в которых каждый объект уникален, этого уже достаточно. Однако существует много задач, под которые полученное «ядро» было бы неплохо пополнить дополнительными возможностями.

Налаживаем учет

Существует большой класс объектов, экземпляры которых с точки зрения учета не отличаются друг от друга. К таким объектам применимо понятие количества. Например, нам безразлично, какие конкретно рубли лежат в кассе. Важно лишь знать, СКОЛЬКО их там. Подобные объекты широко распространены в задачах учета материальных ценностей. Задачи учета требуют хранения в БД информации о перемещениях материальных ценностей и их остатках. Отметим, что остатки есть расчетная величина, которая может быть легко рассчитана из перемещений. Таким образом, хранение остатков может осуществляться в целях кэширования часто требующейся информации и его методика определяется скорее особенностями реализуемой системы. Зачастую от учета можно вообще отказаться и пересчитывать остатки «на лету», по мере поступления запросов от пользователей.

Для хранения информации о перемещениях заведем таблицу:

Рассмотрим некоторые поля этой таблицы подробнее.

OperationId

Каждая операция может состоять из нескольких перемещений. Например, расход товара со склада и приход его на фирму-покупателя. Чтобы иметь возможность логически связать эти перемещения, их группируют в операцию. В то же время операция часто имеет ряд дополнительных атрибутов (менеджер, срок выполнения, тип) и т.п. Поэтому — объявим операцию объектом и будем хранить информацию о ней наравне с другими объектами БД. Можно завести отдельные типы объектов для различных операций, диктуемых предметной областью. Наша схема позволяет легко определить такие виды операций, как:

• продажа товара — расход товара со склада, приход денег в кассу, расход денег с фирмы-покупателя, приход товара на фирму-покупатель;
• конвертация — расход одной валюты и приход другой (возможно на другой счет);
• производство — расход нескольких товаров со склада материалов и приход одного (или нескольких же) других товаров на склад готовой продукции;
• бухгалтерская проводка — дебет одного счета и кредит другого (либо нескольких других).
  

ContainerId

Ссылка на объект-хранилище, на который приходят либо с которого уходят материальные ценности. Таким объектом может быть склад, касса, фирма-контрагент и т.п. Хранилище само может выступать как перемещаемый объект — например гвозди лежат в ящике, а ящик лежит на складе.

Amount

Перемещаемое количество. Если Amount > 0 — это приход, в противном случае — расход.

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

SELECT Sum(Amount)

FROM Movement

WHERE ContentId = :<Id объекта «рубли»>

AND ContainerId = :<Id объекта «касса»>
AND ItemDate <= :InterestDate

Запрос:

SELECT O.Id, O.ItemName, SUM(M.Amount)

FROM Movement M

INNER JOIN Object O ON M.ContentId = O.Id
INNER JOIN ObjType OT ON O.TypeId = OT.Id

WHERE OT.Code = ‘GOOD’

AND M.Amount > 0
AND M.ContainerId = :<Id объекта «склад»>
AND M.ItemDate BETWEEN :StartDate AND :EndDate

GROUP BY O.Id, O.ItemName

вернет суммы прихода на склад по всем товарам (тип объекта «GOOD») за период от StartDate до EndDate.

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

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

Триггер на таблицу Movement не допускает создания записей с недопустимыми (то есть отсутствующими в AllowedMovement) типами объектов.

Цены

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

Сами цены будем хранить в таблице:

Таким образом, мы можем задать любое количество цен по любому объекту. Например, товар по оприходованию получает входную цену, затем ему назначается долларовая цена, а впоследствии, время от времени, начиная с каких-то дат — рублевая. Каждая из подсистем учета (бухгалтерия, торговый зал и т.п.) может выбирать и работать с интересующими их ценами. Для ведения курсов валют достаточно завести объекты, устанавливающие эти курсы и по ним вести цену типа «EXCHANGE» (курс обмена) по интересующим валютам.

Проблемы и пути их решения

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

Ослабленный контроль за целостностью данных

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

При необходимости возможно дополнение таблиц, хранящих описание атрибутов (StringDesc, PropDesc и т.п.) полями, несущими информацию об ограничении значений вводимого атрибута. Например, можно добавить в таблицу PropDesc поля MinValue и MaxValue и в триггере на таблицу Properties проверять соответствие вводимых значений заданному диапазону. Подобные расширения зависят от конкретной реализации всей системы и здесь для простоты не рассматривались, однако разработчик имеет возможность применить их про необходимости.

Пониженное быстродействие при выборке данных

Хранение атрибутов в различных таблицах требует аккуратности при программировании выборки множества объектов. Так, чтобы получить одновременно несколько атрибутов, придется написать запрос вида:

SELECT O.Id, S1.Value AS Family, S2.Value AS FirstName,

S3.Value AS LastName, H.ItemDate AS BirthDate

FROM Objects O

INNER JOIN ObjType OT ON OT.Id = O.TypeId
LEFT JOIN Strings S1 ON O.Id = S1.ObjectId
LEFT JOIN StrDesc SD1 ON S1.TypeId = SD1.Id
LEFT JOIN Strings S2 ON O.Id = S2.ObjectId
LEFT JOIN StrDesc SD2 ON S2.TypeId = SD2.Id
LEFT JOIN Strings S3 ON O.Id = S3.ObjectId
LEFT JOIN StrDesc SD3 ON S3.TypeId = SD3.Id
LEFT JOIN History H ON O.Id = H.ObjectId
LEFT JOIN Status S ON H.TypeId = S.Id

WHERE OT.Code = ‘PEOPLE’

AND SD1.Code = ‘FAMILY’
AND SD2.Code = ‘FIRSTNAME’
AND SD3.Code = ‘LASTNAME’
AND S.Code = ‘BIRTHDATE’

Кроме неудобства в написании (об этом мы поговорим позже), такой запрос может привести еще и к медленному выполнению на сервере. Выходом из ситуации может послужить автоматическое формирование в поле Objects.ItemName краткого описания объекта, необходимого для поиска его в списке себе подобных и использование преимущественно этого поля. Прочие атрибуты, выводимые в список объектов, должны по возможности исключаться из него. Как правило, это условие вполне выполнимо. Практика показывает, что вывод имени объекта и двух-трех атрибутов вполне достаточен для его выбора и практически не создает проблем для сервера.

Сложность в понимании структуры и выборке данных

Структура БД, не ориентированная на бизнес-логику, часто бывает затруднительной для понимания. Часто даже достаточно квалифицированным разработчикам требуется некоторое время, чтобы понять принципы хранения объектов и связи между ними. Еще хуже обстоит дело с конечными пользователями, которым бывает нужен нестандартный доступ к хранящейся в БД информации для ее анализа. Преодолением данной проблемы может послужить создание набора представлений (VIEW), предоставляющих более простой доступ к данным. Так, создав следующие представления:

CREATE VIEW vObjects AS

SELECT O.*, OT.Code

FROM Objects O

INNER JOIN ObjType OT ON O.TypeId = OT.Id

CREATE VIEW vStrings AS

SELECT S.*, SD.Code, OT.Code AS TypeCode

FROM Strings S

INNER JOIN StrDesc SD ON S.TypeId = SD.Id
INNER JOIN ObjType OT ON SD.TypeId = OT.Id

CREATE VIEW vHistory AS

SELECT H.*, S.Code, OT.Code AS TypeCode

FROM History H

INNER JOIN Status S ON H.TypeId = S.Id
INNER JOIN ObjType OT ON S.TypeId = OT.Id

мы сможем записать запрос из предыдущего раздела как:

SELECT O.Id, S1.Value AS Family, S2.Value AS FirstName,

S3.Value AS LastName, H.ItemDate AS BirthDate

FROM vObjects O

LEFT JOIN vStrings S1 ON O.Id = S1.ObjectId
LEFT JOIN vStrings S2 ON O.Id = S2.ObjectId
LEFT JOIN vStrings S3 ON O.Id = S3.ObjectId
LEFT JOIN vHistory H ON O.Id = H.ObjectId

WHERE O.Code = ‘PEOPLE’

AND S1.Code = ‘FAMILY’
AND S2.Code = ‘FIRSTNAME’
AND S3.Code = ‘LASTNAME’
AND S.Code = ‘BIRTHDATE’

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

Построение клиентской части

В роли клиентской части для такой БД, как правило, выступает сервер приложений, реализующий слой бизнес-логики. Для его создания реализуется иерархия классов

TBaseDBObject

+--TInvoice
+--TEmployee
...

В TBaseDBObject реализуется базовая функциональность по загрузке свойств из БД и сохранению их в БД, а также интерфейсные методы, позволяющие ему общаться со слоем представления данных, расположенным на клиентской машине. Наследники создаются для каждого типа объектов в БД и реализуют логику их поведения. Возможна реализация этих объектов как COM-серверов, скомпилированных в отдельные DLL и подключаемых к основному модулю сервера приложений при необходимости (либо, например, как объектов Microsoft Transaction Server).

При построении интерфейса пользователя удобно реализовать набор базовых классов, умеющих работать с информацией о произвольном объекте БД. Такие классы, оформленные в виде компонентов, позволяют легко компоновать интерфейсные решения для работы с конкретными типами объектов. На рисунке приведены два таких компонента: наследник TListBox, отображающий информацию о свойствах объекта, и наследник TTreeView, отображающий другие объекты, связанные с выбранным, и типы связей. Оба они реализованы независимыми от типа конкретного объекта, за счет единообразия хранения информации о свойствах и связях. Такая пара компонентов позволяет легко реализовать произвольное перемещение по связям между объектами в БД, что достаточно сложно реализуется в традиционной БД.

Дополнительную информацию Вы можете получить в компании Interface Ltd.

Обсудить на форуме
Отправить ссылку на страницу по e-mail