Search code examples
sqlsurveyauto-generate

SQL Structure to Store Surveys and Answers - Build Tables and Queries Based on User Data?

I'm a total newbie when it comes to SQL. I'm making a site in ASP.NET for doing surveys. A privileged admin user will be able to build a survey. Then lots of other people will respond to the survey.

Once I have the set of questions for a new survey, what is the best way to automatically construct a table and an INSERT query to store the responses?

UPDATE

Thanks for the quick responses! I'm still a little unclear on how survey responses would best be stored in this scenario. Would each respondent get a row of the table? And if so, would the columns be "answer given for question k of the survey identified by the entry in column 1"?

Solution

  • You dont want to build a new table for each survey.

    Create a surveys table (SurveyID, UserID, Name, etc)

    Create a Questions Table (QuestionID, SurveyID, QuestionText, SortOrder, etc)

    optionally, Create an Options table, assuming you have multiple choice type questions (OptionID, QuestionID, OptionText, etc)

    Then when someone creates a survey, you insert into the Surveys Table, referencing that person's UserID as the foriegn key, then get the newly inserted SurveyID,

    Then for each question they add, insert it into the Questions table, using the above SurveyID as the foriegn key.. and so on.

    EDIT to answer your edit:

    Sorry I should have followed through to cover storing answers as well.

    You would want another table called SurveyResponses (ResponseID, Name, etc) And another table I'll call ResponseAnswers(ResponseAnswerID, SurveyID, ResponseID, QuestionID, AnswerText/AnswerID) Where SurveyID and ResponseID are foriegn keys to thier respective tables, and depending on wether users have multiple choice, or enter an answer, you would either store thier answer as text(varchar) or as another foriegn key to the options table.