[sql-server] 여러 테이블에 대한 외래 키

내 데이터베이스에 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 필드를 통해 그룹이나 사용자가 소유 할 수 있습니다.

티켓과 선택적으로 사용자 또는 그룹 간의이 관계를 설명 하는 가장 정확한 방법은 무엇입니까 ?

티켓 테이블에 어떤 유형이 소유하는지 알려주는 플래그를 추가해야한다고 생각합니다.

답변

몇 가지 옵션이 있으며 모두 “정확성”과 사용 용이성이 다릅니다. 항상 그렇듯이 올바른 디자인은 필요에 따라 다릅니다.

Ticket에 OwnedByUserId와 OwnedByGroupId라는 두 개의 열을 만들고 각 테이블에 대해 nullable 외래 키를 가질 수 있습니다.
ticket : user 및 ticket : group 관계를 모두 활성화하는 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
)

답변

@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있으며 둘 다 nullable 외래 키입니다. (다른 두 테이블의 각 열은 이에 따라 기본 키가됩니다.) CK_Ticket_GroupUser검사 제약 조건은 두 외래 키 열 중 하나만 참조를 포함하도록합니다 (다른 하나는 NULL이므로 둘 다 null을 허용해야 함).

( Ticket.ID이 특정 구현에는 기본 키 가 필요하지 않지만 이와 같은 테이블 에 기본 키 가있는 것은 확실히 해가되지 않습니다.)

답변

또 다른 옵션은 Ticket소유 엔터티 유형 ( User또는 Group)을 지정하는 하나의 열 , 참조 된 User또는 GroupID가있는 두 번째 열이 있고 외래 키를 사용하지 않고 대신 트리거를 사용하여 참조 무결성을 적용하는 것입니다.

Nathan의 우수한 모델 (위)에 비해 두 가지 장점이 있습니다 .

보다 즉각적인 명확성과 단순성.
더 간단한 쿼리 작성.

답변

또 다른 접근 방식은 각 잠재적 리소스 유형에 대한 열을 포함하는 연결 테이블을 만드는 것입니다. 귀하의 예에서 두 가지 기존 소유자 유형 각각에는 자체 테이블이 있습니다 (참조 할 항목이 있음을 의미 함). 항상 이런 경우라면 다음과 같이 할 수 있습니다.

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_ID int NOT NULL,
    Subject varchar(50) NULL
)

CREATE TABLE dbo.Owner
(
    ID int NOT NULL,
    User_ID int NULL,
    Group_ID int NULL,
    {{AdditionalEntity_ID}} int NOT NULL
)

이 솔루션을 사용하면 데이터베이스에 새 항목을 추가 할 때 새 열을 계속 추가하고 @Nathan Skerl에 표시된 외래 키 제약 조건 패턴을 삭제하고 다시 만듭니다. 이 솔루션은 @Nathan Skerl과 매우 유사하지만 (선호도에 따라) 다르게 보입니다.

각각의 새로운 소유자 유형에 대해 새 테이블을 가지지 않으려는 경우 각 잠재적 소유자에 대한 외래 키 열 대신 owner_type을 포함하는 것이 좋습니다.

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_ID int NOT NULL,
    Owner_Type string NOT NULL, -- In our example, this would be "User" or "Group"
    Subject varchar(50) NULL
)

위의 방법을 사용하면 원하는만큼 소유자 유형을 추가 할 수 있습니다. Owner_ID에는 외래 키 제약 조건이 없지만 다른 테이블에 대한 참조로 사용됩니다. 단점은 스키마를 기반으로 즉시 명확하지 않기 때문에 소유자 유형이 무엇인지 확인하려면 테이블을 확인해야한다는 것입니다. 소유자 유형을 미리 모르고 다른 테이블에 연결되지 않는 경우에만 이것을 제안합니다. 소유자 유형을 미리 알고 있다면 @Nathan Skerl과 같은 솔루션을 사용합니다.

SQL이 잘못되면 미안합니다. 그냥 함께 던졌습니다.

답변

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

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

플래그를 사용하는 대신 원하는 것을 나타내는 가장 일반적인 방법이라고 생각합니다.