Back to articles list
- 6 minutes read

A Database Model for an Online Survey. Part 3

In the conclusion to Part 2 of this series of articles, I mentioned that I would be adding more advanced features, such as:

  • Conditional ordering of questions in a survey or, in other words, the possibility for a conditional path through the survey
  • Administration of the survey
  • Reports and analytics

In this third article related to an online survey, I will extend the functionality to support conditional ordering of questions.

In the future, we may 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)?”

Conditional Path

We want to set certain conditions on the questions presented to the user based on user responses. For example, if the answer to question 4 is “yes,” then we ask question 5 and skip question 6; while if the answer to question 4 is “no,” then we skip question 5 and ask question 6).

So we need to define which questions are conditional and how to “skip” questions based on the response to a question.

Initially, to keep the conditional path simple, we will not allow conditions based on multiple choice questions, but only for polar (yes or no) questions. The design can be extended to support conditional paths based on multiple choice questions, but that is more complex and I want to start simple.

It is important that the application checks this flow for each question, as the answer to a previous question may decide the flow for the current question (to skip a question based on a previous response).

Administration & Reports

For now, we will not add administrators of the online surveys, but keep that for the next extension.

There will need to be some reports and analytics; however, I will keep this for the next version as I want to store some information first to perform analytics later.

Entities and Relationships

For the conditional path through the survey, I will extend the question_order that is defined for each survey and links the survey to the questions. As mentioned, for now, the conditional jump will only be based on polar questions, so I can define the next question to display in case of a positive response and the next question to display in case of a negative response.

Formal Design

Let’s extend the ERD that was created in Part 1 of this series of articles.

I’ll add conditional_order linked to question_order; as I mentioned earlier, I will only support conditional order through the survey based on polar questions. Now, there are a few ways this can be implemented. My needs are straight-forward, so I will chose a straight-forward implementation. If your needs are more complex, you would need a more complex solution.

It would be nice to just define the “next” question to be asked based on the response of the current question, but that will not allow us to skip a question later in the survey, so we need more flexibility.

One way is to specify how many questions to move forward in case of a positive response and how many to move forward on a negative response; however, I must specify for which question the jump applies and the response of which question to be used. So to support my example: if the answer to question 4 is “yes,” then we ask question 5 and skip question 6, while if the answer to question 4 is “no,” then we skip question 5 and ask question 6 -- this requires two entries both of which check the response to question 4, one moving forward one question (as usual) and one skipping forward two questions (to skip a question), and the other condition to be checked after answering question 5 which skips question 6.

  +-------------------+----------------------+------------------------+------------------------+ 
  | question_order_id | response_to_question | positive_response_jump | negative_response_jump |
  +-------------------+----------------------+------------------------+------------------------+
  | 4                 | 4                    | 1                      | 2                      |
  +-------------------+----------------------+------------------------+------------------------+
  | 5                 | 4                    | 1                      | null                   |
  +-------------------+----------------------+------------------------+------------------------+

An alternative is to specify the id of the next question to which the survey should jump in case of a particular response: next question in case of positive response and next question in case of negative response. This approach has pros and cons. It would allow loops to occur, which an administrator might not notice. However, loops might be a desired effect so that you can have the final question ask the respondent if they are finished with the survey and if they respond “no,” then return to the first question. In addition, the questions to jump to in case of a positive or negative response can be set up as foreign keys to the order of questions specified for the survey so that we know for sure that the specified question is defined in the survey. This is a nice check in logic implemented through referential integrity. I think that this is probably the better solution, so that is what you will find in the ERD.

So to support my example: if the answer to question 4 is “yes,” then we ask question 5 and skip question 6, while if the answer to question 4 is “no,” then we skip question 5 and ask question 6.

As mentioned, we have two rows:

Survey #1, question #4, response to question #4, positive response: next question order id = 5, negative response: next question order id = 6.

Survey #1, question #5, response to question #4, positive response: next question order id = 7. We will never get to question #5 if the response to question #4 was negative, so we always skip question #6 after asking question #5.

  +--------+----------+----------------------+-------------------------------+-------------------------------+
  | survey | question | response_to_question | positive_response_question_id | negative_response_question_id |
  +--------+----------+----------------------+-------------------------------+-------------------------------+
  | 1      | 4        | 4                    | 5                             | 6                             |
  +--------+----------+----------------------+-------------------------------+-------------------------------+
  |        | 5        | 4                    | 7                             | null                          |
  +--------+----------+----------------------+-------------------------------+-------------------------------+

When setting up the condition, we will use a foreign key (not mandatory) to ensure that the next question exists. A null value is used for a path that is not possible or, if no next question is specified, to conditionally end the survey.




I have colored the tables which were created in Part 1 of the article series in  yellow , the tables added in Part 2 in  orange , and the newly added table in  green  so that it is easier to see the additions.

Conclusion

Neither of the solutions that are described for managing conditional steps through a survey is the ultimate rule-based system, but one of my goals is to keep things simple and straight-forward. Allowing flexibility without overwhelming things in complexity. And, I’ll repeat myself, you might have other requirements. Identify your requirements; implement or adapt what you need. I believe strongly in re-use and not re-inventing the wheel.

Now we have started implementing the improvements which were discussed in parts 1 and 2 of this series of articles.

In the next articles, I will add support for these features:

  • Administration of the surveys
  • Reports and analytics
go to top