SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    OK, I'll kick it off, why will this not work?

    Code:
    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.

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    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)

  4. #4
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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!

    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.

    Code:
    Question:
    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:
    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
    
    Anwer:
    answer_id, answer
    =======================================
    1, 'You cannot enter'
    2, 'Enter the kiddy version'
    Thanks for your help. It seems pretty clear to me now.

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    other than question_option_id being redundant (natural key: question_id, option) and next_question_id being the pointer, looks like a good design.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    In fact couldn't the table `answer` contain natural keys only...

    answer
    =====
    'You cannot enter'

    And in that case that table becomes redundant, because question_option simply becomes:
    Code:
    Question_Option:
    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?

  7. #7
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Well, good luck with it and let us know the complexities when you feel you can - or if you want the discussion.

  9. #9
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code:
    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?

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  11. #11
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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?

  12. #12
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I haven't really thought this though. but could you not apply bitwise to these questions?

    For instance:-

    PHP Code:
    <?php
    $a 
    1;
    $b 2;
    $c 4;
    $d 8;

    $total 6;
    As the total is 6, you know that they answered questions $b and $c ?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  13. #13
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    I haven't really thought this though. but could you not apply bitwise to these questions?
    That's certainly 1 way to go about it, and that may well feature somewhere in the application flow.

  14. #14
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    Cups, you've opened up a can of worms here!
    Blame it on Ekzu in this post

    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?

  15. #15
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    [thinks] I wonder if there any similar projects on SourceForge.net, not necessarily PHP projects either ...

  16. #16
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    I'd love to help work it out but am embroiled in something else at the very minute - how urgent is this?
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •