help: designing survey database to handle different TYPES of questions
hi! I'm trying to figure out how you would make a database to create surveys that will be composed of different types of questions. The key factor is that the administrator needs to be able to use the database to create custom surveys that may have any number of different types of equations, such as multiple choice, yes/no, rate 1 to 5, and open-ended comments...
the way I've thought it out is to have a main table surveys:
then seperate tables for each type of question:
For the Rating and Multiple choice, I figure a parent-child table for the questions and options. The difference between the two is that Rating forces only one option, whereas multiple choice can have several.
in the end I would need tables in between each of these, like
and so on
this sounds really redundant and I know there has to be a better way! but I can't wrap my head around it and figure out where I'm missing it. I'm also not sure how exactly I'm supposed to store the results!
I should mention that if there is a packaged solution that can do this instead I'm open to that suggestion instead of building my own! if you know of one that can be easily integrated using asp.net, please let me know!!