I'm trying to design survey-type application that uses user responses to determine what the next question should be. For simplicity's sake, question types can be either combo boxes (they have a set of distinct answers to choose from) or images (user selects an image file from their system). I require this to be database-driven, so questions (and any answers they depend on) need to be stored in the database.
Here is a sample question flow:
There are several use cases in this example. Initially, the user would be presented with question Q1 (no other questions visible at this time). If the user answers "Yes", both Q2 and Q10 become visible. If Q1 is "No", then Q3 becomes visible instead. This is the simplest case.
Another common scenario is shown later on. Q5, Q6, and Q7 are all shown at the same time. If the user answers those specific questions with "Yes", "Dogs", and "Yes", respectively, then Q10 is shown. In all other cases, Q8 is shown instead.
The final scenario is where two branches both lead to the same question. For example, if the user answers Q8 with "Yes", then Q10 is shown. If the answer is "No", then Q9 is shown instead, and Q10 is only revealed once the user has answered Q9 (with any result).
There can be any combination of the above scenarios. Additionally, as you can see, some questions can be reached from several different branches (Q10, for example, can be accessed on 3-4 different question flows).
The issue I'm having is coming up with a good database design to accommodate these requirements. The approach I've been aiming for to tackle this is to define some kind of dependency lookup table in the database.
Here's the general (simplified) structure I came up with:
---------- |Question| ---------- QuestionID, QuestionText --------------- |QuestionGroup| --------------- GroupID, QuestionID -------------------- |QuestionDependency| -------------------- ParentGroupID, ParentGroupValue, ChildGroupID
The idea here is that each question could belong to one or more "groups", meaning that a single answer could enable several questions at once (by activating just one group). The QuestionDependency tables is used to look up which question groups to activate based on the ParentGroupValue field. Let's look at a concrete example based on the sample question flow from above:
(In these tables,
<ans> means the question has been answered and is not blank - can be any other value.)
So for example, as you can see in the QuestionDependency table, once the answers in G1 (which only has one question) are equal to "Yes", then G2 (which contains Q2 and Q10) is enabled. Otherwise if the answer is "No", G3 is enabled. Similarly for G5, if the answers for questions Q5, Q6, and Q7 are "Yes", "Dogs", and "Yes" respectively (parsing logic will be handled separately, don't worry about that), then questions in G8 are enabled. In all other combinations (assuming each question is actually answered), G6 is enabled instead.
The problem with this approach is that when questions belong to multiple groups (as with Q10), there's no way of knowing from the code which group is currently being accessed. If the user answers Q10, are they answering from G2 or G8? I'm unable to determine that.
My main question is, can you think of a better way to approach this problem? I've been trying to think of a better solution for several days now and this is unfortunately the best I've come up with.