3

This question already has an answer here:

I am putting together a quiz application. (I'm calling each quiz a "challenge")

I have a "challenge" table and a "challenge_question" table which is pretty straightforward.

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)

Where I'm having trouble in normalization is with the types of question options: 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..

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

challenge_options table:
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)

This really strikes me as less than ideal. Actually, I don't see how the ordering_options would work out correctly at all. Wouldn't I need to have each of those be a separate option? Surely there must be a better way. Any suggestions/criticisms or alternate approaches?

marked as duplicate by Evan Carroll, McNets, joanolo, Marco, Michael Green May 30 '17 at 3:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

Instead of option I would use another table option_type with a PK and a name field and use a fk-relationship in challenge_options. You can easily add new kinds of challenges.

I am not sure if I would use is_true / is_correct within challenge_options. I would use a second table with user_id, challenge_options_id and a boolean correct/false.

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