Storing/representing flowchart-style logic in database?

I’m designing a series of flowchart-style questions, where the answer to each question leads the user to another, dependant, question, or to a given answer.

Any ideas on how best to represent this in a database format? I’m trying to weight up the possibly complex development of a generic, standardised representation, and just doing a hack job of hard-coding the logic flow for each set of questions.

OK, I’ll kick it off, why will this not work?

question_id, question, pos_answer_ref, neg_answer
1, 'Are you over 18', 2, 'you cannot enter'
2, 'Are you of a nervous disposition', 3, 'enter the kiddy version'
3, 'Are you more than 100kgs in weight',12,'You are too heavy to ride'
12, 'Have you brought your credit card',13, 'loser'
13, 'Get on and have some fun',0,''

A simple table with questions, the failure answer and the next positive question in the flow.

EDIT: Dont see any reason Cups’ layout wouldn’t work.

Off Topic:

Questions 2 and 3 are… I want to say contrapositives but i’m not sure thats the right term (The ‘negative’ answer is actually Yes)

other than question_option_id being redundant (natural key: question_id, option) and next_question_id being the pointer, looks like a good design.

I should probably have started off by offering some solution of my own. I think sometimes I get too tied up with making things too complicated and need someone to tell me how simple it can be! :slight_smile:

I’ve built on Cups’ answer with a little more complexity, allowing multiple options (rather than yes/no, but I’ve kept the same questions/options as Cups), and a separate list of answers. The question_options will probably have a lot of repetition of answers which could be resolved with another link table, but for the sake of simplicity I’ll keep one.

question_id, question
1, 'Are you over 18'
2, 'Are you of a nervous disposition'
3, 'Are you more than 100kgs in weight'

question_option_id, question_id, option, next_question_id, answer_id
1, 1, 'Yes', 2, NULL
2, 1, 'No', NULL, 1
3, 2, 'Yes', NULL, 2
4, 2, 'No', 3, NULL

answer_id, answer
1, 'You cannot enter'
2, 'Enter the kiddy version'

Thanks for your help. It seems pretty clear to me now.

In fact couldn’t the table answer contain natural keys only…

‘You cannot enter’

And in that case that table becomes redundant, because question_option simply becomes:

question_id, option, next_question_id, answer
1, 'Yes', 2, NULL
1, 'No', NULL, 'You cannot enter'

Which means it would be easier to read, and therefore easier to manage?

Good points, all. Except there’s going to be more stuff associated with an answer than just the text in this example. It’ll probably include relationships to products. That’s why the extra level here.

Well, good luck with it and let us know the complexities when you feel you can - or if you want the discussion.

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_id, question
1, 'Are you a sociable person'
2, 'Are you dependable'
3, 'Do you like a challenge'

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

answer_id, answer
1, 'You must be a Gemini'
2, 'You're a virgo'
3, 'You're definitely a pisces'

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?

Now you are wondering off in the direction I guessed you would be when I first read your title, you get to the stage where it is less of a solely a data-modelling question.

Not sure if this fits your bill, but take a look at a finite state machine, there is a PHP lib in PEAR. FSM.

I only used it on one project, mostly just because I wanted to understand how it worked, but tbh found it a bit of a mind **ck for a long time (just reading the docs and the code) but when I started using it, the penny dropped.

You program in a load of rules into a black box, and sticking with my penny idea, it is like watching a penny in a gumball machine bouncing its way around till something falls out the bottom of it (your penny or a piece of gum).

Maybe not bother with it if you are not into OOP though.

Cups, you’ve opened up a can of worms here!

My project has halted momentarily because I’ve become embroiled in reading up on finite state machines and chains of responsibility and other similar patterns.

Not just on this question, but to apply to a number of problems. I’ve a feeling that somewhere in this bag of snakes is something that’s going to really make my life easier, if I just reach a little deeper…

Though, that said, I don’t think the FSM deals with multiple dependancies between states, does it? ie If the answer to this question is A, AND the answer to the previous question is B, then give this answer?

I haven’t really thought this though. but could you not apply bitwise to these questions?

For instance:-

$a = 1;
$b = 2;
$c = 4;
$d = 8;

$total = 6;

As the total is 6, you know that they answered questions $b and $c ?

That’s certainly 1 way to go about it, and that may well feature somewhere in the application flow.

Blame it on Ekzu in this post :wink:

I felt like that for weeks as I brooded over it, until I put it into practice - probably somewhat for the sake of it.

Though, that said, I don’t think the FSM deals with multiple dependancies between states, does it? ie If the answer to this question is A, AND the answer to the previous question is B, then give this answer?

else return ‘SUCCESS’?

I still think you can leave the structure in your database, I mean you want to because it has to live somewhere to be adjusted and so on, but somehow using an FSM to help with the filtering and decision making process.

I’d love to help work it out but am embroiled in something else at the very minute - how urgent is this?

[thinks] I wonder if there any similar projects on, not necessarily PHP projects either …

It’s only as urgent as everything else on my desk. The LARGER issues - that’s something I’m going to have to think about. First I need to figure out what it is I’m confused about, then I’ll post back!