多个表的外键 [英] Foreign Key to multiple tables

问题描述

我的数据库中有 3 个相关表.

I've got 3 relevant tables in my database.

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 字段，票证可以由组或用户拥有.

Users belong to multiple groups. This is done via a many to many relationship, but irrelevant in this case. A ticket can be owned by either a group or a user, via the dbo.Ticket.Owner field.

最正确的方式是什么来描述票证与可选的用户或组之间的这种关系?

What would be the MOST CORRECT way describe this relationship between a ticket and optionally a user or a group?

我想我应该在票证表中添加一个标志，说明拥有它的类型.

I'm thinking that I should add a flag in the ticket table that says what type owns it.

推荐答案

您有几个选项，它们的正确性"和易用性各不相同.与往常一样，正确的设计取决于您的需求.

You have a few options, all varying in "correctness" and ease of use. As always, the right design depends on your needs.

• 您可以简单地在 Ticket 中创建两列，OwnedByUserId 和 OwnedByGroupId，并为每个表设置可为空的外键.

• You could simply create two columns in Ticket, OwnedByUserId and OwnedByGroupId, and have nullable Foreign Keys to each table.

您可以创建 M:M 参考表，同时启用票证:用户和票证:组关系.也许将来您会希望允许多个用户或组拥有一张票?这种设计并没有强制要求一张票必须只归一个实体所有.

You could create M:M reference tables enabling both ticket:user and ticket:group relationships. Perhaps in future you will want to allow a single ticket to be owned by multiple users or groups? This design does not enforce that a ticket must be owned by a single entity only.

您可以为每个用户创建一个默认组，并让票证由真正的组或用户的默认组拥有.

You could create a default group for every user and have tickets simply owned by either a true Group or a User's default Group.

或者(我的选择)建模一个实体，作为用户和组的基础，并拥有该实体拥有的票证.

Or (my choice) model an entity that acts as a base for both Users and Groups, and have tickets owned by that entity.

这是一个使用您发布的架构的粗略示例:

Heres a rough example using your posted schema:

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
)

这篇关于多个表的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！