This article covers how to design the database for the application that involves complex non-sequential workflows. This is a follow up post to my first article written on designing sequential workflow database for business applications.
In this post, I chose simple task workflow that consists of three states – Ready, In Progress and Completed. As shown in the figure below, the task can be moved from Ready to In Progress or directly to Completed state.
Before going into the database design, I would like to give brief idea about the approach. Considering that application might have many workflows, we will need a master Workflow table to have store all the workflows. Each workflow can have multiple states, so we require a table to store the states and it has one to many relationship with the Workflow table. Next, these states must be linked together.
We need a table to store the navigation paths for each state in the workflow. For ex: Ready state can have two navigation paths – In Progress and Completed.
Finally, tasks table will hold all tasks in the application along with the current workflow state and WorkflowNavigation table helps to navigate to the next state.
This design is flexible enough to accommodate any changes to the workflow. For ex: if new state needs to be added to the workflow, configure navigation in WorkflowNavigation table by inserting records with possible states that it can navigate to and also add records for the existing states to link to the new state. This way new or existing tasks can use the new state based on their current state.
And same applies to deletion of an existing state as well. But I would advise not to delete the workflow state directly since it would impact the tasks that have it as their current state. Instead use IsActive column in the WorkflowStates table to identify the states workflow supports.
As you might have realized, there is a new table, TaskStateHistory, added to the design. It allows the task to go to previous states by storing the history of the task as shown in the figure below. To go to previous state, select the latest (using TOP operator) workflow state using the CreatedDate column.
Also, consider adding columns such as CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, etc in tables as needed for audit purpose.
This article is not as descriptive as my first one since I just wanted to focus only on the subject. I would recommend reading the first one to get a little background.
Please do let me know your feedback or comments on the presented approach.