4

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 it the business requirement thats forcing you to add FK relationship? If thats not the case then I think, current implementations looks good. Adding more FKs will add more complexity & might hamper performance in future. – Ketan Sep 25 '16 at 6:48
  • 1
    No, there's no business requirement, but my goal (like with any other foreign key) was to ensure data integrity and preventing the database from getting into an inconsistent state. – Lambo Jayapalan Sep 25 '16 at 13:14
1

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

0

Here is my 2 cents.

First I noticed that you are using uniqueidentifiers for all you primary keys. In many instances that also means you will use that for your Clustered index, in which case you will want to think about why you are using the uniqueidentifiers and if you really need them. You may be better served to change those over to integers with an incrementing identity. See here for more information

As for you specific question, I see 2 options if you want to leave the schema as it is:

  1. Add BankID to the QuizQuestionStudent table, that way you can put a foreign key on it as you want.
  2. Add a new column to the Questions table called QuestionID, this you can make an identity column and either make it the primary key or make it a unique index, then you can put a foreign key to that from the QuizQuestionStudent table.

Edit:

After the discussion below, from what I understand you want to ensure that the QuestionNumber in the QuizQuestionStudent table is associated with the correct BankId and QuizId. To pull this off you can put the BankId into the QuizQuestionStudent. Then modify the Quizzes table to either include the BankId into the Primary key, or create a unique index on the QuizId and BankId. Then you can put 2 foreign keys on the QuizQuestionStudent table; One that references the Questions Table on the BankId and QuestionNumber, then on that references teh Quizzes table on the QuizId and BankId. Between the 2 it will prevent the wrong question from being inserted.

Tables with keys:

CREATE TABLE dbo.QuestionBanks(
  BankId   INT NOT NULL
 ,CONSTRAINT pk_QuestionBanks PRIMARY KEY CLUSTERED(BankId))

CREATE TABLE dbo.Questions(
  BankId           INT
 ,QuestionNumber   INT
 ,CONSTRAINT pk_questions PRIMARY KEY CLUSTERED(BankId, QuestionNumber)
 ,CONSTRAINT fk_questions_BankId FOREIGN KEY(BankId) REFERENCES dbo.QuestionBanks(BankId))

CREATE TABLE dbo.Quizzes(
  QuizId   INT NOT NULL
 ,BankId   INT NOT NULL
 ,CONSTRAINT pk_quizzes PRIMARY KEY CLUSTERED(QuizId, BankId)
 ,CONSTRAINT fk_quizzes_bankid FOREIGN KEY(BankId) REFERENCES dbo.QuestionBanks(BankId))

CREATE TABLE dbo.QuizQuestionStudent(
  QuizId           INT NOT NULL
 ,QuestionNumber   INT NOT NULL
 ,StudentId        INT NOT NULL
 ,BankId           INT NOT NULL
 ,IsCorrect        BIT
 ,CONSTRAINT fk_QuizQuestionStudent_QuizID_BankID FOREIGN KEY(QuizId, BankId)
  REFERENCES dbo.Quizzes(QuizId, BankID)
 ,CONSTRAINT fk_QuizQuestionStudent_BankID_QuestionNumber FOREIGN KEY(BankId, QuestionNumber)
  REFERENCES dbo.Questions(BankId, QuestionNumber))

You can replace the primary key constraints with unique indexes if you want to keep your primary keys, or clustered index keys smaller.

  • I agree with the cautionary note about uniqueidentifiers. However, neither of your solutions prevents questions from being "incorrect" BankIds. That is, just because the question will have a valid BankId doesn't mean it will match the BankId of the Quiz. (If I'm understanding the problem context.) – Riley Major Sep 27 '16 at 19:58
  • Not necessarily, if you add BankID to the QuizQuestionStudent, you can put a foreign key that will reference both the BankID, and the question number on the Questions table, since that is defined as your primary key. – Shawn Beddes Sep 27 '16 at 20:13
  • But it might not match the BankID from the also-related Quiz table. – Riley Major Sep 27 '16 at 20:29
  • I see your point, then I only see one option. Put a unique index on the Questions the references both the BankID and the QuestionNumber, so you can foreign key to that. Then put a unique index on the Quizzes table that references the the BankID and the QuizID or add the QuizID to the primary key, so you can put the foreign key to that also. – Shawn Beddes Sep 27 '16 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.