Структуры данных, применяющиеся для хранения документов
Автор:
В статье рассмотрены известные автору структуры данных, применяющиеся в
реальных приложениях СУБД, оперирующих документами.
Названия объектов будут даваться условные, на русском языке. Показаны принципы,
наборы типовых решений. Если вы хотите предложить что-то
свое, конструктивно покритиковать - пишите.
Сначала рассмотрим крайности.
Вариант 1. "Нормализованный"
Суть данного подхода заключается в том, что на каждый тип документа,
которым оперирует приложение, в БД создается своя таблица (если
документ не имеет позиционной части) или 2 таблицы - для
заголовка и позиционной части (частей) документа. Например,
"ЗАГОЛОВКИ_НАКЛАДНЫХ", "ПОЗИЦИИ_НАКЛАДНЫХ","ПЛАТЕЖНЫЕ_ПОРУЧЕНИЯ";.
Преимущества такого подхода:
компактность БД;
ссылки на справочники возможно организовать в виде физических ключей, т.к.
их количество и тип известны для единственного в таблице типа документа;
тонкая настройка привилегий пользователей (на уровень типов документов)
возможна стандартными средствами СУБД.
Недостатки:
количество таблиц в БД прямо пропорционально количеству типов документов;
соответствие типов документов и таблиц хранится в отдельной вспомогательной
структуре (например, голове разработчика :)), которая не
связана с таблицами документов;
сложность сборки отчетов по десяткам таблиц, если они строятся по разным типам
документов;
отсуствие возможности для пользователя создания своих типов документов или
расширение уже существующих без обращения к разработчику;
часто возникает задача получить объединенный список документов разных
типов, который решается обычно написанием хитрых представлений или
хранимых процедур, которые содержат полный набор реквизитов
заголовков всех типов документов. Предположим однако, что система
оперирует 100 типами документов, которые хранятся в сотне-другой
таблиц. Представьте исходный текст для общего представления (причем
похожие поля, которые надо группировать называются по разному и/или
имеют разный тип/длину).
Вариант 2. "Универсальный"
Подход, обратный к рассмотренному выше: все документы хранятся в двух
таблицах "ДОКУМЕНТЫ" и "ПОЗИЦИИ_ДОКУМЕНТОВ".
Таблицы содержат количество полей, необходимое для хранения самого
громоздкого документа (реально с запасом), которое обычно
измеряется сотнями.
Ссылки на справочники при этом:
или не являются физическими внешними ключами;
или создается единая таблица-справочник со сложной внутренней
структурой, на которую возможно создать физические ключи (скорее
теоретический вариант);
принимаются некие соглашения и вводится опять же с запасом некоторое количество
ссылок на справочник 1, справочник 2 в виде физических ключей, часть
полей может использоваться как фиктивные ссылки на остальные справочники.
Преимущества
легкость получения любых отчетов, различных выборок и журналов документов;
универсальность;
быстродействие;
тип документа определяется по физическому внешнему ключу на справочник "ТИПЫ_ДОКУМЕНТОВ";
Недостатки
громоздкость (даже если в типе документа используется 5 реквизитов, все равно
запись содержит 150 полей и имеет соответствующий размер). Впрочем,
в нынешнее время дешевых гигабайтов так ли это существенно?;
если ссылки на справочники все-таки в виде ключей - обилие
не самых эффективных индексов;
невозможность разграничения доступа к разным типам документов штатными средствами
СУБД (таблица одна) - надо изобретать свои системы безопасности или
создавать представления, осуществляя через них доступ к одной
таблице;
при отсутсвии физических ключей необходимо поддерживать на уровне
триггеров или хранимых процедур (а то и на уровне клиенсткого приложения, что еще хуже) целостность БД (это нехорошо потому, что
триггеры могут быть нерабочими, или допущено редактирование записи,
минуя хранимую процедуру);
Вариант 1а. Промежуточный между 1 и 2
Фактически вариант №1, но типы документов объединяется как бы в группы (например,
"НАКЛАДНЫЕ" со "СЧЕТАМИ_ФАКТУРАМИ" образуют таблицу "НАКЛАДНЫЕ_СЧЕТА_ФАКТУРЫ"). При этом "лишние"
поля создаются, но их количество минимально (собственно оно зависит от
того, как объединять документы). Данный вариант при сохранении
преимуществ варианта 1 предлагает большую универсальность.
Вариант 3. "Экстремально-универсальный"
Создаются таблицы "ДОКУМЕНТЫ" и "РЕКВИЗИТЫ_ДОКУМЕНТОВ" (не в смысле фактурные части!).
В первой таблице хранятся ссылка на тип документа (внешний ключ) и реквизиты, которые является
обязательным для всех или большинства типов документов (базовый набор
реквизитов), которыми оперирует приложения (например: дата, номер, ссылка на справочники "КОНТРАГЕНТЫ",
"ВАЛЮТЫ" и т.п.). При этом ссылки на "базовые" справочники являются внешними
ключами.
Прочие реквизиты, включая реквизиты фактурных частей хранятся в таблице
"РЕКВИЗИТЫ_ДОКУМЕНТОВ". Причем данные в эту таблицу
записываются не в столбцы а в виде строк (таблица как бы становится
перевернутой с неограниченным количеством столбцов). Т.е. если в
документе 3 специфических реквизита, в таблице "РЕКВИЗИТЫ_ДОКУМЕНТОВ"
будут созданы 3 записи, если 10 - 10 записей и т.д.
Соответственно помимо справочника "ТИПЫ_ДОКУМЕНТОВ"
(вариант 2) создается справочник "РЕКВИЗИТЫ_ТИПОВ_ДОКУМЕНТОВ".
Принадлежность реквизита позиционной части документа определяется
организацией иерархии в таблицах "РЕКВИЗИТЫ_ТИПОВ_ДОКУМЕНТОВ"
и "РЕКВИЗИТЫ_ДОКУМЕНТОВ" (ссылка на фиктивный
реквизит - заголовок спецификации). Ссылки на справочники - фиктивные.
Преимущества (по отношению к варианту 2):
компактность - лишние данные не хранятся, более того, пока реквизит не
заполнен, его значение можно не сохранять (не добавлять строку в
таблицу);
нет никаких ограничений на структуру типа документов на уровне структуры
данных;
возможно разграничение доступа и организация протоколирования не на уровне
типа, а на уровне реквизита документа (т.к. введен новый справочник
"РЕКВИЗИТЫ_ТИПОВ_ДОКУМЕНТОВ");
Недостатки (опять же по сравнению с вариантом 2):
сложность разработки БД;
в таблице "РЕКВИЗИТЫ_ДОКУМЕНТОВ" надо либо преобразовывать
все данные в строки, либо держать несколько полей - для разных типов данных. Опять же их надо где-то описывать;
необходимость разработки базового набора хранимых-процедур - примитивов для
обработки реквизитов документов, особенно позиционных частей;
скорость выборки и обработки документа напрямую зависит от сложности его
структуры;
Резюме
Какой же вариант применим в реальной жизни? Если вы пишите небольшую
учетную систему, которая обрабатывает десяток типов документов, формы
которых редко изменяются и/или не настраиваются заказчиком, вам
больше подойдут варианты 1 или 1а, т.к. система компактна и довольно проста.
При этом большую часть бизнес-логики вполне можно вынести на уровень
триггеров, использовать стандартную систему безопасности и
максимально облегчить клиентское приложение.
Если же в системе имеет место обилие документов различной структуры или требуется
обеспечить возможность настройки их заказчиком (вплоть до создания
своих типов документов), стоит подумать над универсальной
структурой данных вариантов 2 или даже 3 (структура документов
произвольна или большая часть документов имеет простую структуру, но
присутствет как минимум 2-3, в которых по 500 реквизитов).
Подзапросом называют запрос SELECT, который включается в другой запрос в качестве параметра или выражения. Они обычно используются, чтобы генерировать значение или набор результатов, которые используются в условиях главного запроса... подробнее
В различных сетевых форумах часто обсуждается вопрос: как получить отсортированные результаты выполнения запроса, передавая хранимой процедуре некоторый параметр? Я собрал несколько решений этой задачи, предложенных талантливыми программистами. Основная часть идей, изложенных в этой статье... подробнее
Данная статья написана на основе статей: The Interbase On - Disk Structure by Ann Harrison ; Structure of a Data Page by Paul Beach, а так же включает в себя мой горький опыт в удалении важных данных, а потом в их успешном восстановлении. Я никоим образом не собираюсь нарушать права ни выше перечисленных авторов, ни компаний, чьи зарегистрированные торговые знаки перечисленные в данной статье... подробнее
СУБД — Система Управления Базами Данных (dbms — database management system).
Программа, либо комплекс программ, предназначенных для полнофункциональной работы с данными. Как правило, включает в себя инструменты для создания и изменения структуры хранения наборов данных... подробнее
Временные таблицы всегда прекрасно помогали разработчикам. Раньше, когда я использовал access, я обычно создавал временные таблицы, которые удалял после решения задачи. При использовании sql server решить задачу можно гораздо проще. Не так ли?... подробнее
