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
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
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_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
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.