I have a question table which contains
choice_type_id ( say freetext, binary choice and multiple choices )
Now, the question table (which has
question_id, question_text, choice_type_id) has a foreign key to the choice type table (
choice_type_id, choice_type_desc) . Now all the different choice types have a different schema.
For multiple choice, I want to store multiple choice fields against a question. For binary choice , I want to store two choices against a question and for freetext, I have no choices.
How do I populate a question's choices based on its type and model this in a sql like db. I would further want to see user interactions with each of these questions and choices within that question ( i.e. the user chose which choices or entered what text for the given question)
I can model the
question_choice table as a big table which can have all the three types and based on the type certain columns can have values and all other remain null.
This would be a very sparse representation and I'm not sure how this will scale.
What would be the best schema design considering I want fast reads and the question types will keep increasing further.