MySQL database structure for a questionnaire

Question

Hello there! I am willing to build a small questionnaire and I was just wondering, how the database structure should look like... I mean I have different possibilities for the answers - checkboxes, drop-down-menus, input-boxes, etc., so as for the questions. And how should I save the questionnaires itself? Should I make for each different questionnaire an own table? With the rows "question_1", "question_2", etc.? What other opportunities do you see? Thanks in advice, guys! :)

Answer 1

Since the datatype of the answers will vary (e.g. you might have a Yes/No question and then a question where the answer is multiple-choice a, b, c, d) you should probably keep it simple and make the answer a varchar.

 Questions
  id  integer pk
 question  varchar

 QuestionPossibleAnswers
id int pk
questionid int foreign key references Questions
ordinal  decimal
possibleanswer  varchar


Users
id
name



Questionnaire
id
name
description



QuestionnaireQuestions
int pk
questionnaireid foreign key references Questionnaire
questionid   foreign key references Question table
ordinal      decimal  (where in the sequence of questions this one belongs)
[put unique composite index  on (questionnaireid, questionid)]



QuestionnaireQuestionsAnswers
id int pk
QuestionnaireQuestionid  foreign key references QuestionnaireQuestions(id)
userid  foreign key references Users
answer  varchar
[unique composite index on (questionnairequestionid, userid)]

This structure gives you the ability to have master list of questions, create possible choices for answers to questions, define questionnaires as needed using a subset from the master-list of questions, and then track the answers the users supply when completing the questionnaire.