How would you design a table to store Configuration or a Setup in the following Questionnaire scenario?

Question

I have a table named Questionnaire which will contain binary YES/NO type of questions with following columns.

Id INT	Question VARCHAR	GroupId INT
1	Are you a female?	1
2	Are you healthy?	1
3	Are you happy?	1
4	Do you live in California?	2
5	Are you 21+?	2
6	Do you speak Spanish?	3

... and so on.

Questions will be asked to the user group wise. i.e Questions with GroupId 1 will be asked first and based on the answers given by the user, the next group will be determined.

For example: In the above table, Group 1 has 3 questions. So, various possibilities after answering all the questions from Group1 are

1. If the User answers YES to all Questions 1, 2 and 3, then next Group of questions has to be from Group 3.
2. If the User answers NO to all Questions 1, 2 and 3, then next Group of questions has to be from Group 2.
3. If the User answers YES to Question 1, and NO to Question 2, 3, then next Group of questions has to be from Group 2.
4. If the User answers YES to Question 1 and 2. NO to Question 3, then next Group of questions has to be from Group 6. ....and so on.

As number of questions in the group here are 3, there could be 9 different possibilities. In general, if there are n questions in a Group, there could be 2^n different possible outcomes.

I am trying not to hard code this logic of Questionnaire and Group in the Application side as there is a requirement that the set of questions and their outcomes will be changed very often. Hence, I am trying to design a table which will store this logic, any inputs will be much appreciated. Thanks :)

Answer 1

Option 1 (Group focused)

If you're set on having having groups, you're going to need a table called Group to control the flow to next question groups, based on answers to questions within the current group. You would then want to create a foreign key between Group.ID and Questionaire.GroupID to ensure each Questionnaire is tied to a valid group.

Group Table

ID INT	NextGroupIfTrue INT	NextGroupIfFalse INT
1	2	3
2	3	4
3	5	6

Try to make your group logic as simple as possible. For example, an answer of "yes" to any question in the current group triggers group 'x' as the next group, otherwise go to group 'y'.

Your logic (either in a stored procedure or application code) could then be to check if a condition exists within the answers to the questions of the current group, if condition is found to be 'true' follow the next group branch for true, otherwise follow the false branch.

Option 2 (Question focused)

Ditch the group concept and control next question flow at the question level. Take the same concept as mentioned above in the Group scenario, but list a next question branch for each question directly in the Questionnaire table. For example, if the answer to question 1 is True, go to question 2, else go to question 4.

Summary

In either case, you're going to want to be able to make a simple boolean decision at either the question or group level, and avoid mismatching the two. Otherwise you're going to need to look into using some form of dynamic SQL generation at each decision is needed, which means you'll be maintaining the underlying code every time a question or group is added or removed.