I am designing a schema for an interview system as per the suggestion in this answer. I need to store several different types of questions:

  • Questions with a free-text response
  • Multiple choice text-response questions
  • Likert (rating) scale questions. This is essentially a multiple-choice question with numeric options.

I am keeping all questions in a q_question table. Possible answers for multiple choice questions are kept in a q_choice table, with a foreign key to q_question.

I also need to store responses to questions. I store responses to free-text questions in an q_answer_text table, and responses for multiple choice questions in a q_answer_choice table (with a foreign key into the q_choice table).

The question is, should I represent my Likert questions as free-text questions, multiple-choice questions, or something else entirely?

At the moment, the question-choice relation is one-to-one. So if I stored Likert responses as multiple-choice, I'd have to either relax this constraint, or duplicate the choices for each new Likert question (since they all use the same scale). If I just keep the numeric choice as a free-text response, I will have no way to anchor the numeric choice to a qualitative description (e.g. "strongly agree") in my database.

Clarification regarding @MDCCL's notes:

  • The relation between q_question and q_answer_* is ternary, in the context of a specific interview (this part of the design is very similar to the one outlined in this question. An interview maps a respondent (not shown) to a questionnaire (which is simply a set of questions).
  • Yes, a question can have multiple choices for the likert response. With the model I've come up with below, it would actually be a many-to-many relationship. Then the same five choices could be reused across all likert questions (which are always on the same 1-5 scale).
  • I am thinking about creating a ternary relation table among questionnaires, sections, and questions. This would allow reuse of questions and sections across different "questionnaires". This would be especially useful, for instance, in versioning.
  • The more I think about it, maybe storing different types of answers in different tables is overkill. Choices are immutable anyway (modifying the survey in any way should necessitate creating a new revision of a questionnaire). So, perhaps just storing the value of the choice directly, in the same way as a free-text response, would be more sensible.

DDL Structure

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.