Множественные, но взаимоисключающие внешние ключи - это правильный путь?

Если мы посмотрим на модель здесь, мы увидим следующее:

1. A user is related to exactly one website
   • A company is related to exactly one website
   • Веб-сайт связан только с одним пользователем или компанией

Третье отношение подразумевает существование сущности «пользователь или компания», чье PRIMARY KEY должно где-то храниться.

Чтобы сохранить его, вам нужно создать таблицу, в которой будет храниться PRIMARY KEY из website owner объектов. В этой таблице также могут храниться атрибуты, общие для пользователя и веб-сайта.

Поскольку это отношение один к одному, атрибуты веб-сайта также могут храниться в этой таблице.

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

Чтобы установить правильные отношения, вам нужно сделать PRIMARY KEY из website составным с owner type как его часть, и принудительно указать правильный тип в дочерних таблицах с ограничением CHECK:

CREATE TABLE website_owner (
    type INT NOT NULL,
    id INT NOT NULL,
    website_attributes,
    common_attributes,
    CHECK (type IN (1, 2)) -- 1 for user, 2 for company
    PRIMARY KEY (type, id)
)

CREATE TABLE user (
    type INT NOT NULL,
    id INT NOT NULL PRIMARY KEY,
    user_attributes,
    CHECK (type = 1),
    FOREIGN KEY (type, id) REFERENCES website_owner
)

CREATE TABLE company (
    type INT NOT NULL,
    id INT NOT NULL PRIMARY KEY,
    company_attributes,
    CHECK (type = 2),
    FOREIGN KEY (type, id) REFERENCES website_owner
)

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

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

Проблема, с которой я столкнулся с принятым ответом (от Quassnoi), заключается в том, что отношения объектов неверны: компания не является подтипом владельца веб-сайта; у нас были компании до того, как у нас появились веб-сайты, и у нас могут быть компании, которые являются владельцами веб-сайтов. Кроме того, мне кажется, что владение веб-сайтом — это отношение между веб-сайтом и человеком или компанией, т. е. у нас должна быть таблица отношений (или две) в схеме. Может быть приемлемым подходом отделить личное владение веб-сайтом от владения корпоративным веб-сайтом и объединять их только при необходимости, например. через VIEWs:

CREATE TABLE People
(
 person_id CHAR(9) NOT NULL UNIQUE,  -- external identifier
 person_name VARCHAR(100) NOT NULL
);

CREATE TABLE Companies
(
 company_id CHAR(6) NOT NULL UNIQUE,  -- external identifier
 company_name VARCHAR(255) NOT NULL
);

CREATE TABLE Websites
(
 url CHAR(255) NOT NULL UNIQUE
);

CREATE TABLE PersonalWebsiteOwnership
(
 person_id CHAR(9) NOT NULL UNIQUE
    REFERENCES People ( person_id ),
 url CHAR(255) NOT NULL UNIQUE
    REFERENCES Websites ( url )
);

CREATE TABLE CorporateWebsiteOwnership
(
 company_id CHAR(6) NOT NULL UNIQUE
    REFERENCES Companies( company_id ),
 url CHAR(255) NOT NULL UNIQUE
    REFERENCES Websites ( url )
);

CREATE VIEW WebsiteOwnership AS
SELECT url, company_name AS website_owner_name
  FROM CorporateWebsiteOwnership
       NATURAL JOIN Companies
UNION
SELECT url, person_name AS website_owner_name
  FROM PersonalWebsiteOwnership
       NATURAL JOIN People;

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

Если мы можем предположить, что СУБД применяет проверочные ограничения (как это делает принятый ответ), то мы можем использовать тот факт, что (человек) человек и компания являются юридическими лицами, и использовать таблицу супертипа (LegalPersons), но все же сохранить таблицу отношений подход (WebsiteOwnership), на этот раз с использованием VIEWs для отделения личной собственности на веб-сайт от отдельной от корпоративной собственности на веб-сайт, но на этот раз со строго типизированными атрибутами:

CREATE TABLE LegalPersons
(
 legal_person_id INT NOT NULL UNIQUE,  -- internal artificial identifier
 legal_person_type CHAR(7) NOT NULL
    CHECK ( legal_person_type IN ( 'Company', 'Person' ) ),
 UNIQUE ( legal_person_type, legal_person_id )
);

