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.
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?
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.