在SQL中处理一对一关系的最佳方法是什么？
假设我有一些Alpha事物可能与Bravo或者Charlie事物相关或者无关。
这些是一对一的关系：没有Alpha会涉及不止一个Bravo。而且，Bravo不会与一个以上的Alpha相关。
我有一些目标：
- 一个易于学习和维护的系统。
- 在数据库中强制执行数据完整性。
- 一种与我的数据的现实世界逻辑组织相匹配的架构。
- 我编程中的类/对象很好地映射到数据库表(la Linq to SQL)
- 快速的读写操作
- 有效利用空间(很少有空字段)
我有三个主意
PK = primary key FK = foreign key NU = nullable
一个包含许多nullalbe字段的表(平面文件)
Alphas
--------
PK AlphaId
AlphaOne
AlphaTwo
AlphaThree
NU BravoOne
NU BravoTwo
NU BravoThree
NU CharlieOne
NU CharlieTwo
NU CharlieThree
许多具有零nullalbe字段的表
Alphas
--------
PK AlphaId
AlphaOne
AlphaTwo
AlphaThree
Bravos
--------
FK PK AlphaId
BravoOne
BravoTwo
BravoThree
Charlies
--------
FK PK AlphaId
CharlieOne
CharlieTwo
CharlieThree
两者最好(或者最坏)：许多表都有很多nullal外键
Alphas
--------
PK AlphaId
AlphaOne
AlphaTwo
AlphaThree
NU FK BravoId
NU FK CharlieId
Bravos
--------
PK BravoId
BravoOne
BravoTwo
BravoThree
Charlies
--------
PK CharlieId
CharlieOne
CharlieTwo
CharlieThree
如果Alpha必须是Bravo或者Charlie，但不能同时是两者，该怎么办？
如果Alphas不仅可以是Bravos和Charlies，还可以是Delta，Echos，Foxtrots或者Golfs等中的任何一个，该怎么办？
编辑：这是问题的一部分：哪个是我的导航的最佳数据库架构？
解决方案
回答
另一种方法是使用3个表来存储3个实体，并使用一个单独的表来存储关系。
回答
就个人而言，我在第二个模型上取得了很多成功，在单个列上使用了PK / FK。
我从来没有遇到过所有Alpha都必须在Bravo或者Charlie表中有记录的情况。我一直处理1 <-> 0..1，从不处理1 <-> 1.
至于最后一个问题，那就是更多的表。
回答
我们可能有一个连接表，用于指定Alpha和相关的ID。然后，我们可以添加另一列，以指定它是Bravo，Charlie或者其他名称的ID。使列保持在Alpha上，但确实增加了连接查询的复杂性。
回答
回答
如果我们希望每个Alpha仅通过一个Bravo关联，那么我会结合使用FK / PK投票赞成这种可能性：
Bravos
--------
FK PK AlphaId
BravoOne
BravoTwo
BravoThree
这样，只有一个Bravo可以引用Alpha。
如果Bravos和Charlies必须互斥，则最简单的方法可能是创建一个discriminator字段：
Alpha
--------
PK AlphaId
PK AlphaType NOT NULL IN ("Bravo", "Charlie")
AlphaOne
AlphaTwo
AlphaThree
Bravos
--------
FK PK AlphaId
FK PK AlphaType == "Bravo"
BravoOne
BravoTwo
BravoThree
Charlies
--------
FK PK AlphaId
FK PK AlphaType == "Charlie"
CharlieOne
CharlieTwo
CharlieThree
这样，AlphaType字段将强制记录始终完全属于一个子类型。
回答
到目前为止，我有一个可以很好地适合模型的示例：
我有具有来自Alpha的外键alpha_id的Charlie和Bravo表。像第一个示例一样，除了alpha不是主键，bravo_id和charlie_id是主键。
我在需要寻址到这些实体的每个表上使用alpha_id，因此，为避免可能导致研究Bravo和Charlie来查找哪个Alpha是一个延迟的SQL，我创建了一个AlphaType表，并在Alpha表上拥有其ID (alpha_type_id)作为外键。这样，我可以以编程方式知道我要处理的AlphaType，而无需联接可能有成千上万条记录的表。在tSQL中：
// For example sake lets think Id as a CHAR.
// and pardon me on any mistake, I dont have the exact code here,
// but you can get the idea
SELECT
(CASE alpha_type_id
WHEN 'B' THEN '[Bravo].[Name]'
WHEN 'C' THEN '[Charlie].[Name]'
ELSE Null
END)
FROM ...
回答
我会创建一个超类型/子类型关系。
THINGS ------ PK ThingId ALPHAS ------ FK ThingId (not null, identifying, exported from THINGS) AlphaCol1 AlphaCol2 AlphaCol3 BRAVOS ------ FK ThingId (not null, identifying, exported from THINGS) BravoCol1 BravoCol2 BravoCol3 CHARLIES -------- FK ThingId (not null, identifying, exported from THINGS) CharlieCol1 CharlieCol2 CharlieCol3
因此，例如，一个具有查理但没有勇气的字母：-
insert into things values (1); insert into alphas values (1,'alpha col 1',5,'blue'); insert into charlies values (1,'charlie col 1',17,'Y');
请注意，我们不能为alpha创建多个charlie，就像我们尝试创建ThingId为1的两个charlie一样，第二个插入将获得唯一的索引/约束冲突。
回答
我们提出了很多问题，这使得很难选择任何建议的解决方案，而没有对正在尝试解决的确切问题进行更多的澄清。不仅要考虑我的澄清问题，还要考虑评估我的问题所使用的标准，以表明解决问题所需的详细信息数量：
- 一个易于学习和维护的系统。
易于学习和维护的"系统"是什么？应用程序的源代码还是通过其最终用户界面的应用程序数据？
- 在数据库中强制执行数据完整性。
我们所说的"在我的数据库中强制执行"是什么意思？这是否意味着我们不能以任何其他方式控制数据完整性，即项目仅需要基于DB的数据完整性规则？
- 一种与我的数据的现实世界逻辑组织相匹配的架构。
我们能为我们提供我们所指的现实世界，逻辑组织吗？从我们要存储的数据的三个示例中无法推断出-即假设所有三个结构都是完全错误的。除非我们了解现实世界的规范，否则我们怎么知道呢？
- 我编程中的类/对象很好地映射到数据库表(la Linq to SQL)
这个要求听起来像是手被迫使用linq to SQL创建它，是这样吗？
- 快速读写操作
什么是"快速"？ .03秒？ 3秒？ 30分钟？目前尚不清楚，因为我们没有指定要引用的数据大小和操作类型。
- 有效利用空间(少数空字段)
有效使用空间与空字段的数量无关。如果我们指的是规范化的数据库结构，那将再次取决于问题中未提供的实际规范和应用程序的其他设计元素。
回答
我假设我们将使用SQL Server 2000/2005. 我有一个我使用的一对一关系的标准模式，这与第二个想法并不太相似，但是有一些区别：
- 每个实体都必须首先拥有自己的主键，因此，除了Alpha表的外键列之外，Bravo，Charlie等表还应定义自己的代理键。通过指定一个表的主键必须与另一表的主键完全相同，使域模型变得非常不灵活。因此，实体变得非常紧密地耦合在一起，一个实体不能没有另一个实体而存在，这不是需要在数据库设计中强制执行的业务规则。
- 在Bravo和Charlie表的AlphaID列之间向Alpha表的主键列添加外键约束。这给我们一对多的关系，还允许我们仅通过设置FK列的可为空性(在当前设计中是不可能的)来指定该关系是否为强制性的。
- 向表Bravo，Charlie等的AlphaID列上添加唯一的键约束。这将创建一对一的关系，其添加好处是唯一键还可以充当索引，这可以帮助加快基于外键值检索行的查询。
这种方法的主要好处是更改变得更容易：
- 想要一对多回来吗？删除相关的唯一键，或者仅将其更改为普通索引
- 想要Bravo独立于Alpha而存在吗？我们已经有了代理键，我们所要做的就是将AlphaID FK列设置为允许NULL
