How to persist branching logic into database?

Question

We are building a survey engine for our internal use. I would like to know how to persist the question branching logic into the database? Any body done this before or any ideas on the schema for the database?

If the user responses with an answer, we need to skip to the next questions based on the logic added to the questions Each question can have multiple logic added to it.

For eg:

Question: Is it Sunny, Raining or Cloudy?
Answer: Raining.
The next question should be based on the previous answer.
if(Raining)
{
}

if(Sunny)
{
}

if(Cloudy)
{
}

how do I persist the above to the database and go from there?

Any bright ideas ?

Partheniaparthenocarpy

should the logic be stored in the db? maybe just store the data for the questions and answers then in the application have the logic that says what the database should have. – Kowalczyk

yes, we need to store the logic in the db. – Partheniaparthenocarpy

Answer 1

You're essentially looking to persist a decision tree into a database. You'd want to store each question as a node and, in the interests of a normalized database, store the edges in a separate table relating questions that depend on other questions (directed edges), and walk as appropriate.

Edit: A simple design can be two tables: Questions and Edges. Questions just has id and question text. Edges can be answered_question_id, next_question_id, and answer. The first table is self-explanatory. The second table lists that if a question answered_question_id is asked and answered with something that either equals to or matches answer, forward to question next_question_id next.

Revivify

Hello Yan, thanks for your input. Can you please provide a sample schema along with it. – Partheniaparthenocarpy

Hello Yan, Thanks again for your input. I will try it out. – Partheniaparthenocarpy