sql-server - 复合 vs 代理主键

Question

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

• 组织可以独立存在
• 一个组织可以有任意数量的不同条款(日期范围)
• 一个组织可以有任意数量的调查类型(学生、教师、家长等)
• 为调查表指定了期限和调查类型

这个结构可能是:

组织

 - 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)

调查类型

 - 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 年，当时我认为可能存在不同的数据库设计问题)？作为推论，什么时候可以接受向表添加外键以减少为公共(public)表达式连接的表的数量？

Answer 1

从学术上讲，您可以沿两个沿袭迁移组织 key 。毕竟这只是 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 中实现的业务规则。