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?