Back to articles list
In part 1 of this article series, we discussed a basic design for an online survey. In the conclusion to that article, I mentioned part 2 would cover more advanced features for our survey such as:
- Different types of questions such as multiple choice questions
- Conditional order of questions in a survey or, in other words, the possibility for a conditional path through the survey
- Administration of the surveys
- Reports and analytics
Let’s start by extending the functionality to support different types of questions.
Types of Questions
In part 1 of this series of articles, we were only using open-ended questions that consisted of a question and an answer. In this article, we will define different types of questions such as polar (yes-no) questions and multiple choice questions. Each question will be associated with a type. For polar questions, we will only allow yes/no as the answer, but, in the future, we could allow variations like true/false. Questions which are not open-ended will have possible responses from which the respondent can choose.
In the future, we will add questions that require a rated response. For example, “How much do you like database design; rate between 1 and 100 (with 1 indicating that you like it very little and 100 indicating that you like it immensely)?”
Entities and Relationships
For the different types of questions in the survey, I will extend the “questions” area with types and response choices.
Ideally, I would like to make a foreign key between the actual responses and the possible responses for multiple choice questions (response_choice) to ensure data integrity. This would work if all questions had response choices and open ended questions were not allowed. As I need to support open ended questions, I will have to ensure integrity of the responses in the application code.
Formal Design
We need to extend the ERD that was created in part 1 of this series of articles. As before, I will use Vertabelo, an online database modeler. If you don’t have a Vertabelo account yet, you can register for a free trial here.
I will make one comment; you will find that I generally use round numbers like 100 or 1000 to define the length of varchar fields; I am not suggesting that these are necessarily the appropriate size, but rather I use this as a shorthand instead of leaving the length undefined. When you are making use of this model, please adjust the lengths for your particular requirements. For example, will you allow a respondent to type in a very, very long answer to an open-ended question – or will you limit them to, let’s say, 1000 characters? This may depend on the application that you are building to use the database, as it may have limitations on field lengths.
I add a question_type table linked to the question: these might have a name “open ended,” “yes-no,” “multiple choice,” and, in the future, “rating.” For multiple choice questions, each question would have response_choices to be chosen from.
You could even use this to implement polar questions, but I think that is overkill. Another solution would be to link response_choice to question_type, so that the question_type row “yes-no” would be linked to response_choice rows “Yes” and “No”, but again, I don’t feel that is necessary – but you might if you want multi-lingual possibilities. You would then include a field for the respondent’s language within the response_choice table, or manage internationalization on the user interface.
I have colored the tables created in part 1 in yellow and the newly-added tables in orange so that it is easier to see the additions.
Conclusion
Now we have started implementing the improvements which were discussed in part 1 of this series of articles.
In the next article, I will add more support for the following features:
- Conditional order of questions in a survey
- Administration of the surveys
- Reports and analytics
