Внешний ключ для нескольких таблиц

У меня есть 3 релевантные таблицы в моей базе данных.

CREATE TABLE dbo.Group
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)  

CREATE TABLE dbo.User
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)

CREATE TABLE dbo.Ticket
(
    ID int NOT NULL,
    Owner int NOT NULL,
    Subject varchar(50) NULL
)

Пользователи принадлежат нескольким группам. Это делается с помощью многих-многих отношений, но в этом случае не имеет значения. Билет может принадлежать либо группе, либо пользователю через поле dbo.Ticket.Owner.

Каким образом MOST CORRECT описывает эту взаимосвязь между билетом и, возможно, пользователем или группой?

Я думаю, что я должен добавить флаг в таблицу билета, в котором говорится, какой тип принадлежит ему.

Ответы

Ответ 1

У вас есть несколько вариантов, различающихся по "правильности" и простоте использования. Как всегда, правильный дизайн зависит от ваших потребностей.

Вы можете просто создать два столбца в Ticket, OwnedByUserId и OwnedByGroupId и иметь обнуляемые внешние ключи для каждой таблицы.
Вы можете создать справочные таблицы M: M, включающие как тикет: пользователь, так и тикет: групповые отношения. Возможно, в будущем вы захотите разрешить владение одним билетом нескольким пользователям или группам? Этот дизайн не предусматривает, что билет должен принадлежать только одному объекту.
Вы можете создать группу по умолчанию для каждого пользователя и иметь билеты, принадлежащие либо истинной группе, либо группе пользователей по умолчанию.
Или (мой выбор) моделировать сущность, которая действует как база для пользователей и групп и имеет тикеты, принадлежащие этой сущности.

Вот грубый пример, используя вашу опубликованную схему:

create table dbo.PartyType
(   
    PartyTypeId tinyint primary key,
    PartyTypeName varchar(10)
)

insert into dbo.PartyType
    values(1, 'User'), (2, 'Group');


create table dbo.Party
(
    PartyId int identity(1,1) primary key,
    PartyTypeId tinyint references dbo.PartyType(PartyTypeId),
    unique (PartyId, PartyTypeId)
)

CREATE TABLE dbo.[Group]
(
    ID int primary key,
    Name varchar(50) NOT NULL,
    PartyTypeId as cast(2 as tinyint) persisted,
    foreign key (ID, PartyTypeId) references Party(PartyId, PartyTypeID)
)  

CREATE TABLE dbo.[User]
(
    ID int primary key,
    Name varchar(50) NOT NULL,
    PartyTypeId as cast(1 as tinyint) persisted,
    foreign key (ID, PartyTypeId) references Party(PartyID, PartyTypeID)
)

CREATE TABLE dbo.Ticket
(
    ID int primary key,
    [Owner] int NOT NULL references dbo.Party(PartyId),
    [Subject] varchar(50) NULL
)

Ответ 2

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

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

Вот как он мог выглядеть при применении к вашим таблицам:

CREATE TABLE dbo.[Group]
(
    ID int NOT NULL CONSTRAINT PK_Group PRIMARY KEY,
    Name varchar(50) NOT NULL
);

CREATE TABLE dbo.[User]
(
    ID int NOT NULL CONSTRAINT PK_User PRIMARY KEY,
    Name varchar(50) NOT NULL
);

CREATE TABLE dbo.Ticket
(
    ID int NOT NULL CONSTRAINT PK_Ticket PRIMARY KEY,
    OwnerGroup int NULL
      CONSTRAINT FK_Ticket_Group FOREIGN KEY REFERENCES dbo.[Group] (ID),
    OwnerUser int NULL
      CONSTRAINT FK_Ticket_User  FOREIGN KEY REFERENCES dbo.[User]  (ID),
    Subject varchar(50) NULL,
    CONSTRAINT CK_Ticket_GroupUser CHECK (
      CASE WHEN OwnerGroup IS NULL THEN 0 ELSE 1 END +
      CASE WHEN OwnerUser  IS NULL THEN 0 ELSE 1 END = 1
    )
);

Как вы можете видеть, таблица Ticket имеет два столбца: OwnerGroup и OwnerUser, оба из которых являются нулевыми внешними ключами. (Соответствующие столбцы в двух других таблицах являются соответственно первичными.) Ограничение проверки CK_Ticket_GroupUser гарантирует, что только один из двух столбцов внешнего ключа содержит ссылку (другой - NULL, поэтому оба значения должны быть обнуляемыми).

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

Ответ 3

Еще один вариант - иметь в Ticket один столбец, в котором указан тип объекта-владельца (User или Group), второй столбец с указанным идентификатором User или Group и НЕ использовать внешние ключи, а вместо этого полагаться на триггер для обеспечения ссылочной целостности.

Два превосходства Натана перед отличной моделью (выше):

Более немедленная ясность и простота.
Более простые запросы для записи.

Ответ 4

CREATE TABLE dbo.OwnerType
(
    ID int NOT NULL,
    Name varchar(50) NULL
)

insert into OwnerType (Name) values ('User');
insert into OwnerType (Name) values ('Group');

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