Здравствуйте!

Поделюсь своим опытом по этому вопросу.

В своё время *в 200-ом году) наши заказчики возжелали иметь у себя так называемую систему ведения справочников, т.е. отдельное приложение, которое в отдельной базе ведёт все справочники, которые используются в нескольких десятках учреждений.

Мы пошли по пути, который автор обозначил как SQL DDL.

Вот что из этого получилось (в общих чертах):

Структура справочников:
1. Термин справочник определён как набор объектов (таблиц, триггеров и т.д.), описывающих нечто из предметной области.
2. Внутри справочника выделили несколько типов таблиц:
- линейная таблица справочника - основная таблица справочника
- таблицы дополнительных свойств
- таблицы оглавлений, задающие иерархию для линейной таблицы
- ещё пара типов таблиц...
3. Для линейных таблиц справочников утвердили набор стандартных полей (код, наименование, период действия записи, дата создания записи). Всего таких служебных полей получилось 9 штук. Эти поля есть в каждой линейной таблице, их нельзя удалить и их порядок фиксирован.
4. Кроме служебных полей есть дополнительные поля (начиная с 10-го), которые можно добавлять/удалять по мере необходимости.
5. Среди дополнительных полей могут встречаться поля-ссылки, т.е. поля ссылающиеся на другие справочники.

Порядок работы самой программы:
1. Для хранения описательной информации сформирован набор из примерно 10 служебных таблиц (репозитарий), в которых хранятся данные о полных наименованиях объектов, списки полей (с дополнительными свойствами) и ещё много чего, что понадобилось для нормальной работы.
2. Рядовым действием системы является создания нового объекта (справочника, оглавления, поля, ...), а это значит, что выполняются соотвествующие DDL-команды (CREATE TABLE, ALTER TABLE, CREATE TRIGGER, ...). Соотвественно для таблиц есть интерфейс добавления/удаления полей/индексов/связей.
3. Для просмотра справочников испольется многооконный интерфейс и обычные гриды. Для редактирования записей приходится для каждой таблицы строить свою форму в виде a'la property editor в Delphi/C#/...

вот такая основная идея - будут вопросы, отвечу

с уважением,
Дмитрий Жучков

vinniPooh2 Dimkas
Не мог бы ты распространиться о скорости написания, работы и усилиях, необходимых для поддержки всего этого?

скорость написания - первая версия была написана за полгода в одиночку, но с первого раза ни работоспособность, ни функциональность заказчика не устроили и пришлось ещё месяца 3 дописывать/переписывать. А для достижения текущего результата понадобилось примерно 5 человеко лет ;)
т.к. сейчас и СУБД другая, и функций сильно больше...
vinniPooh
Что было особенно неприятно делать, какие подводные камни, что при таком подходе радовало?

Особо неприятно то что структура таблиц меняется на ходу,
когда раньше жили на MS SQL Server'е, то DDL-операции можно было включить в транзакцию и в случае чего откатить, сейчас живём на Oracle и приходится думать как сделать аккуратнее ибо откатить DLL уже нельзя...
Подводных камней особых не вспомню, хотя конечно были трудности...
Что порадовало - тоже особенно ничего не припомню :) работа как работа...
vinniPooh
Насколько я понимаю, при создании нового справочника модифицируются (добавление внешних ключей) также и те таблицы, которые ссылаются на этот справочник - для поддержания целостности?

При создании нового справочника на него ещё никто не ссылается, ведь его только что не существовало :). Вопрос надо уточнить...
vinniPooh
На какой базе это работало?

Сначала работало на MS SQL 97, потом MS SQL 2000, потом переехали на Oracle 8.1.7 и FireBird 1.5, сейчас качуем в сторону Oracle 10.2 и FireBird 2.0
vinniPooh
Так как у меня конкретная СУБД не определена, то боюсь, что могут возникнуть проблемы с мигрированием.

не понял, какие проблемы? мигрированием чего и куда?

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

Да, внешние ключи прописываются при добавлении в справочник поля, ссылающегося на другой справочник.
Периодически администратор системы выполняет так называемую проверку целостности - проверяются все первичные и внешние ключи, наличие и правильность триггеров и т.д.
vinniPooh Посколько синтаксис SQL DDL может, к сожалению, быть различным для разных СУБД, а у меня целевая СУБД не определена или может меняться (пути кастомера неисповедимы), теоретически могут возникнуть проблемы с переходом с одной базы на другую.
теперь понял :) у нас система работает на двух разных базах (Oracle и FireBird).
естественно, что для них запросы строятся разные. для этого введён промежуточный программный слой, которому приложение сообщает что надо сделать, а он уже делает... вобщем то всё как обычно...

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

