同一业务规则的多个外键 [英] Multiple Foreign Keys for the same business rule

问题描述

让我们跳到一个示例，该示例说明了一个表引用多个表：

Lets jump to an example that illustrates a table referencing multiple tables :

CREATE TABLE Corses
(
   ID int PRIMARY KEY,
   .....
)  

CREATE TABLE Questions
(
    ID int PRIMARY KEY,
    .....
)

CREATE TABLE Answers
(
    ID int PRIMARY KEY,
    .....
)

CREATE TABLE Files
(
    ID INT PRIMARY KEY,

    Corse_ID INT,
    Question_ID INT,
    Answer_ID INT,

    FOREIGN KEY (Corse_ID) REFERENCES Corses(ID),
    FOREIGN KEY (Question_ID) REFERENCES Questions(ID),
    FOREIGN KEY (Answer_ID) REFERENCES Answers(ID)
)

上面的示例说明了文件对其他对象（问题，问题和答案）的真实性学习App，所有对象的业务规则都相同，如下所示：

The example above illustrates a file realtion to other objects (corses, questions and answers) in a learning App, the business rule is the same for all objects and it is as folowing :

• 文件必须附加到单个对象上，并且一个仅限e对象


• 对象可以不附加任何文件，也可以附加许多文件
  ，这使它成为1-Many关系并按上述方式进行了精化。

我的问题：

1。
当业务规则为1时，这会使文件出现的其他外键列过时，例如，如果文件附加到一个问题（如屏幕截图），则仅附加到该问题，而不附加到答案而不是一成不变的。
每次出现实际上只使用一个外键。
必须有更好的方法来模拟这种情况。
还有另一种方法可以实现更好的设计吗？

1. When the business rule is 1-Many this makes the other Forign Key columns for a file occurence obsolete, for example if a file is attached to a question like a screenshot it is attached to that question only, not to an answer and not to a corse. Only one Foreign Key is actually used for each occurence. There must be a better way to modelize this situation. Is there an other way to achive a better design ?

2。
当添加基于同一业务规则的多个1-Many关系并且子表必须依赖于父表中的行（文件必须附加到对象）时，我不能添加 NOT NULL强制执行此规则的约束，因为我不知道我的文件将附加到哪个对象。
如何实现？

2. When multiple 1-Many relations based on the same business rule are added and while the child table must depend on a row in a parent table (a file must be attached to an object) i can not add "NOT NULL" constraints to enforce this rule because i dont know which object my file will be attached to. How to achieve that ?

推荐答案

这个问题可能有多个答案，但我在＃4以下的答案是一个更好的解决方案

This question may have multiples answers but my answer below #4 is a better solution to this polymorphic association in my opnion.

首先要避免其他可能的选择：

First lest go through the other possible options :

1. 基于基数的设计：
   由于该关系是对象（Corse，Question或Answer）与File之间的1-Many关系，因此这意味着File将托管引用FK PK的FK。对象表。
   此设计存在以下问题：

1. Cardinality based design : Since the relation is a 1-Many between an Object (Corse, Question or Answer) and a File, this means that the File will host the FK referencing the PK of the Object table. This design has the following issues :

• 每次出现文件仅使用一个FK，其余的已过时。

• Only one FK is used for each File occurence and the rest is obsolete.
• NOT NULL constraints cannot be used and must be replaced with a CHECK constraint to check if at least one FK is populated.

基本表设计：
创建一个具有ID列的基本抽象对象表，并在所有对象表中添加FK（ Corse，Question和Answer）引用了抽象对象表的ID，最后引用了文件表中抽象对象表的ID。
此设计存在以下问题：

Base table design : Create a base abstract Object table with an ID column and add FK in all the objects tables (Corse, Question and Answer) that references the ID of the abstract Object table and finaly reference the ID of the abstract Object table in the File table. This design has the following issue :

• 创建对象时，它表示Corse，Question或一个答案（仅一个对象和一个对象），但是使用这种设计，我可以创建一个假定为问题的对象，并使用同一对象来表示科西斯。必须使用具有功能的 Triggers 或 CHECK 约束来避免这种情况。

• When an object is created, it is meant to represent a Corse, a Question or an Answer (one object and one object only) but using this design i can create an Objet that suppose to be a Question and use that same Object to represent a Corse. Triggers or CHECK constraints with functions must be used to avoid this scenario.

基于对象类型的设计：
创建一个ID列为PK的对象类型表，并在File表中对其进行引用，然后在没有FK的文件表中创建一个Object_ID列，最后添加一个 UNIQUE 约束。
此设计存在以下问题：

Object type based design : Create an object type table with an ID column as PK and reference it in the File table, then create an Object_ID column in the file table with no FK and finally add a UNIQUE constraint on the ObjectType_ID and the Object_ID columns. This design has the following issues :

• 文件可能被附着到（问题，问题或答案），甚至连存在。必须使用具有功能的 Triggers 或 CHECK 约束来避免这种情况。

• Files can be attched to (Corses, Questions or Answers) that don't even exist. Triggers or CHECK constraints with functions must be used to avoid this scenario.

基于许多的设计：
即使对象（Corse，Question或Answer）与File之间的关系为1-Many，也等效于对Many-Many关系的修改关系表的PK。
首先，我在每个对象的文件和对象之间创建一个关系表，然后将File_ID列用作PK。
这是File-Corse关系的DDL，对于问题和答案也是相同的：

Many-Many based design : Even though the relation is a 1-Many between an Object (Corse, Question or Answer) and a File, it is equivalent to a Many-Many relationship with a modification of the PK of the relation table. First i create a relation table between a File and an Object per object and then i use the File_ID column as the PK. This is the DDL of the File-Corse relationship which is the same for Questions and Answers as well :

< hr>

---

CREATE TABLE Files
(
    ID INT PRIMARY KEY,
    .....
)

CREATE TABLE Corses
(
   ID INT PRIMARY KEY,
   .....
)

CREATE TABLE Files_Corses
(
    File_ID INT PRIMARY KEY,
    Corse_ID INT NOT NULL,

    FOREIGN KEY (File_ID) REFERENCES Files(ID),
    FOREIGN KEY (Corse_ID) REFERENCES Corses(ID)
)

这篇关于同一业务规则的多个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！