Table design for questionaire

Question

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.

Answer 1

If it's only yes/no questions that may have different next question, I would suggest adding 2 columns to the questions table that specify next question id when the answer is Yes (or default, when it's not a yes/no question) and next question id when the answer is No. keeping the previous answer id seems to me to be redundant, since you can easily keep in memory the entire questions path in the application itself.

However, if sometime in the future you are going to also have multiple choice questions, and the next question will depend on the answer (i.e category and sub categories), then I suggest you add an answers table as well, and keep a column for next question id both in the questions table and in the answers table.
If the next question id is not dependent on the answer, then keep it in the questions table. If it is dependent on the answer, then put null in the next question id column of the questions table, and keep the next question id in the answers table. This will provide you maximum flexibility and a simple enough data structure.