PVPВсе справочники в одной таблице. В общем, все они содержат относительно не много записей, поэтому эта таблица не растет на столько, что бы заметно было при выборках данных.
доводилось сталкиваться с таким подходом и есть замечания:
1. Как быть с внешними ключами? Если в большой таблице есть записи из нескольких справочников, то как на уровне СУБД ограничить ввод в таблицу данных значений из конкретного справочника? Можно конечно ставить диапазон, но будут проблемы с добавлением значений. Можно во внешний ключ ввести ещё дополнительное поле, но тоже как-то имхо некрасиво.
2. Засорение базы сотней мелких таблиц - это конечно плохо, но есть и плюс - все таблицы имеют свои собственные имена и их можно предсказать при переносе между системами. А в Вашем подходе какое имя будет у справочника если Вы его передадите в стороннюю систему?

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

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

в чём трудность хранения в базе 100-150 дополнительных таблиц, если не считать затрат на придумывание имён для этих таблиц? с именами конечно есть проблемы, но они достаточно легко разруливаются системными соглашениями.
PVP
Другое преимущество - элементарное добавление новых справочников в живой работающей системе, без остановки, без возни с исходными текстами и последующей перегенерации системы.

если в ТЗ на систему сразу стоит ограничение типа "должна быть возможность добавления новых справочников и создания документов, имеющих ссылки на эти справочники", то все перечисленные трудности не зависят от выбранного подхода. Выполнение одного CREATE TABLE никак не проблемнее чем INSERT INTO bigtable... и будет "возня с исходными текстами" или нет тоже лежит целиком на совести разработчика.
PVP
Третье преимущество - программисты сопровождения должны выучить одну таблицу и правила работы с ней - это происходит за полчаса.

... не только выучить одну таблицу, но и запомнить какие значения имеют одни и те же поля для разных справочников. А вот это уже никак не полчаса для "более сотни справочников". При наличии большого количества мелких таблиц с _одинковой_ общей частью на их освоение тоже уйдёт полчаса. А вот нестандартные дополнительные поля придётся учить и там и там.
PVP
Следующее - для ввода в данных в справочники и для выбора из них значений используется одна и та же функция - упрощение самой системы.

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

Вобщем итоговое ИМХО - трудности, связанные с неоднозначностью интерпретации значения из большого мега-справочника, а именно ответ на вопрос "Какому под-справочнику принадлежит значение с кодом ХХХХ?", на мой взгляд перевешивают призрачные приимущества, которые указаны в качестве плюсов данного подхода.

vinniPooh Справочники, по идее, не сильно затормозят работу системы, ведь обращения к ним происходят достаточно редко, а отчеты по ним вообще не строятся.
Здрасьте! 8-о Как это обращения к справочникам происходят достаточно редко?!
А заполнение полей документов? А показ этих документов?
Рекомендую считать, что количество обращений к справочникам равно
количеству обращений к документам умноженному на количество справочников в документе...

[quot vinniPoohНо все равно, скорость здесь не настолько падает, чтобы это было критичным и могло послужить в пользу подхода с использованием SQL DDL.[/quot]
попробую дать информацию к размышлению
пример из здравоохранения:
Демографический справочник ~40 000 записей
Справочник городов и улиц ~15 000 записей
Справочник диагнозов ~ 13 000 записей
Справочник медицинских услуг ~ 5800 записей
Классификатор медицинского оборудования ~ 3800 записей
…
Справочник должностей – 379 записей
Справочник специальностей – 175 записей
Справочник видов лицензируемых услуг – 59 записей
Справочник зон и территорий – 14 записей

Внимание, вопрос – по какому из этих справочников наиболее часто строятся аналитические отчёты, подаваемые на стол (монитор) руководителю? минута пошла…

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

Конечно некоторые системы не подразумевают аналитической обработки собираемых данных, но уверяю, что все руководители ей подразумевают :)

guest_20040621> Демографический справочник ~40 000 записей
> Классификатор медицинского оборудования ~ 3800 записей
Вы уверены, что это _справочники_?
насчёт демографического - нет :)
а вот второй - совершенно точно...

встречный вопрос - список уникальных клиентов компании это справочник или нет? что-то мне подсказывает что таки справочник. а значит в любой системе массового обслуживания такой справочник может легко перевалить через указанные 40 тысяч. чего стоит,например, 12 млн. клиентов МТС - и ведь их обрабатывают...

вывод - справочники бывают разные и надо смотреть на конкретные нужды.
я только хотел предостеречь от сваливания разнородной информации в одну таблицу - плюсы сомнительны, а основной минус - противоречие базовой идее реляционных СУБД.