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)
)

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 :

• A file must be attached to a single object and a single object only
• An object can have none or many files attached to it Which makes it a 1-Many relationship and mdelized as above.

My question :

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. 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 ?

回答1:

Here you have one alternative design that doesn't have those problems:

CREATE TABLE Objects
(
    Id int PRIMARY KEY
);

CREATE TABLE Courses
(
   CourseId int PRIMARY KEY,
   CONSTRAINT FK_Courses_Objects FOREIGN KEY (CourseId) REFERENCES Objects(Id)
)  

CREATE TABLE Questions
(
    QuestionId int PRIMARY KEY,
    CONSTRAINT FK_Questions_Objects FOREIGN KEY (QuestionId) REFERENCES Objects(Id)

)

CREATE TABLE Answers
(
    AnswerId int PRIMARY KEY,
    CONSTRAINT FK_Answers_Objects FOREIGN KEY (AnswerId) REFERENCES Objects(Id)

)

CREATE TABLE Files
(
    FileId int PRIMARY KEY,
    ObjectId int NOT NULL CONSTRAINT FK_Files_Objects REFERENCES Objects(Id)
)

You can however solve the second problem keeping the original design:

CREATE TABLE Files
(
    FileId int PRIMARY KEY,

    CourseId int REFERENCES Courses(CourseId),
    QuestionId int REFERENCES Questions(QuestionId),
    AnswerId int REFERENCES Answers(AnswerId),

    CONSTRAINT CHK_JustOneObjectReferenced  CHECK (
        CourseId IS NOT NULL AND QuestionId IS NULL AND AnswerId IS NULL
        OR CourseId IS NULL AND QuestionId IS NOT NULL AND AnswerId IS NULL
        OR CourseId IS NULL AND QuestionId IS NULL AND AnswerId IS NOT NULL
    )
)

回答2:

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. 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 :

   • 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.

2. 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 :

   • 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.

3. 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 :

   • 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.

4. 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 :

---

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)
)