3

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

alexw
  • 192
  • 6

0 Answers0