Foreign Key referencing multiple tables in a Question Bank scenario I am working on a business domain in which there are QuestionBanks that contain a set of Questions. Multiple Quizzes can refer to a specific QuestionBank (BankId), and there is an entry in QuizQuestionStudent for each Question on the Quiz for each Student.

I have created the following tables to shape my database structure:

CREATE TABLE QuestionBanks (
    BankId UniqueIdentifier --PK
)

CREATE TABLE Questions (
    BankId UniqueIdentifier, --PK
    QuestionNumber Int, --PK
    QuestionText VarChar(1000)
)

CREATE TABLE Quizzes (
    QuizId UniqueIdentifier, --PK
    BankId UniqueIdentifier
)

CREATE TABLE QuizQuestionStudent (
    QuizId UniqueIdentifier, --PK
    QuestionNumber Int, --PK
    StudentId UniqueIdentifier, --PK
    IsCorrect Bit
)


What is the best way to design the schema such that the QuizQuestionStudent also has a Foreign (FK) to Questions (to ensure only valid questions get added). I essentialy want a FK that consists of columns in QuizQuestionStudent (QuestionNumber) as well columns in the Quizzes table (BankId).

One solution is to repeat the BankId for each row in QuizQuestionStudent, but that seems quite wasteful.

Is there any other way to achieve this?
Is QuestionNumber a unique identifier somehow for table Questions? Or is the QuestionBank designed to have, say, 5 different sets of questions with numbers 1 through 25?

If Questions.QuestionNumber is a mere non-unique ordering value for a given BankId, then there's no way to know, when saving a QuizQuestionStudent record, what QuestionBank a particular question belongs to without presuming that the answer is for a question in the proper bank. When QuestionNumber = 5, you'd have no idea how to check its BankId if multiple banks had a fifth question. You could presume it belongs to the same BankId as the Quiz, but then what is left for the database to check?

Now, if QuestionNumber is unique, you have a different problem. You want the Question's BankId to be required to be the same as the Quiz's BankId. Repeating the BankId in the QuizQuestionStudent table wouldn't help, because that value is coming from the app and it's what you're trying to check. You want to make sure data in two other tables conforms not just to QuizQuestionStudent values, but also to each other's values. You can't do that with a foreign key. (A significantly different design for all of the tables might eliminate the issue, but I wasn't able to come up with one.)

However, it can still be strongly enforced at the database layer. You can create an "indexed view" or "materialized view" and then constrain its values. The view can reference multiple tables. It would pull the BankId from two different paths and make sure they are the same.

CREATE VIEW QuizValidityCheck
WITH SCHEMABINDING
AS
(
SELECT
1 AS DupeCheckValue
FROM QuizQuestionStudent qqs
INNER JOIN Quizzes qz ON qqs.QuizId = qz.QuizId
INNER JOIN Questions qu ON qqs.QuestionNumber = qu.QuestionNumber
CROSS JOIN (VALUES (0),(1)) AS RowExploder(ExploderRow) -- this forces there to be 2 rows any time there's a BankId mismatch, quick causes the unique constraint below to fail
WHERE qu.BankId &lt;&gt; qz.BankId
)
GO

CREATE UNIQUE CLUSTERED INDEX IX_CHECK_BankIdMismatch ON QuizValidityCheck(DupeCheckValue)
GO


You can also use a UDF to enforce a custom CHECK constraint on a column, which could go and check the other. But there are some scenarios where that falls apart.