SQL一对一关系定义

Question

我正在设计一个数据库，但不确定如何定义其中一种关系。这是情况:

创建发票

如果产品没有库存，则需要制造它并创建工作订单。

关系是一对一的。但是，有时会出于其他目的创建工作订单，因此 WorkOrder 表也将以类似的一对一关系链接到其他表。此外，一些发票根本没有工作订单。这意味着我无法通过在两个表中使用相同的主键以正常方式定义这些关系。我没有这样做，而是创建了一个链接表，然后在两个字段上设置唯一索引以定义一对一关系(见图)。

(来源: markevans.org)
.
这是最好的方法吗？
干杯
标记
编辑:我刚刚意识到这种设计将允许将单个工作订单链接到发票以及我通过 2 个链接表提到的其他表之一。我想没有一个解决方案是完美的。

Answer 1

好的，这个答案是特定于 SQL Server 的，但应该适用于其他 RDBMS，只需做一些工作。据我所知，我们有以下限制:

一张发票可能与 0 或 1 个工单相关联

工单必须与发票或 ABC 或 DEF 相关联

我会设计 WorkOrder 表如下:

CREATE TABLE WorkOrder (
     WorkOrderID int IDENTITY(1,1) not null,
     /* Other Columns */
     InvoiceID int null,
     ABCID int null,
     DEFID int null,
     /* Etc for other possible links */
     constraint PK_WorkOrder PRIMARY KEY (WorkOrderID),
     constraint FK_WorkOrder_Invoices FOREIGN KEY (InvoiceID) references Invoice (InvoiceID),
     constraint FK_WorkOrder_ABC FOREIGN KEY (ABCID) references ABC (ABCID),
     /* Etc for other FKs */
     constraint CK_WorkOrders_SingleFK CHECK (
          CASE WHEN InvoiceID is null THEN 0 ELSE 1 END +
          CASE WHEN ABCID is null THEN 0 ELSE 1 END +
          CASE WHEN DEFID is null THEN 0 ELSE 1 END
          /* + other FK columns */
          = 1
     )
)

因此，基本上，无论定义了多少个 PK，该表都仅限于 FK 到另一个表。如有必要，计算列可以告诉您它链接到的项目的“类型”，基于哪个 FK 列是非空的，或者类型和单个 int 列可能是真实的列，以及 InvoiceID、ABCID 等可以是计算列。

最后要确保的是发票只有 0 或 1 个工单。如果您的 RDMBS 忽略唯一约束中的空值，这就像将此类约束应用于每个 FK 列一样简单。对于 SQL Server，您需要使用筛选索引 (>=2008) 或索引 View (<=2005)。我将只显示过滤后的索引:

CREATE UNIQUE INDEX IX_WorkItems_UniqueInvoices on
    WorkItem (InvoiceID) where (InvoiceID is not null)

处理保持 WorkOrders 直的另一种方法是在 WorkOrder 中包含一个 WorkOrder 类型列(例如“发票”、“ABC”、“DEF”)，包括受检查约束约束的计算或列以包含链接表中的匹配值，并引入第二个外键:

CREATE TABLE WorkOrder (
     WorkOrderID int IDENTITY(1,1) not null,
     Type varchar(10) not null,
     constraint PK_WorkOrder PRIMARY KEY (WorkOrderID),
     constraint UQ_WorkOrder_TypeCheck UNIQUE (WorkOrderID,Type),
     constraint CK_WorkOrder_Types CHECK (Type in ('INVOICE','ABC','DEF'))
)
CREATE TABLE Invoice_WorkOrder (
     InvoiceID int not null,
     WorkOrderID int not null,
     Type varchar(10) not null default 'INVOICE',
     constraint PK_Invoice_WorkOrder PRIMARY KEY (InvoiceID),
     constraint UQ_Invoice_WorkOrder_OrderIDs UNIQUE (WorkOrderID),
     constraint FK_Invoice_WorkOrder_Invoice FOREIGN KEY (InvoiceID) references Invoice (InvoiceID),
     constraint FK_Invoice_WorkOrder_WorkOrder FOREIGN KEY (WorkOrderID) references WorkOrder (WorkOrderID),
     constraint FK_Invoice_WorkOrder_TypeCheck FOREIGN KEY (WorkOrderID,Type) references WorkOrder (WorkOrderID,Type),
     constraint CK_Invoice_WorkOrder_Type CHECK (Type = 'INVOICE')
)

这个模型的唯一缺点，虽然更接近您的原始提案，但您可以拥有一个实际上并未链接到任何其他项目的工作订单(尽管它声称是用于例如发票)。