Overview
This article describes about an approach to design database for the objects which have sequential workflow in an application.
Description
In most of the business applications, we come across certain items which have a workflow defined in the system. For example, in e-commerce applications, orders have a workflow starting from placing the order till the items getting shipped. The stages or the workflow of the object is defined by the business of the application. Workflows drive the system. So it is very important to have a flexible application to maintain the workflows. In this article, I tried to come up with the database design to manage such workflows.
We will define a simple workflow for orders and walk through the design process.
As the image infers the order workflow begins when a customer orders an item, then it gets processed and finally shipped.
Implementation
Starting with “Orders” table, we can think of columns such as OrderId, CustomerId, CreatedDate, Status.
OrderId – Auto numbering column distinctly identify the orders
CustomerId – Identifies the customer who placed the order
CreatedDate – Date on which order is placed
Status – To maintain the current status of the order such as ordered, processed, shipped
We are now bothered about the “status” column in the “Orders” table which manages the workflow. As a novice developer, I tend to think of storing state of the order directly into the status column (varchar) like ‘ordered’, “processed” and ‘shipped’ which is a bad idea because somewhere we need to hard code the workflow (may be in stored procedure or in front end). For instance, maybe we need to check the current status of an order by using ‘if-loop’ and then hard code the next step.
To overcome it, we can plan to store an integer value in the status column and uniquely identify each state like 1-Ordered, 2-Processed, 3-Shipped. So whenever item moves to next stage current status is increased by 1. Going by first glance, it seems good. But we are again hard coding things, I mean how does a new developer know that 1 is for Ordered, 2 is for Processed and 3 is for Shipped. It is better we maintain another table namely “Status” with “StatusId” (Primary key) and “Name” columns. And we refer “StatusId” column in the “Orders” table as foreign key. Suppose an order which is under processing is having status ’2′ and when it’s ready to ship, the status is incremented by ’1′ and status changes to ’3′. Ok done and all seems to be working well and solution is deployed.
One fine day, client wants to improve the efficiency of the system by introducing an additional state ‘Review’ before processing the order. Now the workflow of the order is changed to Ordered > Review > Processed > Shipped. Assuming that quite a number of orders are already in the database, will the above modification in the workflow fit in the existing database design? Let’s check it.
We can’t re-order the workflow by directly changing the “processed” status to “review” and altering the statusid of “processed” state to 3 and shipped to 4 since some orders already exists with processed or shipped status. Changing likewise will bring all shipped items to processed and processed items to review which isn’t good.
So we consider adding the new “review” status with status id as 4. Now after ordered (statusid = 1) it should be review (statusid = 4) not processed, so the above logic doesn’t work as we are incrementing the statusid each time the order state is changed. And again for that we need to hardcode the workflow in the stored procedure or front end to incorporate the modification.
Database has to be designed in such a manner that any modification like adding a new state or removal of existing state from the workflow can be easily done and shouldn’t impact the existing system. For that we add “NextStatusId” column to the “Status” table. “NextStatusId” column stores the “statusid” of the next state in the workflow. Below are their values for the above defined scenario.
By this database modification, the orders which are already having “Ordered” status will pick next state from “NextStatusId” column and will move to “Review” and from “Review” they move to “Processed” following the modified workflow. Whenever a new state is added or removed the “NextStatusId” ” of the affected states are altered accordingly.
It works well with the business applications which have sequential workflow defined through a certain single path. How about designing the database for the applications which have many alternative flows in each state or having the options to switch from current state to other states. Stay tuned!
I would like to thank my ex-colleague Sumanth Pereji for reviewing the concept.
Please feel free to comment with alternative ideas or criticize the presented one.
First of Thumbs Up Sundeep for such Detailed Content. You’ve come up with cool idea of NextStatusID . No clue how you would implement this in Non Sequential Work Flows. Eagerly waiter for your next post
Hi Pradeep, Thank you very much for taking a look at it
Thanks a lot for this article…
Keep writing ..
Thank you Hiren for your comments.
Nice article!
I will be waiting for the article with many flows in applications. Keep going!
Excellent article, use of simple, easy to understand language.
I have learnt something,
Thanks Sundeep.