外键既指向父表又指向祖父表

Question

我有三个数据库表，分别为“项目”、“合同”和“事故”。该设计是针对基于项目的维护系统。客户能够在项目上建立合同，以维护各种设施。此外，可能或可能不与项目上的合同相关的隔离事件应该可以被报告，例如损坏的安装。

项目与合同具有1对多的关系（每个项目可以拥有多个合同或没有）。事故记录最终必须解析到一个项目，但并不总是需要存在合同。在某些情况下，一个项目可能没有任何合同，但它应该能够拥有事故。

我们的数据库设计人员建议将事故作为外键同时属于项目和合同。实际上，这是一个具有父级和祖父级别分开键的关系，以允许父记录的缺失。 另一种选择是创建一个“虚拟”合同。两种解决方案都不是我想要的。

更糟糕的是，合同还会引用来自另一个表中的“债务人”。因此，在没有合同的情况下，一个事故也应该能够引用一个债务人。

我感觉提出的方法违反了所有正常形式，并有可能带来未来问题，包括维护问题，因此我正在寻找一种备选方案，能够在这些表之间维持完整性。此外，是否有人熟悉这种方法可能会导致的进一步问题？

值得注意的是，我是负责编写与该数据库配合工作的应用程序的开发人员。该项目将使用WPF和LINQ over SQL创建。其中一个要求是应该能够查询项目记录及其所有事故，包括通过合同引用的事故。

我已经在SO上寻找类似的问题，虽然有很多处理祖父级键的问题，但没有一个与我的问题相匹配。

- Bart

---

我猜，替代有条件的可空外键到三个表之一的方法是拥有单独的“ProjectIncident”、“ContractIncident”和“DebtorIncident”表。你可以将它们变得更加复杂，并将其超类化为一个“Table per Class”继承（“BaseIncident”），完全没有外键？ - StuartLC

Answer 1

这里有一种更简单的方法。为每个项目准备一个虚拟合同，用于解决没有正式合同的事件。该合同将始终用于解决项目问题。

这简化了数据库设计，但也引入了其他问题。例如，要查找没有合同的事件，您不会在合同列中寻找NULL。您需要在合同表中寻找“不是真正的合同”。根据情况，这可能是更优雅的解决方案。这也解决了Debtor的问题。

这确实带来了另一个问题，即可能涉及多个合同的事件。事实上，您可能最终需要支持另一个表，它是事件和项目之间的n-m映射。

- Gordon Linoff

---

我们的组织不允许在多个合同中发生事故。我还没有考虑到您描述的实际上识别虚拟合同的问题，这是一个非常好的观点。尽管我还不确定这是否会成为我们系统的问题。 - Bart

Answer 2

我对此没有偏好或经验。乍一看，我喜欢“虚拟”合同的想法。如果您这样做，建议在合同中添加一个特定的列，以便您可以轻松地看到它是虚拟合同还是真实合同。

单个虚拟合同可以容纳所有没有合同的事件。当您开始使用虚拟合同的字段（如债务人）时，虚拟合同的风险就会出现。 如果没有合同，同一项目的所有事件的债务人是否相同？如果不是，这意味着您将最终得到多个虚拟合同（每个债务人一个）。也许在未来，您会有其他字段，最终导致每个事件一个虚拟合同。

我不知道您的业务，但这些没有合同的事件可能对您的设计非常危险。

另一种方法是将合同用作事件的蓝图/模板。在这种情况下，您在事件级别上具有债务人ID、合同ID和项目ID(...)。当事件被创建并链接到合同时，某些合同信息将被复制到事件中。这为事件级别提供了最大的灵活性，这是您需要处理没有合同的事件所需的。如果存在相关合同，您可以决定使这些事件字段只读并同步。

- Conffusion

Answer 3

