CODEWITHSUNDEEP
databaserelational-database
MindGame
MindGame

Reputation: 1251

Modeling questionaire responses

Wondering what is the best way to model out a questionnaire in terms of saving the response. So I would assume the ideal way to save it would be a table like this:

ID_pk
response_id
question_id
value

So each entry would be an answer to a question. The question is when I search on this table the performance would be bad. I was wondering do I need to create another table on the side to help me with performance. Any ideas?

Thanks

Upvotes: 0

Views: 83

Answers (2)

David
David

Reputation: 73564

The best approach depends on how flexible you need this to be. Will you have multiple choice questions? Numeric-only questinos used for calculaitons? True/False questions, etc.

The approach that worked for us to enable ultimate flexibility has this structure:

Table: Surveys

  • SurveyId PK
  • Surveyname

Table QuestionTypes

  • QuestionType PK
  • QuestionTypeText (ie "Multiple Choice", "True/False", etc.)

Table Questions

  • SurveyId FK
  • QuestionId PK
  • QuestionText
  • QuestionType

Table Answers (Contains answers ONLY for multiple choice questions)

  • Answerid PK
  • QuestionId FK
  • AnswerText

Table Results

  • ResultId PK
  • Surveyid FK
  • QuestionId FK
  • QuestionType (copied in from Question)
  • MultipleChoiceAnswerid (null unless it was a multiple choce question, FK to Answer otherwise)
  • BooleanResult (if QT = boolean, null otherwise)
  • NumericResult
  • FreeFormTextResult

and then a lot of programming logic to handle the display.

Actually, our real soultion goes a stp further and groups questions into QuestionGroups so that similar questions can be kept together, but this simplified version should give you some ideas. It's relatively complex, but I've seen professional survey software that's even more so, but follows a similar pattern. it's also worked out very well for us. We've been able to reuse it for several scenarios by creating a new SurveyType.

Upvotes: 2

swatkins
swatkins

Reputation: 13630

The idea is that anything that can be duplicated in a table should be pulled out to another table.

  • Question would be duplicated, so you'd need a questions table
  • Response could be duplicated, depends on if this would be multiple choice or fill in the blank, etc.
  • Value could be duplicated, but depends on the answer from Responses

So, I'd say to think about your questionnaire. Think about what would be saved multiple times in the db and then normalize from that.

Don't worry about performance - normalization will help more than it will hurt.

Upvotes: 1

Related Questions