Работа с внешними ключами к нескольким таблицам

У вас есть три таблицы: регионы, страны и штаты. Страны и штаты могут принадлежать к регионам, образуя иерархическую структуру. Теперь вы хотите создать таблицу «popular_areas» со столбцами «region_id» и «popular_place_id» и установить связь внешнего ключа между «popular_place_id» и странами или штатами на основе столбца «popular_place_type».

Это Сценарий представляет собой проблему, поскольку ограничения внешнего ключа SQL обычно ссылаются на одну целевую таблицу. Однако при проектировании баз данных существует концепция, известная как полиморфные ассоциации, которая пытается решить эту проблему.

Полиморфные ассоциации: объединение нескольких таблиц с общим супертипом

Полиморфные ассоциации позволяют внешний ключ для ссылки на одну из нескольких возможных целевых таблиц. Это достигается за счет использования дополнительного столбца для указания целевой таблицы, на которую ссылаются. Например, в вашем случае вам понадобится столбец «popular_place_type», чтобы определить, ссылается ли «popular_place_id» на страну или штат.

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

Создание отдельных таблиц:

Создание родительской таблицы:
• Создайте таблицу «места», которая действует как супертип для штатов и стран. И штаты, и страны будут иметь внешние ключи, ссылающиеся на «места», обеспечивая ссылочную целостность.
Использование двух столбцов:
• Вместо одного столбца, ссылающегося на штаты или страны, используйте два столбца с нулевым значением: " state_id» и «country_id». Однако вы должны убедиться, что только один из этих столбцов не равен NULL, и обеспечить соблюдение этого ограничения с помощью логики приложения или триггеров базы данных.
• Реляционная целостность и нарушение нормальных форм
Важно отметить, что полиморфные ассоциации нарушают принципы нормализации базы данных. Первая нормальная форма (1NF) запрещает смешивание разных значений в одном столбце, что происходит при хранении идентификаторов штата и страны в одном столбце «popular_place_id». Полиморфные ассоциации также нарушают третью нормальную форму (3NF), поскольку значение столбца «popular_place_id» зависит от дополнительного столбца «popular_place_type».

Подводя итог, полиморфные ассоциации предлагают потенциальное решение для связей внешнего ключа с несколькими таблицами. в SQL. Однако они создают проблемы с целостностью данных и нарушают принципы нормализации базы данных. Рассмотрите альтернативные подходы, упомянутые выше, для надежного управления данными.