db schema for a object with multiple foreign key tables

Question

I have a question table which contains question_id, question_text and 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.

Answer 1

Simply include in your model a choice table for each type of choice that your Your field choice_type_id is essentially a kind of partitioning attribute. It indicates what type of child table is applicable.

So far you have three tables: MULTIPLE_CHOICE, BINARY_CHOICE, and FREE_TEXT (although you might consider binary choice to be a special case of multiple choice).

Each of these tables is a child of your QUESTION table, i.e. they all have a question_id foreign key. The multiple choice table will have multiple records pointing to QUESTION whereas the other tables will have one - depending on how you model binary choices.

Note that this physical arrangement could be flattened out using a view to look like the one big table with sparse columns that you want to avoid. The one big flat (sparse) table might be handy for reporting, but since you're worried about scale you might want to avoid a sparse table for physical storage.

Your user answer tables would be children of the various choice tables that intersect the USER and the appropriate choice, since each question is answered by multiple users.

For more about this approach, see my answers to this question and this question here on DBA.SE.