Wai-chuen Cheung Wai-chuen Cheung - 6 months ago 67
SQL Question

Rails: Creating a survey data structure with variable type answers

I'm creating a survey-answer data structure in my Rails app to collect user information. I'll be asking a mixture of multiple choice, number field and open ended questions so my (MySQL) data structure will need to cope with a variable data-type. How can I achieve this? My current plan is based on a previous answer here:


  1. User model (the particpant)

  2. Survey model (the survey)

  3. Question model (the question asked)

  4. Choice model (possible choice for question stored in 'text' column)

  5. Answer model (the answer that links the choice to the participant user)



This works great for just multiple choice text answers with checkboxes, but what if I want an answer to be an integer field (e.g. "What is your age?") or an open ended text field (e.g. "What could be improved?")?

Different type fields

I imagine that a Choice or Answer model with multiple columns for each possible type (e.g. text, integer, datetime, etc.) would be bad as it would be incredibly sparse.

Would multiple Choice tables for each type be better? (e.g. Choice_Text, Choice_Integer etc)

But then how would the Answer model link to the right table?

Open ended unique answers

Should I store unique text answers in the Answer model as another data column, or in the Choice model as a new entry each time?

Any help would be much appreciated. Cheers!

Answer

So I ended up using a polymorphic association to link the different types of input, and dealt with open ended text answers by adding them to the Choice_Text table.

If anyone comes across this in the future, the data structure was as follows:

class Survey < ActiveRecord::Base
  has_many :survey_questions
  has_many :survey_attempts
end

class SurveyAttempt < ActiveRecord::Base
  has_many :survey_answers
  belongs_to :survey
  belongs_to :user
end

class SurveyQuestion < ActiveRecord::Base
  has_many :survey_choices
  belongs_to :survey
end

class SurveyChoice < ActiveRecord::Base
  has_many :survey_answers
  belongs_to :survey_question
  belongs_to :survey_choice_value, polymorphic: true
end

class SurveyChoiceString < ActiveRecord::Base
  has_many :survey_choices, as: :survey_choice_value
  has_many :survey_question, through: :survey_choices
end

class SurveyChoiceText < ActiveRecord::Base
  has_many :survey_choices, as: :survey_choice_value
  has_many :survey_question, through: :survey_choices
end

class SurveyChoiceInteger < ActiveRecord::Base
  has_many :survey_choices, as: :survey_choice_value
  has_many :survey_question, through: :survey_choices
end

class SurveyAnswer < ActiveRecord::Base
  belongs_to :survey_choice
  belongs_to :survey_attempt
end