Schema for quiz application with multiple types of questions

Databases
1

Hi all, I am using mySQL and coding with python/django for a quiz application. (I’m calling each quiz a “challenge” btw)

Setting up a “challenge” table and a “challenge_question” table is pretty straightforward. However, because I want to have different types of questions, I can’t yet figure out how to store the challenge_options (potential answers)

My questions types are: multiple choice, true/false, ordering, and fill-in-the-blank. I wonder, should I have a separate table for each type of challenge-question-option? Or is it possible to combine these into one somehow…

Here are my tables for challenge and challenge_question:

challenge table:
id
name
num_questions (I want to randomize and only display a certain number of Qs per each challenge)

challenge_question table:
id
challenge_id
question (text of the question)
required (some questions will be required to be displayed per each challenge)
question_type (here I indicate what type of question: Mult. choice, T/F, Ordering, or Fill-in-the-blank)

The tricky part is storing the ordering options. With this type of question, I present possible sequential steps that need to be ordered properly. So an “answer” to this question is in the sequence of options, not just the option itself.

If I combined all possible options into a single table, it might look be something like this

[B]challenge_options table:[/B]
id
challenge_question_id
option (text field, can be used for both multiple choice and for fill-in-the-blank style questions)
is_true (boolean field for true/false questions)
is_correct (boolean, used to determine multiple choice answer)
ordering_option_1
ordering_option_2
ordering_option_3
ordering_option_4 (these ordering fields would store the correct sequence of an ordering style of question)

I feel like this solution could work, but it seems less than ideal…

Appreciate any feedback, criticism, or suggested alternative approaches!

2

the answer is often both :slight_smile:

all the attributes they have in common, including other relationships (via FKs) should be in a supertype table, and each different type of question would then be in its own subtype table of unique attributes

search supertype/subtype for more info (there have been many threads like this in this forum)

3

Oh that sounds great, thanks I will look up how to do it!

4

Great. Of course, if you have any doubts or problems implementing it, don’t hesitate to ask

5

Ok excellent. I found a couple articles on it and I think I understand enough to implement.

I need to have 4 subtypes of my challenge_questions supertype. Each subtype will have it’s own fields of storing the answers.

Each subtype has a PK which is a FK to the quiz-question table. So example type tables would be

challenge_question_mc (multiple choice)
question_id = FK/PK to challenge_question
option = text of the option
is_correct = Boolean to indicate the answer

challenge_question_fb (fill in the blank)
question_id = FK/PK to challenge_question
option = text of the option
sequence = int (in case there may be more than one blank to fill in)

challenge_question_tf (true or false)
question_id = FK/PK to challenge_question
is_correct = Boolean

challenge_question_or (ordering type question)
question_id = FK/PK to challenge_question
option = text of the option
sequence = int to indicate the correct order

If this all looks correct, I’m not sure I quite have my mind wrapped around how to handle the ordering type of question. Here, the correctness of the answer depends on how the user placed the sequence. I think what I have here is enough, but I’m open to suggestions or alternate approaches…

I’m also going to have “challenge snapshot” tables which will store each unique challenge configuration that users are presented with every time they take a challenge. The reason for this is that each challenge will randomize a smaller number from the pool of questions, and also randomize from the pool of options for each question. But I want a user to be able to return later to a challenge they may have previously started but did not finish.

What I have so far for my challenge snapshot tables looks like this

user_challenge_snapshot table
id
challenge_id (FK to challenge table)
started (date)
submitted (date)
passed (not sure if I want this here or somewhere else)

user_challenge_snapshot_question table
id
challenge_snapshot_id (FK to user_challenge_snapshot)
question_id (FK to challenge_question)
is_correct (here I store if they got it right or not)

So then I will also need matching snapshot tables for each challenge_question subtypes, with additional related tables that store the answer the user actually entered.

I think I will create an EER Diagram and post as a screenshot later today, for anyone who may be interested.

Again, I’m open to suggestions, alternate approaches, or criticisms!

6

I believe I have a working schema now. If anyone is interested, have a look at the challenge_question table and related cq_multiple_choice, the true_false, fill_blank, and ordering tables. These have PK as a FK to the challenge_question table. In other words, they are subtypes… (I believe I have it correctly?)

Now my question is, would it be necessary to also include that “question_type” field in the challenge_question table? Or is that not really necessary? It seems to me that the field may be helpful in programming which challenge_question subtype to look up?

I next need to build my user_challenge_snapshot_question table, but I’m not sure there how to reference the subtypes. Although I have a question_id FK to the challenge_question_id, is there something obvious I’m missing that let’s me know which subtype the question is?

(The snapshot_question, snapshot_option and snapshot_answer tables are not complete btw It’s all a work in progress!)

7

yup :slight_smile:

yes, it can be very useful

i must say you have incorporated this supertype/subtype concept really well