复合主键与替代主键

3

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

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

一个可能的结构如下所示:

组织机构

 - 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记录很容易来自于不同的Organization,拥有相同的TermIdSurveyTypeId
为了解决数据完整性问题,似乎需要添加OrganizationId并在组合键(OrganizationId,SurveyTypeId)(OrganizationId,TermId)中使用它。这在本例中还算可以接受,但当模式变得更加完整时,组合键的大小将会增加。
因此,我的问题是:人们现在通常如何处理这个问题(大多数在线参考资料都是来自2008年,当时可能存在不同的数据库设计关注点)?作为一个推论,什么时候向表中添加外键以减少常见表达式的连接表数量是可以接受的呢?

1
您可以始终保留复合键以确保菱形结构的完整性,并添加代理键以使与其他表的连接可管理... - Branko Dimitrijevic
2个回答

2

从学术角度来看,你可以沿着两条谱系迁移组织密钥。毕竟这只有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中实施的业务规则。

非常棒且富有洞见的回答。顺便说一下,在我浏览的许多数据库设计资源(在线、书籍)中,我经常找不到这种实际问题和解决方案。您有什么推荐吗? - user209974
@user209974,这种设计通常被认为是过度的(而且理所当然),所以你通常不会在任何地方看到它。并非所有一致性检查都可以或应该在模式级别上实现。通常您需要在严格性和可用性之间找到某种平衡。关于文献,康诺利和贝格的一本旧版印刷品《数据库系统:设计、实现和管理的实用方法》仍然相关。在过去的30年中,这个主题几乎没有变化,因此任何版本都足够好。而且,无与伦比的实践和实际经验才是最好的。 - Roger Wolf

0

我认为有一种避免循环引用的方法,首先要确定谁真正依赖于谁,并消除冗余依赖。

问题是...是否允许将Organization随意关联到Term而不考虑任何Survey关联?我想知道Organization是否真的需要直接或间接地通过SurveyTerm相关联。例如,如果一个Organization不能与未与OrganizationSurvey相关联的Term相关联,则组织-术语关系是无用的;如果反过来,则不需要组织-调查类型。


我试图给出一个非常简单的例子,但还有更多的表格依赖于术语作为一个概念,而不是调查(例如:ActivityForm)。此外,您可以在多个术语中重复使用组织的SurveyType,并且具有多个SurveyType的术语。任何非循环结构都会导致至少其中一个的重复。 - kulin

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接