sql-server - 复合vs代理主键

我正在设计一个具有以下要求的数据库：

一个组织可以独立存在
一个组织可以有许多不同的术语（日期范围）
组织可以具有任意数量的调查类型（学生，教师，父母等）
为调查表分配了术语和调查类型

其结构可能是：

组织

 - OrganizationId INT IDENTITY(1,1) NOT NULL PRIMARY KEY

术语

 - TermId INT IDENTITY(1,1) NOT NULL PRIMARY KEY
 - OrganizationId INT NOT NULL REFERENCES Organization(OrganizationId)

SurveyType

 - SurveyTypeId IDENTITY(1,1) NOT NULL PRIMARY KEY
 - OrganizationId INT NOT NULL REFERENCES Organization(OrganizationId)

调查表格

 - SurveyFormId INT IDENTITY(1,1) NOT NULL PRIMARY KEY
 - SurveyTypeId INT NOT NULL REFERENCES SurveyType(SurveyTypeId)
 - TermId INT NOT NULL REFERENCES Term(TermId)

该结构与单个代理主键上的流行趋势保持一致。但是，该结构牺牲了数据完整性，因为SurveyForm记录很容易拥有来自不同TermId的SurveyTypeId或Organization。

为了解决数据完整性问题，您似乎必须添加OrganizationId并在复合键(OrganizationId, SurveyTypeId)和(OrganizationId, TermId)中使用它。在此示例中，这在一定程度上是可以忍受的，但是随着架构变得更加完整，组合键的大小也会增加。

因此，我的问题是，人们现在一般如何处理这种情况（大多数在线参考文献来自2008年，当时我认为可能存在不同的数据库设计问题）？因此，何时可以在表中添加外键以减少为公共表达式连接的表的数量？

最佳答案

从学术上讲，您可以沿两种血统迁移组织密钥。毕竟只有4个字节：

create table dbo.Organization (
    OrganizationId INT IDENTITY(1,1) PRIMARY KEY
);
go

create table dbo.Term (
    TermId INT IDENTITY(1,1) NOT NULL,
    OrganizationId INT NOT NULL REFERENCES dbo.Organization(OrganizationId),
    primary key (OrganizationId, TermId)
);
go

create table dbo.SurveyType (
    SurveyTypeId int IDENTITY(1,1) NOT NULL,
    OrganizationId INT NOT NULL REFERENCES dbo.Organization(OrganizationId),
    primary key (OrganizationId, SurveyTypeId)
);
go

create table dbo.SurveyForm (
    SurveyFormId INT IDENTITY(1,1) NOT NULL,
    OrganizationId int not null,
    SurveyTypeId INT NOT NULL,
    TermId INT NOT NULL,
    primary key (OrganizationId, SurveyTypeId, TermId),
    foreign key (OrganizationId, TermId) references dbo.Term (OrganizationId, TermId),
    foreign key (OrganizationId, SurveyTypeId) references dbo.SurveyType (OrganizationId, SurveyTypeId)
);
go

这些表肯定违反了某些NF，我不记得是哪一个，但是我敢肯定您可以自己处理。

尽管几乎可以认为这种设计方法对于仓库来说是必须的（特别是如果您汇总来自不同来源的数据），但我绝不建议将其用于任何实际的OLTP。更简单的解决方案是：

通过存储过程执行所有修改，该存储过程将针对这种可能的差异进行适当的检查。
确保没有用户有权直接在dbo.SurveyForm中添加/修改数据，从而规避了上述SP中实现的业务规则。

organizationId

sql-server - 复合vs代理主键