CREATE TABLE People
(
 legal_person_id INT NOT NULL
 legal_person_type CHAR(7) NOT NULL
    CHECK ( legal_person_type = 'Person' ),
 UNIQUE ( legal_person_type, legal_person_id ),
 FOREIGN KEY ( legal_person_type, legal_person_id )
     REFERENCES LegalPersons ( legal_person_type, legal_person_id ),
 person_id CHAR(9) NOT NULL UNIQUE,  -- external identifier
 person_name VARCHAR(100) NOT NULL
);

CREATE TABLE Companies
(
 legal_person_id INT NOT NULL
 legal_person_type CHAR(7) NOT NULL
    CHECK ( legal_person_type = 'Company' ),
 UNIQUE ( legal_person_type, legal_person_id ),
 FOREIGN KEY ( legal_person_type, legal_person_id )
     REFERENCES LegalPersons ( legal_person_type, legal_person_id ),
 company_id CHAR(6) NOT NULL UNIQUE,  -- external identifier
 company_name VARCHAR(255) NOT NULL
);

CREATE TABLE WebsiteOwnership
(
 legal_person_id INT NOT NULL
 legal_person_type CHAR(7) NOT NULL
 UNIQUE ( legal_person_type, legal_person_id ),
 FOREIGN KEY ( legal_person_type, legal_person_id )
     REFERENCES LegalPersons ( legal_person_type, legal_person_id ),
 url CHAR(255) NOT NULL UNIQUE
    REFERENCES Websites ( url )
);

CREATE VIEW CorporateWebsiteOwnership AS 
SELECT url, company_name
  FROM WebsiteOwnership
       NATURAL JOIN Companies;

CREATE VIEW PersonalWebsiteOwnership AS
SELECT url, person_name
  FROM WebsiteOwnership
       NATURAL JOIN Persons;

Что нам нужно, так это новые функции СУБД для «распределенных внешних ключей» («Для каждой строки в этой таблице должна быть ровно одна строка в одной из этих таблиц») и «множественного назначения», чтобы разрешить добавление данных в таблицы, ограниченные таким образом. в одном операторе SQL. К сожалению, мы далеки от таких возможностей!

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

Насколько я понимаю, вы хотите знать, принадлежит ли сайт пользователю или компании. Вы можете добиться этого, имея простое логическое поле в таблице веб-сайтов — [BelongsToUser]. Если true, то вы ищете пользователя, если false — вы ищете компанию.

Немного поздно, но все существующие ответы, похоже, не соответствуют действительности:

• Владелец веб-сайта является 1:Many отношением
• Веб-сайт к владельцу является 1:1 отношением
• Таблицы «Пользователи» и «Компании» не должны иметь внешнего ключа в таблице «Веб-сайты».
• Никакие данные веб-сайта, общие для пользователей и компаний или нет, не должны находиться в таблицах «Пользователи» или «Компании».
• Никакая информация о владельце, общая или нет, не должна быть в таблице веб-сайтов.
• MySQL молча игнорирует CHECK ограничения для таблиц (без обеспечения ссылочной целостности)
• СУБД должна обрабатывать логику «отношений», а не приложение, использующее базу данных.

Частично это отражено в ответе onedaywhen, но этот ответ по-прежнему упускает возможность сделать MySQL выполняет тяжелую работу и обеспечивает ссылочную целостность.

В любом случае по закону у веб-сайта может быть только один владелец. У человека или компании может быть любое количество веб-сайтов, в том числе ни одного. Ссылка в базе от владельца на сайт может быть только 1:1 на любом уровне нормализации. На самом деле это отношение 1:Many, и для каждого владельца, который владеет более чем одним веб-сайтом, потребуется несколько записей в таблице. Ссылка с веб-сайта на владельца 1:1 как в терминах базы данных, так и в реальности. Наличие ссылки с веб-сайта на владельца лучше представляет модель. С индексом в таблице веб-сайтов выполнение 1:Many поиска данного владельца становится достаточно эффективным.

Атрибут CHECK в SQL был бы отличным решением, если бы MySQL не проигнорировал его молча.

_{Документация по MySQL 13.1.20 Синтаксис CREATE TABLE}

