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:

Surveys:
surveyID
dateCreated

then seperate tables for each type of question:
YesNoQuestions:
questionID
questionText

CommentQuestions:
questionID
questionText
answerLength

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.

RateQuestions
questionID
questionText

RateQuestionsChoices
questionID
choiceText
choiceValue

MultChoiceQuestions
questionID
questionText

MultChoiceQuestionsChoices
questionID
choiceText
ChoiceValue

in the end I would need tables in between each of these, like

SurveyYesNoQuestions
surveyID
questionID

SurveyCommentQuestions
surveyID
questionID

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!!

thank you for your time!