Таблицы-справочники
Таблица-справочник является простейшим элементом информационной базы, в ней могут храниться значения, относящиеся как к одному, так и к разным уровням измерения. Например, справочник « Субъекты РФ » содержит только территории, относящиеся к субъектам РФ, а города и районы каждого субъекта находятся на более низких уровнях и входят в отдельные справочники.
Физически, таблица-справочник состоит, как правило, из трех полей:
- Поле 1 . Уникальный идентификатор каждого элемента данных (Код);
- Поле 2 . Собственно содержание элемента списка (Наименование);
- Поле 3 . Код владельца, то есть уникальный идентификатор элемента справочника уровня выше (Связь). При формировании иерархического измерения « Поле 3 » используется в выражении, которое определяет условие подчиненности элементов данного справочника элементам справочника более высокого уровня. При наличии « Поля 3 » в таблице-справочнике могут храниться значения атрибута, относящиеся к разным уровням измерения. Если « Поле 3 » отсутствует, то таблица-справочник будет содержать элементы только верхнего уровня;
- Поле 4 . Порядковый номер на уровне иерархии (Порядок). При создании измерения можно будет задать порядок элементов на уровне. Если он отличается от обыкновенной сортировки элементов списка по возрастанию, то в структуру таблицы можно добавить данное поле.
Нашли ошибку? Выделите текст с ошибкой и нажмите кнопку «Сообщить об ошибке» или CTRL+ENTER.
Справочная система на версию 9.2 Update 4 от 11/10/2019, © ООО «ФОРСАЙТ»,
Классификация типов справочников в базах данных
К справочникам я отношу всю ту информацию, которая необходима исключительно для красивого вывода пользователю. И как вариант от нее будет нужно брать пару циферок. Какой-нибудь (вес коробки). Внутри приложения все равно будут всевозможные ID и константы. При проектировании от них можно абстрагироваться и считать что они просто есть и будут показываться как надо.
Одно из решений, к которому все приходят рано или поздно это вообще не создавать никакой структуры под справочники, а хранить все в некой универсальной структуре. Которая с виду отлично ложится на классы в приложении.
EAV модель
В интернете про нее написано много чего. Но основная идея, подверженная на практике использовать ее только в том случае когда необходимы сущности задаваемые пользователем. Это настолько редкий класс проектов, что о них даже не стоит говорить.
В любом другом случае мы получаем неудобочитаемую, медленно работающую структуру. Без каких-либо плюсов.
Классические справочники
• Нерасширяемые справочники
Пример: Пол человека.
Плюсы выделения этого класса в том что мы сразу, на этапе проектирования, определяем сколько и каких значений в них будет лежать.
Благодаря этому мы можем соответсвенно проектировать интерфейс пользователя, писать CASE по всем возможным вариантам, итд. С ограниченным числом значений в справочнике работать всегда проще чем с неограниченным.
Например:
CASE
WHEN sex = ‘М’ then caption = ‘Он’
WHEN sex = ‘Ж’ then caption = ‘Она’
WHEN sex = ‘Н’ then caption = ‘НЛО’
END
Если сделать справочник расширяемым, такая конструкция не заработает. Надо делать дополнительные таблицы, писать дополнительные запросы.
Нерасширяемые справочники проще всего делать просто константами в соответствующих полях объектов. Не нужно никаких таблиц, ключей, ничего. Для контроля целостности накладываем на это поле ограничение с перечислением всех возможных вариантов.
• Расширяемые справочники
Пример: Страна проживания
Плюсы выдления этого класса в том что мы не будем требовать задавания уникального кода для каждого нового значения. Нам такие коды вообще будут не нужны.
Есть большой соблазн сделать таблицу со списком всех стран. И реализовать выбор из списка. В лучшем случае с поиском по первой букве. Но на практике оказывается что 95% людей будут из России. А остальные 5% из десяти других стран. И весь этот список из сотни наименований полностью никогда не будет использоваться.
Куда лучше сделать обычное текстовое поле для ввода, и показывать список отфильтрованный по первым буквам. Эстеты могут добывить кнопочку показывающую весь список сразу. Сам список генерить по всем ранее введенным значениям.
Расширяемые справочники лучше всего делать просто текстовыми аттрибутами. Контроль дубликатов будет осуществляться благодаря выпадающему списку подходящих вариантов. Ну и совсем хорошо добавить администратору возможность заводить синонимы РФ = Россия для автозамены.
Как и с нераширяемыми справочниками, не надо делать отдельных таблиц, ключей. Не надо проверять целостность, и думать о том как хранить изменения. Они выраждаются до обычных аттрибутов объетов.
• Справочники достойные отдельных таблиц
Пример: Товары
К этому типу относятся все справочники имеющие собственные аттрибуты или ссылающиеся на что-либо. Делаем для них отдельные таблицы.
Их надо проектировать, думать. Общего подхода тут нет.
Коды
Иногда возникает необходимость хранить много связей Код-Имя. Под такие связи лучше всего выделить отдельную таблицу. И во всех остальных таблицах хранить только коды. Этот подход полностью совместимо с этой системой хранения справчников. К примеру для нераширяемого справочника Пол можно довавить коды (“М”, “Ж”, “Н”) и соответвующие им имена (“мужской”, “женский”, “НЛО”)
UPD: Критика выслушана. Статья обновлена.
Урок 10. Справочники базы данных
Данный урок продолжает тему девятого урока, связанную с организацией взаимодействия с сервером базе данных (БД). В качестве БД также будем использовать «Apache Derby». Подключение к серверу и просмотр табличных объектов БД описано в предыдущем уроке.
Цель данного урока показать, как можно без программирования из готовых модулей создать приложение, позволяющее управлять следующими справочниками базы данных :
- list-measures – справочник единиц измерения
- list-currencies – справочник валют
- list-exchange – справочник курсов валют
Необходимо отметить, что все используемые в данном уроке модули могут взаимодействовать с различными серверами БД. Все зависит от того, какой модуль Вы используете в конфигурации : util-db или один из его прототипов.
Представленные в списке модули при первом старте создадут в БД справочные таблицы. Модули list-measures и list-currencies запишут в справочник инициализационные записи. Отличительная особенность справочника курсов валют list-exchange связана с возможностью подключения к WEB-сервису ЦБ РФ и получения значений различных курсов валют как за текущий месяц, так и за прошлые месяцы.
Конфигурация
Конфигурация приложения включает набор бандлов в поддиректории «configuration/bundles», которые регистрируются в файле «configuration/bundles.ini». В конфигурацию к базовому набору платформы дополнительно включены модули :
- db-connection – модуль подключения к серверу БД;
- db-objects – модуль представления структуры и записей табличных объектов БД;
- util-db-derby – модуль взаимодействия с сервером БД «Apache Derby»;
- form-locale – модуль определения текущего языка локализации.
- org.apache.derby-10.10.1000001 – JDBC-драйвер сервера БД «Apache Derby».
Листинг configuration/bundles.ini
bundles=org.apache.felix.eventadmin-1.4.8.jar@start, \ org.apache.felix.log-1.0.1.jar@start, \ util-resources-1.0.0.jar@start, \ util-logger-1.0.0.jar@start, \ gui-widgets-1.0.1.jar, \ base-jframe-1.0.0.jar, \ base-jpanel-1.0.0.jar, \ base-jdialog-1.0.0.jar, \ gui-menu-1.0.0.jar, \ gui-toolbar-1.0.0.jar, \ org.apache.derby-10.10.1000001.jar, \ util-db-derby-1.0.0.jar, \ db-connection-1.0.0.jar@start, \ db-objects-1.0.0.jar, \ form-locale-1.0.0.jar, \ list-currencies-1.0.0.jar, \ list-exchange-1.0.0.jar, \ list-measures-1.0.0.jar, \ templ-jframe-1.0.0.jar@main
Листинг configuration/menu.xml
Листинг файла описания структуры меню «configuration/menu.xml» имеет следующий вид :
С форматом структуры главного меню можно познакомиться на странице описания. В примере главное меню включает один пункт меню слева и два пункта меню справа, разделенные в описании тегом . Атрибут пункта меню name используется для определения локализованного заголовка.
К пунктам главного меню подключены один или несколько подпунктов ; тег наименованием separator используется для разделения подпунктов меню. Описание главного меню включает интуитивно-понятные атрибуты :
- name – наименование пункта/подпункта меню, используется в локализации текстовой надписи;
- image – отображаемое в подпункте меню изображение, расположенное в директории «configuration/resources/images»;
- groupId, artifactId, version – связанный с подпунктом меню бандл.
Логирование сообщений
Конфигурация приложения данного урока была использована в качестве примера для описания логирования сообщений модуля util-logger.
Связанные страницы
Скачать урок
В таблице представлены архивные файлы конфигурации и база данных Apache Derby. Главный модуль приложения можно скачать в Уроке 1. Конфигурация приложения включает JDBC-драйвер Apache Derby и утилиту взаимодействия с СУБД util-db-derby. После скачивания архивных файлов необходимо создать структуру платформы JaBricks. В файле инициализации приложения jabricks.ini необходимо указать полный путь к директории, как это описано в утилите db-connection.
| Архив | Описание | Скачать |
|---|---|---|
| configuration.lesson09.zip | Конфигурация десятого урока | скачать (3.11 Мб) |
| db.zip | База данных Apache Derby | скачать (1.69 Мб) |
Справочники
Contents
Система Ultima Businessware® предоставляет прикладному разработчику механизмы создания и редактирования справочников, описывающих бизнес-объекты предметной области.
Описание справочников хранится в схеме ядра базы данных в следующих таблицах:
• DICTIONARIES – атрибуты справочников;
• DICT_PROPERTIES – свойства справочников;
• PROP_TRANSLATIONS – локализованные значения полей справочника, переводимых на язык отличный от языка по умолчанию;
• DICT_TOONEREFS – взаимосвязи свойств справочников с другими справочниками;
• LINK_TABLES – атрибуты развязочных таблиц;
• LINK_PROPERTIES – свойства развязочных таблиц;
• LINK_TOONEREFS – взаимосвязи свойств развязочных таблиц со справочниками;
• DICT_LINKTABLES – взаимосвязи справочников с другими через развязочные таблицы.
Поля этих таблиц будут подробно описаны ниже при описании механизма создания новых объектов типа справочник.
С помощью таблиц OBJECTS и VERSIONS реализуется механизм версионирования конфигурации.
Каждая из таблиц, описывающих справочники, имеет по два ключевых поля, связанных с соответствующими таблицами:
• OBJECT_ID – идентификатор объекта;
• VERSION_ID – идентификатор версии конфигурации.
Физическая (ER) модель данных выглядит следующим образом:
Прикладной разработчик может осуществлять запросы к метаданным и через представления (view) – виртуальные таблицы, полученные выборкой из базы данных всех объектов относящихся к одной версии конфигурации, идентификатор которой получен из текущей сессии. Таким образом, обращаясь к таблицам базы данных и внося в них изменения, разработчик получает доступ только к выбранной при входе в систему версии конфигурации. Для всех таблиц представлений к имени таблицы добавляется префикс «V».
Модель проекции текущей версии данных выглядит в этом случае гораздо проще:
Таблица PROP_TRANSLATIONS модели данных не имеет префикса «V», так как не является представлениям по причине того, что не версионируется.
В таблице DICTIONARIES хранятся атрибуты , которые описывают справочник и его таблицу в прикладной схеме базы данных:
• NAME – название справочника, определяет название генерируемого класса ;
• CAPTION – отображаемое в экранных формах название справочника, например, для справочника контрагентов Agents это может быть название «Контрагенты» или «Справочник контрагентов»;
• TABLE_NAME – название таблицы в прикладной схеме базы данных (вследствие ограничений, накладываемых СУБД Oracle, название может содержать только буквы латинского алфавита в верхнем регистре, цифры и знак «_», при этом название должно начинаться с буквы и быть не больше 30 символов);
• CACHED – флаг кэширования, если он установлен – справочник кэшируется на компьютерах конечных пользователей. При повторном обращении к нему данные берутся из локальной копии, а не с сервера базы данных, что можно использовать для справочников с редко меняющимися данными, например, списком офисов.
Для кэшируемого справочника всегда рассылаются уведомления об изменении, поэтому не следует делать кэшируемыми часто изменяющиеся справочники .
• NOTIFICATION_ENABLED – флаг уведомления, используется для оповещения о внесенных в данные справочника изменениях. Для кэшируемого справочника уведомления рассылаются всегда независимо от состояния флага;
• PARENT_REF_OBJ_ID (FK) – атрибут используется для создания справочника древовидного типа. В качестве его значения указывается ссылка на запись таблицы DICT_TOONEREFS , которая указывает, какое из свойств выбранного справочника (полей его таблицы) будет родительским ( PARENT_ID в примере):
• DISPLAY_FORMAT – формат строки, в котором отображаются элементы справочника в экранных формах, когда они выводятся не в табличном, а строковом виде;
• SCRIPT_OBJ_ID (FK) – обработчик событий справочника (перед созданием, перед сохранением, после сохранения, перед удалением, после удаления), создается при необходимости;
• IS_SMALL – флаг размера справочника, устанавливается в true для справочников с небольшим количеством строк. Флаг влияет на работу некоторых элементов управления (controls), ориентированных на работу со справочниками. Так, например, содержимое маленьких справочников выводится в элементах управления DictionaryLookupEdit и DictionaryMultiSelectEdit целиком, содержимое прочих фильтруется;
• TRANSP_LOCALIZATION – флаг прозрачности локализации ( true – справочник локализуется прозрачно, false – непрозрачно).
Локализация справочников подробно описана в следующем разделе .
В таблице DICT_PROPERTIES описываются свойства справочника – по сути, поля его таблицы :
• NAME – название свойства, определяет название свойства генерируемого класса ;
• CAPTION – отображаемое в экранных формах название свойства справочника, например, для свойства Name справочника контрагентов это может быть «Имя контрагента» или «Название контрагента»;
• DICT_OBJ_ID (FK) – ссылка на справочник, которому принадлежит свойство, заполняется автоматически при добавлении его в справочник;
• COLUMN_NAME – название поля таблицы в прикладной схеме базы данных (на название поля СУБД Oracle накладывает те же ограничения, что и на таблицу);
• TYPE_ID (FK) – тип свойства (подробнее см. раздел Типы данных );
• CONTROL_ID (FK) – элемент управления, который будет использоваться по умолчанию в экранных формах для ввода значений этого свойства, выбирается из предлагаемых системой. Например, для строкового свойства это может быть строка, поле или строка для ввода пароля, скрывающая введенные символы;
• STRING_SIZE – ограничивает длину строки, если в качестве типа свойства TYPE_ID выбран string или text ;
• IS_MULTILANGUAGE – флаг, указывающий требуется ли перевод для этого свойства;
• IS_REQUIRED – флаг, указывающий обязательно ли свойство для заполнения;
• DEFAULT_VALUE – значение свойства по умолчанию, которое подставляется автоматически при создании нового элемента справочника.
В таблице PROP_TRANSLATIONS хранятся локализованные значения полей справочника :
• OBJECT_ID (FK) – ссылка на локализуемое свойство справочника;
• VALUE_ID – запись справочника в прикладной схеме базе данных, свойство которой локализуется;
• LANG_ID (FK) – язык локализации;
• STRING_VALUE – поле, предназначенное для хранения локализованного значения длинной не больше 4’000 символов;
• CLOB_VALUE – поле, предназначенное для хранения локализованного значения длинной свыше 4’000 символов.
Свойство справочника может быть ссылкой на другой справочник, взаимосвязи такого рода хранятся в таблице DICT_TOONEREFS :
• NAME – название взаимосвязи, определяет название свойства генерируемого класса типа класса , на который оно ссылается;
• CAPTION – отображаемое в экранных формах название взаимосвязи;
• DICT_OBJ_ID (FK) – справочник, свойство которого является ссылкой на другой справочник;
• PROP_OBJ_ID (FK) – свойство, которое является ссылкой на другой справочник. В качестве значения типа этого свойства ( TYPE_ID ) должен быть выбран long ;
• REF_DICT_OBJ_ID (FK) – справочник, на который ссылается указанное свойство.
Кроме того, через таблицу DICT_TOONEREFS задается свойство-родитель для справочников древовидного типа. В этом случае значения DICT_OBJ_ID (FK) и REF_DICT_OBJ_ID (FK) совпадают.
В результате заполнения всех обязательных атрибутов и свойств в прикладной схеме базы данных будет создана новая таблица TABLE_NAME с первичным ключом ID и полями COLUMN_NAME , которая будет использоваться для хранения данных нового справочника.
Кроме того, для каждой таблицы справочника создается представление, через которое производятся все операции чтения/записи. Представление необходимо для:
• версионирования схемы СУБД;
• поддержки часовых поясов;
• прозрачной локализации мультиязычных свойств справочников.
Рассмотрим на примере создание справочника товаров.
Описываем атрибуты и свойства нового справочника «Товары». В результате в схеме ядра сохраняются следующие метаданные:
На их основании в прикладной схеме базы данных ядром создается таблица GOODS.
В таблице LINK_TABLES хранятся атрибуты , которые описывают развязочную таблицу в прикладной схеме базы данных:
• NAME – название развязочной таблицы, определяет название генерируемого класса ;
• CAPTION – отображаемое в экранных формах название развязочной таблицы, значение этого атрибута подставляется по умолчанию в соответствующее поле при заполнении связи с ней справочника;
• TABLE_NAME – название таблицы в прикладной схеме базы данных.
В таблице LINK_PROPERTIES описываются свойства развязочной таблицы – по сути, поля ее таблицы в прикладной схеме базы данных.
• NAME – название свойства, определяет название свойства генерируемого класса ;
• CAPTION – отображаемое в экранных формах название свойства развязочной таблицы;
• LINK_OBJ_ID (FK) – ссылка на развязочную таблицу, которой принадлежит свойство, заполняется автоматически при добавлении его в развязочную таблицу;
• COLUMN_NAME – название поля таблицы в прикладной схеме базы данных;
• TYPE_ID (FK) – тип свойства (подробнее см. раздел Типы данных );
• CONTROL_ID (FK) – элемент управления, который будет использоваться по умолчанию в экранных формах для ввода значений этого свойства, выбирается из предлагаемых системой. Например, для строкового свойства это может быть строка, поле или строка для ввода пароля, скрывающая введенные символы;
• STRING_SIZE – ограничивает длину строки, если в качестве типа свойства TYPE_ID выбран string или text ;
• IS_MULTILANGUAGE – флаг, указывающий требуется ли перевод для этого свойства;
• IS_REQUIRED – флаг, указывающий обязательно ли свойство для заполнения;
• DEFAULT_VALUE – значение свойства по умолчанию, которое подставляется автоматически при создании нового элемента развязочной таблицы;
• IS_KEY – флаг, указывающий, что данное свойство является ключевым . Каждому набору ключевых свойств развязочной таблицы (помеченных флагом IS_KEY ) соответствует только один набор значений остальных ее свойств. У каждой развязочной таблицы должно быть как минимум два ключевых свойства.
Свойство развязочной таблицы может быть ссылкой на справочник, взаимосвязи такого рода хранятся в таблице LINK_TOONEREFS :
• NAME – название взаимосвязи, определяет название свойства генерируемого класса типа класса , на который оно ссылается;
• CAPTION – отображаемое в экранных формах название взаимосвязи;
• LINK_OBJ_ID (FK) – развязочная таблица , свойство которой является ссылкой на справочник;
• PROP_OBJ_ID (FK) – свойство развязочной таблицы, которое является ссылкой на справочник. В качестве значения типа этого свойства ( TYPE_ID ) должен быть выбран long ;
• REF_DICT_OBJ_ID (FK) – справочник, на который ссылается указанное свойство.
В результате заполнения всех обязательных атрибутов и свойств в прикладной схеме базы данных будет создана новая таблица TABLE_NAME с полями COLUMN_NAME , которая будет использоваться для хранения данных развязочной таблицы.
В таблице DICT_LINKTABLES хранятся взаимосвязи справочника с другими посредством развязочных таблиц :
• NAME – название развязочной таблицы, определяет название свойства-коллекции генерируемого класса ;
• CAPTION – отображаемое в экранных формах название развязочной таблицы, подставляется по умолчанию из соответствующего атрибута LINK_TABLES , может быть изменено;
• DICT_OBJ_ID (FK) – ссылка на редактируемый справочник, заполняется автоматически;
• LINK_OBJ_ID (FK) – развязочная таблица, с которой будет связан редактируемый справочник;
• PROP_OBJ_ID (FK) – поле развязочной таблицы, которому будет соответствовать идентификатор ( ID ) свойств редактируемого справочника.
Развязочные таблицы не имеют собственного интерфейса редактирования данных, как, например, элементы справочников. В экранной форме редактирования элементов справочника, который мы связываем с помощью развязочных таблиц с другим, автоматически создастся закладка с названием, заданным в атрибуте CAPTION , на которой будет возможно задавать отношения элементов текущего справочника с элементами, заданными в развязочной таблице LINK _OBJ_ID (FK), и редактировать связанные с ними переменные .
В таблице DICT_TOMANYREFS описываются взаимосвязи справочника с другими через отношения многие ко многим и соответствующая им таблица в прикладной схеме базы данных:
• NAME – название ссылки ко многим, определяет название свойства коллекции генерируемого класса ;
• CAPTION – отображаемое в экранных формах название ссылки ко многим;
• DICT_OBJ_ID (FK) – ссылка на создаваемый (редактируемый) справочник, заполняется автоматически;
• TABLE_NAME – название таблицы ссылок ко многим в прикладной схеме базы данных;
• COLUMN_NAME – свойство (поле) таблицы ссылок ко многим, по которому создаваемый (редактируемый) справочник будет связан с другим;
• REF_DICT_OBJ_ID (FK) – ссылка на другой справочник, с которым будет связан создаваемый (редактируемый);
• REF_COLUMN_NAME – свойство (поле) таблицы ссылок ко многим, с которым будет связан другой справочник.
В результате заполнения всех свойств в прикладной схеме базе данных будет создана новая таблица (ссылок ко многим) с именем TABLE_NAME и двумя полями COLUMN_NAME и REF_ COLUMN_NAME , ссылающимися на ключевые поля двух справочников DICT_OBJ_ID (FK) и REF_DICT_OBJ_ID (FK) .
Таблицы отношений многие ко многим так же, как и развязочные таблицы, не имеют собственного интерфейса редактирования данных. Связывая текущий справочник ссылками ко многим с другим, мы тем самым автоматически создаем в его экранной форме закладку с названием, заданным в атрибуте CAPTION , на которой будет возможно задавать отношения элементов текущего справочника с элементами справочника REF_DICT_OBJ_ID (FK) .