不同表上多个主键的外键

Question

我有一个内置了成熟的会计系统的物业管理应用程序。我有一个日记帐分录表，用于控制各种会计活动的所有过帐，例如：

发票

付款

账单

存款

在某些情况下，有必要将这些实体连接到日记账分录表格中，以便按不同的属性和单位聚合会计分录。

我正在寻找做这件事的最好方法。我有几个选择：

1)在日志条目表中添加一个外键以链接到invoice_id、payment_id、bill_id、deposit_id，但是这些外键的大多数组合将是互斥的(即存款不会有付款)，因此对于给定的日记条目，我可能会在不适用于该给定的日记条目的那些外键中具有空值。

2)我可以创建一个外键，我们称它为doc_id，另一个列doc_type表示文档的类型(发票、付款、账单、存款等)，并让doc_id和document_type_id的组合引用其中一个扩展表上的主键(即doc_id =1& doc_type = Invoice，该组合将引用发票表上的主键)。

到底是哪种方式更好，还是我的想法完全错了？

Answer 1

这听起来像是一个标准的基本实体/子实体模式。有一个表，我们称它为JournalEntries，它包含所有日志条目共有的属性: ID、条目类型、创建时间、创建者等等。

create table JournalEntries(
    ID       Int  auto_generating primary key,
    EType    char( 1 )  not null check( EType in( 'I', 'P', 'B', 'D' )) -- Invoice, Payment, etc.
    Amount   currency  not null,
    CreateDate  Date not null,
    ...,     -- other common attributes
    constraint UQ_JournalEntryType unique( ID, EType ) -- create anchor for FKs
);

请注意，ID是主键，因此是唯一的。因此，从域定义的角度来看，使ID和EType组合唯一的约束是多余的。它所做的一切就是为外键定义一个锚点。

这些FK将出现在子实体表中--每个子实体对应一个表:发票、付款、账单和存款。请注意，如果在JournalEntries表中将条目定义为存款(EType = 'D')，则只能在存款表中创建相应的条目。例如，您不能在Payments表中错误地使用该ID。

让我们定义一个子实体表：

create table Invoices(
    ID    int   primary key, -- value generated by JournalEntries table
    IType char( 1 ) not null check( IType = 'I' ), -- Nothing but invoices
    ...,   -- Invoice-specific attributes
    constraint FK_InvoiceToEntry foreign key( ID, IType )
      references JournalEntries( ID, EType )
);

现在，让我们创建一个活动，该活动始终有一个与之关联的发票，并且可以有任意数量的其他条目。这些约束确保只能插入发票，并且ID值必须与定义为发票的JournalEntries条目匹配。

create table Activities(
  ID   int   auto_generating primary key,
  InvID int  not null,
  IType char( 1 ) check( IType = 'I' ),
  ...,   -- other data
  constraint FK_ActivityInvoice foreign key( InvID, Type )
);

可能有任意数量的附加条目，并且它们可能是任何条目类型，因此您需要一个交叉表：

create table ActivityEntries(
    ActID  int  not null,
    EntID  int  not null,
    DateEntered date not null,
    constraint FK_ActEntry_Activity foreign key( ActID )
      references Activities( ID ),
    constraint FK_ActEntry_JEntry foreign key( EntID )
      references JournalEntries( ID )
);

请注意，“日志条目”是与来自某个子实体表的相关数据连接的JournalEntries数据。因此，对任何日志条目的FK引用都应该引用JournalEntries表，而不是任何子实体表，即使您知道它是哪种类型的条目。因此，Activities行使用EType字段引用JournalEntries表作为额外的数据完整性工作，因为它必须是发票。交叉表包含任何类型的条目，因此它的FK目标就是PK。

注意:为了便于说明，JournalEntries表中的类型指示符由check语句约束。在实际的数据库中，一个更好的设计应该是一个条目类型查找表。这维护了数据的完整性，但是一个更灵活的设计。(再加上MySQL仍然(！)不实现检查约束。)