Custom Workflow Architecture: Workflow Steps and Transitions

!!ROUGH DRAFT!!

This post expands on a previous post: Custom Workflow Architecture: The Routing Table

In my previous post we looked at a simple one step approval workflow and talked about how to store workflow activity to a routing table. In this post we'll look at one possible way to define a workflow's steps and transitions in a set of database tables, and how a workflow engine might utilize that information for use in a web application.

In designing our new system, we'll continue using the simple one step approval workflow at first, but I know that we will want to support more than just that one workflow in our new system. So first I'll define a table in the database to identify unique workflows available in our web application:

--- Create table Workflow
CREATE TABLE Workflow (
ID INT NOT NULL,
Name VARCHAR2(128) NULL,
Description VARCHAR2(2000) NULL
);
--- Set column ID to be Primary Key for table Workflow
ALTER TABLE Workflow ADD CONSTRAINT Workflow_PK1 PRIMARY KEY (ID);

Keeping things simple, this table tags each available workflow with a unique id (Workflow.ID). In addition to the unique id, we can also assign a human readable name and description to help us identify the workflow. Lets insert a new record that will define the one step workflow we defined in the previous post (we will recreate that workflow from scratch in this example):

INSERT INTO Workflow (
ID,
Name,
Description
)VALUES(
1,
'Vacation Request Workflow',
'A workflow to allow employees to request vacation time.'
);
COMMIT;

Now that we have a workflow record in the system, we can go about creating a list of workflow steps (sometimes called Workflow States) we want to display in our application. We'll create a new table to hold our steps and link them back to the workflow they belong to.

--- Create table WorkflowStep
CREATE TABLE WorkflowStep (
ID INT NOT NULL,
WorkflowID INT NOT NULL,
Name VARCHAR2(128) NULL,
Description VARCHAR2(2000) NULL
);
--- Set column ID to be Primary Key for table WorkflowStep
ALTER TABLE WorkflowStep ADD CONSTRAINT WorkflowStep_PK1 PRIMARY KEY (ID);
--- Set foreign key pointing back to the Workflow table
ALTER TABLE WorkflowStep ADD CONSTRAINT WorkflowStep_FK1 FOREIGN KEY (WorkflowID) REFERENCES Workflow(ID);

Now that we have a table to hold the steps, we need to define what steps exist in our workflow. Even though we are calling this a one step approval workflow, there are really at least 2 steps involved - When the employee is working on his request, thats the first step of the workflow. Then, when the manager works on the request, after it's been submitted for approval by the Employee, thats the second step.

In addition, I'm going to add two extra steps - a step to represent the start of the workflow and another to represent the end (as shown in the flowchart diagram). Start and end point steps are not steps that users get to see, or act on, but it allows our application to see workflow instances from start to finish.

So lets go ahead and define all 4 of those steps in our new table with a few insert statements:

INSERT INTO WorkflowStep (
ID,
WorkflowID,
Name,
Description
)VALUES(
1,
1,
'Workflow Instance Instantiated',
'This step marks the point where a workflow instance was created.'
);

INSERT INTO WorkflowStep (
ID,
WorkflowID,
Name,
Description
)VALUES(
2,
1,
'Request Creation/Submission',
'A vacation request is created and submitted for approval at this step.'
);

INSERT INTO WorkflowStep (
ID,
WorkflowID,
Name,
Description
)VALUES(
3,
1,
'Manager Review/Approval',
'The vacation request is reviewed and approved/rejected by a manager at this step.'
);

INSERT INTO WorkflowStep (
ID,
WorkflowID,
Name,
Description
)VALUES(
4,
1,
'Workflow Completed',
'The vacation request was finalized (Either approved or rejected).'
);

COMMIT;

Notice I havent defined any way to order these steps yet >_> We'll do that now using Transitions!

If we look at the example workflow flowchart, we can see that steps are represented as squares. The transitions are the lines with arrows that connect steps in the workflow. Transitions define how you could get from one step to another.

In our example workflow the transitions are pretty simple. We've got at least 3 transitions to get us started:

1. A Transition from "Instantiate Workflow Instance" to "Request Creation/Submission", which happens when a user creates a new Vacation request. We'll call this transition "Instantiate Workflow"
2. A Transition from "Request Creation/Submission" to "Manager Review/Approval", which fires off when the employee submits his vacation request for review by a manager. We'll call this transition "Submit For Manager Approval"
3. A final Transition from "Manager Review/Approval" to "Workflow Completed". We'll call this "Approved by Manager"

Lets slap together a table to store information about all the available transitions in the system:

--- Create table WorkflowTrans
CREATE TABLE WorkflowTrans (
ID INT NOT NULL,
WorkflowID INT NOT NULL,
FromStepID INT NOT NULL,
ToStepID INT NOT NULL,
Name VARCHAR2(128) NULL,
Description VARCHAR2(2000) NULL
);
--- Set column ID to be Primary Key for table WorkflowTrans
ALTER TABLE WorkflowTrans ADD CONSTRAINT WorkflowTrans_PK1 PRIMARY KEY (ID);
--- Set foreign key pointing back to the Workflow table
ALTER TABLE WorkflowTrans ADD CONSTRAINT WorkflowTrans_FK1 FOREIGN KEY (WorkflowID) REFERENCES Workflow(ID);
--- Set foreign key pointing back to the starting WorkflowStep record for this trans
ALTER TABLE WorkflowTrans ADD CONSTRAINT WorkflowTrans_FK2 FOREIGN KEY (FromStepID) REFERENCES WorkflowStep(ID);
--- Set foreign key pointing back to the ending WorkflowStep record for this trans
ALTER TABLE WorkflowTrans ADD CONSTRAINT WorkflowTrans_FK3 FOREIGN KEY (ToStepID) REFERENCES WorkflowStep(ID);

Since a transition links one step to another, we need to link our transition to both the staring step and ending step. Working with this table relationship, we can setup transitions to every possible step and back if we wanted to...for now, we'll stick to the 3 transitions we've already defined above. Here are the insert statements for them:

INSERT INTO WorkflowTrans (
ID,
WorkflowID,
FromStepID,
ToStepID,
Name,
Description
)VALUES(
1,
1,
1,
2,
'Instantiate Workflow',
'The initial transition that fires off for every new workflow.'
);

INSERT INTO WorkflowTrans (
ID,
WorkflowID,
FromStepID,
ToStepID,
Name,
Description
)VALUES(
2,
1,
2,
3,
'Submit For Manager Approval',
'Submit the request for approval.'
);

INSERT INTO WorkflowTrans (
ID,
WorkflowID,
FromStepID,
ToStepID,
Name,
Description
)VALUES(
3,
1,
3,
4,
'Approved by Manager',
'Approve Request.'
);

COMMIT;

That's enough to get us started. With all the above SQL run, you should have a table layout that looks something like this in your DB:

In the next article in the series, Custom Workflow Architecture: Examining Workflow Data , I'll take a closer look at the workflow we've defined here to see how we might generate some meaningful SQL queries to generate lists of steps and transitions that will be the basis of our workflow application.