多个表的外键

我的数据库里有三张相关的表。

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 拥有。车票。业主球场。

什么是 非常正确的方式来描述票证和可选的用户或组之间的这种关系？

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

176699 次浏览
小开 
CREATE TABLE dbo.OwnerType
(
ID int NOT NULL,
Name varchar(50) NULL
)


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

我认为这将是最一般的方式来表示你想要什么，而不是使用一个标志。

小开
最佳答案

您有一些选项，它们在“正确性”和易用性方面各不相同。一如既往，正确的设计取决于您的需求。

  • 您可以简单地在 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
)
小开

@ 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上的主键是不必要的，但是在这样的表中使用一个主键肯定没有坏处。)

小开

还有一种选择是，在 Ticket中，一列指定所有实体类型(User或 Group) ，第二列引用 User或 Group id，不使用外键，而是依靠触发器来强制执行参照完整性。

我在这里看到了 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 类型创建一个新的 Table，那么最好为每个潜在的 Owner 类型包含一个 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 错误，我只是把它们混在一起。

小开

还可以使用枚举来确定 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，我想... 我希望它是有用的无论如何。

小开

我有很多这样的案例，我只是使用多态能力如下:

例子

我有一个周转率表，有 id，amount，user_id列，我需要知道每个记录的引用，所以我只需要加上两个字段 table_id和 table_type我的最终周转率表就像 id，amount，user_id，table_id，table_type。

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

纸条

如果使用 M: M 表，它需要这么多的时间两个检索记录 和我的方式

  • 缺点是营业额表记录数是增长的
  • Pons 在新记录和可读性和搜索能力方面更加灵活

提交答案