I need help on Database design for workflow application.
I have several workflow steps. Each record needs to pass through these steps. But conditionally, the record can move to any one of the workflow step. For example the record can move from step1 --> step2 or from step1 --> step4 based on record type (which is some logic to be executed to determine the record type).
I can have a table WF(master table) which has all the steps.
stepid, stepname, IsConditional
I will have another table to maintain what are the possible steps that a record can go to. So if IsConditional is 1, I will have 2 enteries in wf2approve table corresponditon to that stepid.
How can I determine what will be next step id dynamically when IsConditional is 1.
Is it possible to maintain such information.
Any other solution which provides more flexibility (like I should be able to add more next steps or remove existing next steps for the conditional workflow step)