我会使用触发器来强制执行完整性。现在，一个 Incident 中始终需要 ProjectID。当添加 ContractID 作为外键时，触发器将检查插入的 ContractID 的 ProjectID 是否与已插入的 ProjectID 相匹配，否则不允许插入。这始终确保您不会出现损坏的关系。此外，这使得生成项目内所有事故或特定合同内所有事故的报告变得更加容易。

- TMNuclear

Answer 4

我一点也不喜欢“虚假”的数据。如果一个事件只涉及一个合同，或者没有合同，那么我会采取这样的方法：

CREATE TABLE dbo.Project 
(
        ProjectID INT IDENTITY,
        Filler CHAR(1) NULL,
    CONSTRAINT PK_Project__ProjectID PRIMARY KEY (ProjectID)
);

CREATE TABLE dbo.Contract 
(
        ContractID INT IDENTITY,
        ProjectID INT NOT NULL,
        Filler CHAR(1) NULL,
    CONSTRAINT PK_Contract__ContractID PRIMARY KEY (ContractID),
    CONSTRAINT FK_Contract__ProjectID FOREIGN KEY (ProjectID) REFERENCES dbo.Project (ProjectID),
    CONSTRAINT UQ_Contract__ContractID_ProjectID UNIQUE (ContractID, ProjectID)
);

CREATE TABLE dbo.Incident
(
        IncidentID INT IDENTITY,
        ProjectID INT NOT NULL,
        ContractID INT NULL,
        Filler CHAR(1) NULL,
    CONSTRAINT PK_Incident__IncidentID PRIMARY KEY (IncidentID),
    CONSTRAINT FK_Incident__ProjectID FOREIGN KEY (ProjectID) REFERENCES dbo.Project (ProjectID),
    CONSTRAINT FK_Incident__ContractID FOREIGN KEY (ContractID, ProjectID) REFERENCES dbo.Contract (ContractID, ProjectID)
);

-- CREATE TWO DUMMY PROJECTS
INSERT dbo.Project DEFAULT VALUES;
INSERT dbo.Project DEFAULT VALUES;

-- ADD A CONTRACT TWO EACH
INSERT dbo.Contract (ProjectID)
SELECT  ProjectID
FROM    Project;

-- ADD AN INCIDENT TO EACH WITH NO CONTRACT
INSERT dbo.Incident (ProjectID)
SELECT  ProjectID
FROM    Project;

-- ADD A VALID INCIDENT TO EACH CONTRACT
INSERT dbo.Incident (ContractID, ProjectID)
SELECT  ContractID, ProjectID
FROM     dbo.Contract;

-- TRY AND ADD INVALID CONTRACT TO FIRST PROJECT
INSERT dbo.Incident (ContractID, ProjectID)
SELECT  c.ContractID, p.ProjectID
FROM    dbo.Project AS p
        CROSS JOIN dbo.Contract AS c
WHERE   c.ProjectID != p.ProjectID;

这将会以错误的方式失败：

>The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Incident__ContractID". The conflict occurred in database "TestDB", table "dbo.Contract".

外键可以引用对Contract的唯一约束，这使您能够在dbo.Incident中强制执行完整性，即您不能输入不正确映射到正在输入的合同的项目。场景的唯一真正缺点是当填充ContractID时，您会复制ProjectID，但我认为这不是一个重大问题。肯定（在我看来）比虚拟数据少得多。

然后很容易识别虚拟合同：

SELECT  *
FROM    dbo.Incident
WHERE   ContractID IS NULL;

- GarethD

Answer 5

我认为在系统中引入一个虚拟合同是可行的方法。通常我会创建一个虚拟合同，并将其标记为已删除（这样它就不会出现在任何查询结果中）。然后我会在您的解决方案中创建另一个项目来保存常量。接着，我会添加一行代码：

public static readonly int DummyContractId = 25; // Or whatever the ID is of your dummy contract ID.

现在您可以在代码中构建查询，这些查询可以排除或包含具有虚拟合同的事件。

- Icemanind