We are working on an application which will ask users various questions, these questions can have branches, so they don't run in a straight order. for example, if the below is a structure of questions and where they can flow to:
Question 2 has a branch if the user answers Yes we move to 3 and end at 7, otherwise we work our way down 4,5,6,7. I have come up with a table design but wanted to get feedback if the design will work or if there is a better way to structure this.
There will be a Question table which will have Id (Key) and Text columns. The tree itself will be represented as:
QuestionId | PreviousId | NextId | Branch Condition 1 0 2 NULL 2 1 3 Yes 2 1 4 No 3 2 7 NULL 4 2 5 NULL 5 4 6 NULL 7 3 0 NULL 7 6 0 NULL
the way it would work, once a question is answered the system would check what the current question's branch condition is, if null just move to the nextId. otherwise find the answer the user selected and go to the associated nextId. we only expect Y/N questions to branch.
the previousId I've kept for now, in case the users would like the ability to go back and retake a question, currently they only want to move forward.