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
- If the User answers YES to all Questions 1, 2 and 3, then next Group of questions has to be from Group 3.
- If the User answers NO to all Questions 1, 2 and 3, then next Group of questions has to be from Group 2.
- 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.
- 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 :)