Справочники

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

    Идею статьи подсказало следующее сообщение на http://am.rusimport.ru/MSAccess/f2.aspx?type=1&id=42149

    Подскажите, как правильно разработать структуру базы для реализации почтовой базы. Интересует правильная связь таблиц: Страна, Регион, Город, Улица.

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

    Создаем четыре таблицы: Адресат, Справочник страны, Справочник регионы, Справочник  города. В каждой таблице обязательно должно быть соответствующее ей ключевое поле (Тип Счетчик) - idАдресат, idСтрана, idРегион, idГород. Назвать их разумеется можно и по другому.

    Обычно ключевое поле таблицы, которое является уникальным идентификатором записи в этой таблице, называют внутренним ключом (в нашем случае это поля типа «Счетчик»), а ключевые поля,  через которые внешние таблицы привязаны к данной - соответственно внешними ключами (в нашем случае это числовые поля Длинное целое)

    В руководствах для начинающих разработчиков часто присутствуют рассуждения, какое поле сделать ключевым. Дается определение ключевых полей и приводятся примеры связей между ними. На мой взгляд, такие рассуждения только сбивают с толку начинающих. Ведь в принципе, все довольно просто: в 99% случаев лучшее ключевое поле - счетчик. Оно 100% уникально (без повторений) и при установке связи с основной таблицей (в которую подставляются данные из этого справочника) с соответствующим полем Числовое (Длинное целое) связь автоматически определяется как один ко многим (одна запись в справочнике и много аналогичных записей в основной таблице).

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

    Как видно, в справочниках только два поля: ключевое и обозначение. А основная таблица состоит в основном из числовых полей, кроме поля «Улица». Вот здесь то и проявляется основная особенность построения реляционных баз данных:

    вместо того, чтобы хранить в таблице «Адресат» наименования стран, регионов, городов в виде текста, сохраним их лучше в виде чисел (значений ключевых полей справочников).

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

    Справочники «Улицы» и «Дома» создавать, как мне думается, нет смысла. Улиц будет слишком много, их проще ввести вручную.
Так же в справочных таблицах не помешает сделать поле «Обозначение» уникальным, не допускающим повторений. Для этого в конструкторе таблицы в свойстве поля «Индексированное поле» выберем «Да. Повторения не допускаются». Если теперь нажать в конструкторе таблиц на кнопку «Индексы» (на ней значок молнии), то увидим, что в таблицу кроме основного индекса ключевого поля добавился еще один - «Обозначение».

    Имена полей таблиц не должны содержать пробелов, иначе могут быть проблемы с VBA, и однозначно возникнут проблемы, при переносе базы на SQL Server. Если имя поля состоит из двух слов, то можно выбрать например такай вариант: НазваниеУлицы или Название_улицы.

    Хотя согласно Help, имя может включать любую комбинацию букв, цифр, и специальных знаков за исключением точки (.), восклицательного знака (!), надстрочного знака (`) и квадратных скобок ([ ]), но  желательно не использовать в именах полей таблицы никаких символов, кроме букв и цифр. Дело в том, что например имя поля таблицы типа «Улица№» в проекте mdb скорей всего не вызовет никаких конфликтов, а вот при переносе базы на SQL Server, мастер переноса просто «выкинет» его из таблицы.

    При создании однотипных таблиц дело пойдет быстрее, если в окне проекта «Таблицы» выделить таблицу, затем Ctrl + C или в контекстном меню при правом щелчке мыши выбрать «копировать», затем выбираем вставить, в появившемся окне задаем имя новой таблицы и жмем «ОК». Осталось только в ключевом поле «id…» изменить обозначение (было к примеру «idГород», а теперь сделаем «idРегион»). Остальное менять не нужно. Вот потому то я во всех справочниках ввел однотипное поле «Обозначение». В таблице «Адресат» так же создаем внешние ключевые поля (Длинное Целое) - idСтрана, idРегион, idГород. Не забудьте убрать в них значение по умолчанию = 0.

    Теперь осталось только установить связи между таблицами. Жмем в окне проекта на кнопку «Схема данных» или правой кнопкой мыши, и в контекстном меню выбираем «Схема данных». Появилась диалоговое окно «Добавление таблицы». Щелкаем дважды по всем названиям таблиц и закрываем окно.

    Располагаем таблицы например так:  слева основная - «Адресат», справа остальные. Наводим курсор в таблице «Адресат» на поле «idСтрана» нажимаем и тащим на поле «idСтрана» в таблице «Справочник страны». В появившемся диалоговом окне «Изменение связей» устанавливаем флажки: «Обеспечение целостности данных», «Каскадное обновление связанных полей» и жмем «ОК».

Флажок «Обеспечение целостности данных»

    Его установили для того, чтобы исключить возможность ввода в таблицу «Адресат» в поле «idСтрана» значения, которого нет в аналогичном поле справочной таблицы «Справочник страны». Советую всегда так делать. Этим Вы во многом избавитесь от проблемы «мусора» в базе данных - наличие ни с чем не связанных записей. Правда есть и другие способы «замусоривания», но не лишним будет уже в структурной схеме базы постараться свести их к минимуму.

Флажок «Каскадное обновление связанных полей»

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

    А вот флажок «Каскадное удаление связанных записей» в данном примере я бы не рекомендовал ставить. Иначе может возникнуть весьма печальная ситуация: пользователь решит удалить название города из соответствующего справочника (мол, больше не нужен), а вместе с ним удалятся и все связанные с ним записи в таблице «Адресат» - это и есть каскадное удаление. Правда, при всякой попытке удаления Access выдает соответствующее предупреждение, но надеяться на то, что пользователь адекватно среагирует на него, я бы не стал. Каскадное удаление имеет смысл ставить в случае, например при связи таблиц «Заказы» и «Заказы данные». Удаляете заказ, а вместе с ним автоматически и все данные по нему, так как зачем данные по заказу, которого больше нет.

    Остановлюсь еще на одном моменте: многим наверное приходилось сталкиваться с ошибкой «Слишком большое число». Если не хотите иметь с этим проблем, возьмите себе за правило не делать поля со списками в таблице (начинающие обычно для этого используют мастер подстановок). Дело в том, что из за несоответствия форматов единиц измерения ширины столбцов в разных версиях Access, эта самая ширина может вместо стандартной 2,54см  стать … 57,2 см (слишком большим). Поэтому список лучше сделать на форме, а в таблице оставьте просто поле.
В следующем выпуске я расскажу о вариантах реализации справочной системы без использования справочных таблиц