Multiple Foreign Keys for the same business rule

Question

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 ?

Onesided

Your business rules mean little without context. No one knows what "object" refers to in your rules. And the entity "files" does not have a name that seems to relate to the other entities - which adds confusion. However, your DDL seems vastly incomplete. It is not enough to define primary keys (probably synthetic ones). A particular question usually has a specific answer - generally more than 1 for quiz purposes. Yet this relationship is not captured. I think you have more work to do before you attempt to define "file". – Funk

My business rule is to be able to attach a file to an object, where an object refer to a corse, a question or an answer. The DDL is not incomplete for the understanding of my question (the rest of the DDL won't help in understanding better my question). – Onesided

Answer 1

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

Onesided

Answer 2

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

Dada

You'll want to put parentheses in that CHECK constraint to guarantee that the ANDs and ORs are ordered correctly. – Steeplechase

@Brian. No parentheses are nedeed here. AND take precedence over OR – Fortalice

Fine - I'll rephrase. Whenever I mix ANDs and ORs in a logical clause, I always include parentheses to ensure that the elements are evaluated in the order I require. – Steeplechase

This is definitely a better design but i can still see a bad side to this approach. 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 the design you suggest i can create an objet that suppose to be a question and use that same object to represent a corse (of corse im speaking about your answer in a DB context only ). – Onesided

@Brian, that's OK, you are free to include needless parentheses for explicitness, but I am a very lazy programmer who likes to save keystrokes. – Fortalice

@Samir. Yes, you are right, an object can be a course, an answer and a question at the same time with this design. However it can be prevented by triggers. On the other side I would like to note that I didn't say this is the best design. I presented it as an alternative to consider. Please see #7000783 – Fortalice