多个表的外键

Question

我的数据库中有 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 字段，票证可由组或用户拥有。

描述票证与用户或组（可选）之间关系的最正确方式是什么？

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

Answer 1

您有几个选择，所有选择的“正确性”和易用性各不相同。一如既往，正确的设计取决于您的需求。

• 您可以简单地在工单中创建两列 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
)

Answer 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 上的主键对于此特定实现来说不是必需的，但在这样的表中拥有一个绝对不会有什么坏处。）

Answer 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_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，我只是将它们放在一起。

Answer 4

另一种选择是在 Ticket 中，有一列指定所属实体类型（User 或 Group），第二列包含引用的 用户或组 id，不使用外键，而是依靠触发器来强制引用完整性。

与内森的优秀模型（上图）相比，我在这里看到了两个优点：

• 更直接的清晰度和简单性。
• 编写更简单的查询。

Answer 5

您还可以使用枚举来识别 Owner 是用户还是组，如下所示：

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 TYPE Enum_OwnerType AS ENUM ('Group', 'User');
CREATE TABLE dbo.Ticket
(
    ID int NOT NULL,
    Owner int NOT NULL,
    OwnerType Enum_OwnerType NOT NULL,
    Subject varchar(50) NULL
)

也许它并不比任何建议的解决方案更好，它可能不会提供任何优势。事实上，我认为这可能需要更改 Enum_OwnerType 甚至 ticket 才能更改 OwnerType，我想......我希望它有用反正。

Answer 6

我有很多这样的情况，我只是使用如下所示的多态能力：

示例

我有营业额表，其中包含此列 id、amount、user_id我需要知道每条记录的引用，所以我只需添加两个字段table_id和table_type，我的最终营业额表就像id， 金额， user_id、table_id、table_type。

• 如果新记录是关于像这样插入的订单记录
  [1,25000,2,22,order]
• 如果有新记录是关于这样的增量信用
  [1,25000,2,23,credit]

，请注意

如果使用 M ：M表需要花费很多时间两次检索记录
而我的方式

• 缺点是营业额表记录数变大了，
• 庞斯在新增记录方面更加灵活，可读性和搜索能力也更强

Answer 7

nathan_jr 的第四个选项（对充当用户和组的基础的实体进行建模，并拥有该实体拥有的票证）不会强制 PartyId 的引用完整性。您必须在应用程序层上执行此操作，这会带来各种麻烦。当 django 的 genericforeignkey 实现相同的解决方案时，不能真正将其称为反模式，但毫无疑问，您可以使用框架的 orm 设计更强大和高性能的东西（使用 django 的多表继承之类的东西）

Answer 8

一种解决方案是使用独立于数据记录的记录键。我们为每条记录使用一个 GUID。对几个表之一的外键约束是通过保留对其进行外键约束的“键”表来完成的。在这种情况下，组和用户将各自包含一个必须存在于密钥表中的唯一密钥，可能伴有一个代码来指示所属表。票证外键将引用键表。我们有一个参数化存储过程来插入记录，如果提交的记录中不存在该记录，则该记录会获取新的键值。新的键被插入到键表中，然后新的记录被插入到相应的表中。现有的键会导致插入异常（应该是更新。）

Answer 9

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

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

我认为这将是表示您想要的内容的最通用方法，而不是使用标志。