Self referencing many-to-many на MS SQL Server и Access. Особенности. Часть 1.

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

self referencing many-to-many

       Речь пойдет о проектировании структуры БД состоящей из двух таблиц с использованием связи self referencing many-to-many для одной таблицы, и вспомогательной таблицы взаимосвязей типа предок-потомок (если из вышенаписанного ничего не ясно – дочитайте до начала иллюстраций :)). Плюс при всем этом - будут затронуты нюансы обеспечения целостности данных (каскадное удаление\обновление) зависящие от конкретной используемой "платформы".

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

Постановка задачи

      Итак, сформулирую рабочую задачу на абстрактном примере:

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

а) отдельным минеральным видом

б) минеральной разновидностью для одного или (внимание!) нескольких минеральных видов.

     Каким образом можно спроектировать БД, для хранения информации о минералах с учетом ограничений определенных условиями, и обеспечить целостность данных?

Проектирование (Access)

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

     Так как и минеральные виды и разновидности – это сущности одного типа, то для хранения информации о них необходимо одно отношение STONES, в котором будут определены поля id, и name. Для хранения информации о взаимосвязях (предок-потомок) между минералами необходимо отношение MTF (сокр. – minerals to forms) с полями stone_id, form_id. Ограничения накладываемые условиями предметной области не позволяют нам использовать еще одно дополнительное поле в таблице STONES для характеристики взаимосвязей между минералами, (так как один минерал может быть предком для нескольких потомков и, в свою очередь, один потомок может иметь нескольких предков), поэтому необходимость наличия отдельного отношения взаимосвязей является абсолютно очевидной.


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

   Итак, у нас имеется два отношения STONES и MTF. Целостность отношения MTF обеспечивается составным PK из соответствующих полей stone_id и form_id . Каждая запись в таблице будет уникальной. Отношение STONES будет иметь PK на поле id.

   После завершения проектирования структуры БД можно перейти к схеме данных и приступить к проектированию связей между отношениями. Ссылочная целостность отношений MTF и STONES обеспечивается ограничением FK для каждого из двух полей отношения MTF на PK отношения STONES, однозначно идентифицируя взаимоотношения предок-потомок между ключевыми сущностями.


     После определения последнего из двух FK для отношения MTF мы увидим следующую ужасающую картину:

      Как говорят буржуи: оО WTF? Смотрим код.

CREATE TABLE stf (

    form_id                 Long NOT NULL,

    stone_id                Long NOT NULL,

    CONSTRAINT PrimaryKey PRIMARY KEY (stone_id, form_id)

);

CREATE TABLE stones (

    id                      Long NOT NULL,

    stone_name              Text(80) WITH COMP,

    CONSTRAINT PrimaryKey UNIQUE (id),

    CONSTRAINT PrimaryKey1 PRIMARY KEY (id)

);

  

ALTER TABLE stf

    ADD CONSTRAINT stonesstf FOREIGN KEY (form_id) REFERENCES stones
ON UPDATE CASCADE
ON DELETE CASCADE;

ALTER TABLE stf

    ADD CONSTRAINT stonesstf1 FOREIGN KEY (stone_id) REFERENCES stones
ON UPDATE CASCADE
ON DELETE CASCADE;

        Что же это за таблица STONES_1? Дубликат отношения STONES? Судя по коду – вроде бы нет. В общем, я так и не разобрался, что это за безобразие.

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

Ситуацию удалось немного прояснить следующим образом:

      Я просто разбил отношение STONES на два отношения – STONES и STONEFORMS со связью между ними one-to-one. Как видно по диаграмме, тут все обошлось без фокусов и, видимо, дополнительная таблица на предыдущей диаграмме – это специфика Access-а.

      

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

Итог по Access-у

    Итак, как можно видеть, Access позволяет проектировать подобную структуру БД с обеспечением каскадного обновления и удаления данных, чего не скажешь о SQL Server.

Продолжение во второй части...