So I’ve made myself another hurdle to stumble over: In many cases question 2 (for example) will be asked regardless of the answer for question 1. The final answer is dependant on the combination of answers to questions 1 and 2.
This can be achieved using the solution above, but it would mean entering question 2, and it’s options, as next_questions for all of the options for question 1 - a lot of repetition.
The solution I’ve come up with is a table to link an answer to all of the answers which must be positive in order to reach that answer.
Question:
question_id, question
=======================================
1, 'Are you a sociable person'
2, 'Are you dependable'
3, 'Do you like a challenge'
...
Question_Option:
question_option_id, question_id, option, next_question_id
=======================================
1, 1, 'Yes', 2
2, 1, 'No', 2
3, 2, 'Yes', 3
4, 2, 'No', 3
5, 3, 'Yes', NULL
6, 3, 'No', NULL
Anwer:
answer_id, answer
=======================================
1, 'You must be a Gemini'
2, 'You're a virgo'
3, 'You're definitely a pisces'
Answer_Question_Option:
answer_id, question_option_id
========================================
1, 1
1, 3
2, 1
2, 4
2, 5
3, 2
3, 3
3, 6
So after each question is answered, the application would check whether there are enough appropriate answers given to give an answer (need to work on my terminology, though).
Using this method, question_id becomes useful, unless I were to use question_id and option in Answer_Question_Option.
I guess this breaks away from the flowchart-style process I at first envisaged.
Too complicated? Or just enough? Any tweaks?