Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2014

    Question Design with recursive steps

    I'm building a tool that moves step by step through processes and creates notes for the user, and I want so save my processes to a database, as there's a TON. This is my first crack at actually creating a database.

    I keep running into the same questions, but first I should better describe my tool.

    Each process, pulled by an external ID (which I plan to make the same ID in my database) gets displayed with a title. I have built a wizard-like stepping tool that moves through each top level step, presents options for process flow (if available), and presents the user a comprehensive summary of his actions on the final step. Right now, it's fully functional with hard-coded HTML, but I want to be able to build it dynamically with the processes in the DB.

    • Each process, pulled by ID, has 1-100 steps, title, and a code for viewing permissions
    • Each step has 1-20 substeps, 0-10 options, title, notes (for the summary), and isFinal Boolean for checking if it's the terminating step in any process. (unless it's another process, then it follows process notation)
    • Each substep has 1-20 substeps, but these are a simple string.
    • Each option has destination step (where the wizard goes if selected), notes if that option is selected, isCheck (checkbox or part of select/option) and label

    • Each step can be (and most likely is) in many processes.
    • A step can be another process.
    • Each substep should only belong to one step.
    • Options are harder. I'm making these able to be pulled into many steps, but always the same destination step, until I get more data.

    My questions:
    1. Should steps and substeps be in the same table or separate tables? Substeps have less data than steps, but if they are separate, how do I differentiate if the parent step is a step or a substep? Or is there another method I should be looking into?
    2. How do I efficiently recursively pull every step and substep for a process? (also pulling in other processes, if steps are full processes)

    My first reaction is building a separate table
    (int) parentid FK, (varchar) parenttable, (int) substepid FK
    and handling it all in PHP (assigning what table and the recursion), but I'd prefer not having that many calls to the database.

  2. #2
    Join Date
    Jul 2012

    I have drafted a Data Model for you

    on this page of my Database Answers Web Site :-

    Pls take a look at the Rules on this page and correct and extend them as appropriate :-

    Regarding your question about steps and substeps - as you can see they should both be in the same table using a recursive/self-referencing 'Rabbits Ears' relationship.

    You also ask about 'efficiently pull every step' - I recommend defining the logical Data Model first and using it as a statement of the requirement - then develop a physical implementation separately.


    Barry in London

Tags for this Thread

Posting Permissions

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