Предложение CHECK анализируется, но игнорируется всеми механизмами хранения.

Функциональность MySQL предлагает два решения в качестве обходных путей для реализации поведения CHECK и сохранения ссылочной целостности данных. Триггеры с хранимыми процедурами — это одно из них, и оно хорошо работает со всеми типами ограничений. Легче реализовать, хотя и менее универсально, использовать VIEW с предложением WITH CHECK OPTION, которое MySQL будет реализовывать.

_{Документация по MySQL 24.5 .4 Предложение View WITH CHECK OPTION}

Предложение WITH CHECK OPTION можно указать для обновляемого представления, чтобы предотвратить вставку в строки, для которых предложение WHERE в select_statement неверно. Это также предотвращает обновление строк, для которых условие WHERE истинно, но обновление приведет к тому, что оно не будет истинным (другими словами, оно предотвращает обновление видимых строк до невидимых строк).

Сайт MySQLTUTORIAL дает хороший пример обоих вариантов в их Введение в SQL ПРОВЕРИТЬ ограничение учебник. (Вы должны подумать об опечатках, но в остальном хорошо.)

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

Рекомендуемое решение

Для минимального воздействия на существующую схему и приложение, обращающееся к данным, оставьте таблицы Users и Companies такими, какие они есть. Переименуйте таблицу Websites и замените ее представлением с именем Websites, к которому приложение сможет по-прежнему обращаться. Все старые запросы к Websites должны по-прежнему работать, за исключением случаев, когда требуется информация о владельце. Так:

Установка

-- Keep the `Users` table about "users"
CREATE TABLE `Users` (
    `id` INT SERIAL PRIMARY KEY,
    `name` VARCHAR(180),
    -- user_attributes
);

-- Keep the `Companies` table about "companies"
CREATE TABLE `Companies` (
    `id` SERIAL PRIMARY KEY,
    `name` VARCHAR(180),
    -- company_attributes
);

-- Attach ownership information about the website to the website's record in the `Websites` table, renamed to `WebsitesData`
CREATE TABLE `WebsitesData` (
    `id` SERIAL PRIMARY KEY,
    `name` VARCHAR(255),
    `is_personal` BOOL,
    `owner_user` BIGINT UNSIGNED DEFAULT NULL,
    `owner_company` BIGINT UNSIGNED DEFAULT NULL,
    website_attributes,
    FOREIGN KEY `WebsiteOwner_User` (`owner_user`)
        REFERENCES `Users` (`id`)
            ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY `WebsiteOwner_Company` (`owner_company`)
        REFERENCES `Companies` (`id`)
            ON DELETE RESTRICT ON UPDATE CASCADE,
);

-- Create a new `VIEW` with the original name of `Websites` as the gateway to the website records which can enforce the constraints you need
CREATE VIEW `Websites` AS
SELECT * FROM `WebsitesData` WHERE
    (`is_personal`=TRUE AND `owner_user` IS NOT NULL AND `owner_company` IS NULL) OR
    (`is_personal`=FALSE AND `owner_user` IS NULL AND `owner_company` IS NOT NULL)
WITH CHECK OPTION;

использование

-- Use the Websites VIEW for the INSERT, UPDATE, and SELECT operations as you normally would and leave the WebsitesData table in the background.
INSERT INTO `Websites` SET
    `is_personal`=TRUE,
    `owner_user`=$userID;
INSERT INTO `Websites` SET
    `is_personal`=FALSE,
    `owner_company`=$companyID;

-- Or, using different field lists based on the type of owner
INSERT INTO `Websites` (`is_personal`,`owner_user`, ...)
    VALUES (TRUE, $userID, ...);
INSERT INTO `Websites` (`is_personal`,`owner_company`, ...)
    VALUES (FALSE, $companyID, ...);

-- Or, using a common field list, and placing NULL in the proper place
INSERT INTO `Websites` (`is_personal`,`owner_user`,`owner_company`,...)
    VALUES (TRUE, $userID, NULL, ...);
INSERT INTO `Websites` (`is_personal`,`owner_user`,`owner_company`,...)
    VALUES (FALSE, NULL, $companyID, ...);

-- Change the company that owns a website
-- Will ERROR if the site was owned by a User.
UPDATE `Websites` SET `owner_company`=$new_companyID;

-- Force change the ownership from a User to a Company
UPDATE `Websites` SET
    `owner_company`=$new_companyID,
    `owner_user`=NULL,
    `is_personal`=FALSE;

-- Force change the ownership from a Company to a User
UPDATE `Websites` SET
    `owner_user`=$new_userID,
    `owner_company`=NULL,
    `is_personal`=TRUE;

-- Selecting the owner of a site without needing to know if it is personal or not
(SELECT `Users`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Users` ON `Websites`.`owner_user`=`Users`.`id`
    WHERE `is_personal`=TRUE AND `Websites`.`id`=$siteID)
UNION
(SELECT `Companies`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Companies` ON `Websites`.`owner_company`=`Companies`.`id`
    WHERE `is_personal`=FALSE AND `Websites`.`id`=$siteID);

-- Selecting the sites owned by a User
SELECT `name` FROM `Websites`
    WHERE `is_personal`=TRUE AND `id`=$userID;
SELECT `Websites`.`name`
    FROM `Websites`
        JOIN `Users` ON `Websites`.`owner_user`=`Users`.$userID
    WHERE `is_personal`=TRUE AND `Users`.`name`="$user_name";

-- Selecting the sites owned by a Company
SELECT `name` FROM `Websites` WHERE `is_personal`=FALSE AND `id`=$companyID;
SELECT `Websites`.`name`
    FROM `Websites`
        JOIN `Comnpanies` ON `Websites`.`owner_company`=`Companies`.$userID
    WHERE `is_personal`=FALSE AND `Companies`.`name`="$company_name";

-- Listing all websites and their owners
(SELECT `Websites`.`name` AS `Website`,`Users`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Users` ON `Websites`.`owner_user`=`Users`.`id`
    WHERE `is_personal`=TRUE)
UNION ALL
(SELECT `Websites`.`name` AS `Website`,`Companies`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Companies` ON `Websites`.`owner_company`=`Companies`.`id`
    WHERE `is_personal`=FALSE)
ORDER BY Website, Owner;

-- Listing all users or companies which own at least one website
(SELECT `Websites`.`name` AS `Website`,`Users`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Users` ON `Websites`.`owner_user`=`Users`.`id`
    WHERE `is_personal`=TRUE)
UNION DISTINCT
(SELECT `Websites`.`name` AS `Website`,`Companies`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Companies` ON `Websites`.`owner_company`=`Companies`.`id`
    WHERE `is_personal`=FALSE)
GROUP BY `Owner` ORDER BY `Owner`;

Повышение уровня нормализации

В качестве технического примечания по нормализации информация о владельце может быть исключена из таблицы Websites и создана новая таблица для хранения данных о владельце, включая столбец is_normal.

CREATE TABLE `Websites` (
    `id` SERIAL PRIMARY KEY,
    `name` VARCHAR(255),
    `owner` BIGINT UNSIGNED DEFAULT NULL,
    website_attributes,
    FOREIGN KEY `Website_Owner` (`owner`)
        REFERENCES `WebOwners` (id`)
            ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE `WebOwnersData` (
    `id` SERIAL PRIMARY KEY,
    `is_personal` BOOL,
    `user` BIGINT UNSIGNED DEFAULT NULL,
    `company` BIGINT UNSIGNED DEFAULT NULL,
    FOREIGN KEY `WebOwners_User` (`user`)
        REFERENCES `Users` (`id`)
            ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY `WebOwners_Company` (`company`)
        REFERENCES `Companies` (`id`)
            ON DELETE RESTRICT ON UPDATE CASCADE,
);

CREATE VIEW `WebOwners` AS
SELECT * FROM WebsitesData WHERE
    (`is_personal`=TRUE AND `user` IS NOT NULL AND `company` IS NULL) OR
    (`is_personal`=FALSE AND `user` IS NULL AND `company` IS NOT NULL)
WITH CHECK OPTION;

Я считаю, однако, что созданное VIEW с его ограничениями предотвращает любую из аномалий, на устранение которых направлена ​​нормализация, и добавляет сложности, которые не нужны в данной ситуации. В любом случае процесс нормализации всегда является компромиссом.