Redgate Data Modeler

1 April 2015

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.