The Workflow Pattern. Part 2

Using Configuration Tables to Define the Actual Workflow

Jean-Marc Reynaud, Freelance Data Modeler

by
Jean-Marc Reynaud
Freelance Data Modeler

Posted: April 5, 2016

The first part of this series introduced some basic steps for managing the lifecycle of any entity in a database. Our second and final part will show you how to define the actual workflow using additional configuration tables. This is where the user is presented with allowable options each step of the way. We’ll also demonstrate a technique for working around the strict reuse of ‘assemblies’ and ‘sub-assemblies’ in a Bill of Materials structure.

Defining the Options

Part 1 introduced the core workflow tables and how these can easily be incorporated into your database. What we need now is something to guide the user into selecting the next logical state – something that defines a logical workflow.

The diagram below defines all the components of a workflow database model:




Two configuration tables, workflow_state_option and workflow_state_context, have been added to the model. We will start with the options table, which defines the allowable next states. Once its function is understood, we’ll return to the context table and explain the role it plays.

Allowable Next States

The tables that follow are somewhat like a SQL view across our configuration tables. Here we’ve hidden the table joins and we’re just looking at the combinations of type_keys. So let’s consider each STATE.OUTCOME combination and define the options available to the user:


STATE.OUTCOME Combination (from State Hierarchy)State ContextChild DisabledOption 1Option 2
APPLICATION_RECEIVED
.ACCEPTED
STANDARD_JOB
_APPLICATION
NAPPLICATION_REVIEW
APPLICATION_RECEIVED
.REJECTED
STANDARD_JOB
_APPLICATION
NAPPLICATION_CLOSED .NOT_HIRED
APPLICATION_REVIEW
.PASSED
STANDARD_JOB
_APPLICATION
NINVITED_TO_INTERVIEW
APPLICATION_REVIEW
.FAILED
STANDARD_JOB
_APPLICATION
NAPPLICATION_CLOSED .NOT_HIRED
INVITED_TO_INTERVIEW
.ACCEPTED
STANDARD_JOB
_APPLICATION
NINTERVIEW
INVITED_TO_INTERVIEW
.DECLINED
STANDARD_JOB
_APPLICATION
NAPPLICATION_CLOSED .NOT_HIRED
INTERVIEW
.PASSED
STANDARD_JOB
_APPLICATION
NMAKE_OFFERSEEK_REFERENCES
INTERVIEW.FAILEDSTANDARD_JOB
_APPLICATION
NAPPLICATION_CLOSED
INTERVIEW
.CANDIDATE_CANCELLED
STANDARD_JOB
_APPLICATION
NAPPLICATION_CLOSEDINVITED_TO_INTERVIEW
INTERVIEW
.NO_SHOW
STANDARD_JOB
_APPLICATION
NAPPLICATION_CLOSED
MAKE_OFFER.ACCEPTEDSTANDARD_JOB
_APPLICATION
NSEEK_REFERENCES
MAKE_OFFER.DECLINEDSTANDARD_JOB
_APPLICATION
NAPPLICATION_CLOSED
SEEK_REFERENCES
.PASSED
STANDARD_JOB
_APPLICATION
NAPPLICATION_CLOSED .HIRED
SEEK_REFERENCES
.FAILED
STANDARD_JOB
_APPLICATION
NAPPLICATION_CLOSED
APPLICATION_CLOSED
.HIRED
STANDARD_JOB
_APPLICATION
N
APPLICATION_CLOSED
.NOT_HIRED
STANDARD_JOB
_APPLICATION
N


Because we’re ignoring context for now, State Context and Child Disabled? are greyed out. I’ve also limited the number of options in this example to two for the sake of brevity, though in practice there is no limit.

So how does this work?

Imagine that the interview has just been conducted and the interviewer is recording the outcome. The underlying table being updated is managed_entity_state. There are two logical outcomes: PASSED and FAILED. So the current managed_entity_state is updated with the selected outcome (wf_state_type_outcome_id). In the example model, the interviewer can also include some notes about the interview.

If the interviewer selects PASSED, they are presented with two more options: MAKE_OFFER and SEEK_REFERENCES. These are the next states in our workflow. They’re similar to go to statements in programming. For either option, a new row is inserted into managed_entity_state, moving the job application to the next state in the workflow process. The user may set a deadline for this by entering a due_date.

On the other hand, if the interviewer selects FAILED, there is just one option: APPLICATION_CLOSED. So a new managed_entity_state row is inserted using the APPLICATION_CLOSED state (wf_state_type_state_id).

You will see that there are no options available for the APPLICATION_CLOSED state. This signifies that the end of the workflow process has been reached.

The Context Table

Let’s look at the configuration for the technical job application process to see what role the context table plays:


STATE.OUTCOME Combination (from State Hierarchy)State ContextChild DisabledOption 1Option 2
APPLICATION_RECEIVED
.ACCEPTED
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_REVIEW
APPLICATION_RECEIVED
.REJECTED
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_CLOSED
APPLICATION_REVIEW
.PASSED
TECHNICAL_JOB
_APPLICATION
NINVITED_TO
_INTERVIEW
APPLICATION_REVIEW
.FAILED
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_CLOSED
INVITED_TO_INTERVIEW
.ACCEPTED
TECHNICAL_JOB
_APPLICATION
NTEST_APTITUDE
INVITED_TO_INTERVIEW
.DECLINED
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_CLOSED
TEST_APTITUDE
.PASSED
TECHNICAL_JOB
_APPLICATION
NINTERVIEWSEEK
_REFERENCES
TEST_APTITUDE
.FAILED
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_CLOSED
INTERVIEW
.PASSED
TECHNICAL_JOB
_APPLICATION
NMAKE_OFFERSEEK
_REFERENCES
INTERVIEW
.FAILED
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_CLOSED
INTERVIEW
.CANDIDATE_CANCELLED
TECHNICAL_JOB
_APPLICATION
Y--
INTERVIEW
.NO_SHOW
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_CLOSED
INVITED_TO
_INTERVIEW
MAKE_OFFER
.ACCEPTED
TECHNICAL_JOB
_APPLICATION
NSEEK
_REFERENCES
MAKE_OFFER
.DECLINED
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_CLOSED
SEEK_REFERENCES
.PASSED
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_CLOSED.SUCCESS
SEEK_REFERENCES
.FAILED
TECHNICAL_JOB
_APPLICATION
NAPPLICATION
_CLOSED
APPLICATION_CLOSED
.HIRED
TECHNICAL_JOB
_APPLICATION
N
APPLICATION_CLOSED
.NOT_HIRED
TECHNICAL_JOB
_APPLICATION
NINSUFFICIENT
_EXPERIENCE
OVER
_QUALIFIED


Here the context is TECHNICAL_JOB_APPLICATION. Why is this important? Because it allows us to override outcomes. Remember, we previously stated that we can reuse ‘assemblies’ and ‘sub-assemblies’ in a Bill of Materials (BOM) structure. This is useful when we define something once and reuse it, but it can also be too rigid. What if we don’t want to reuse everything?

By inserting workflow_state_context between workflow_state_hierarchy and workflow_state_option, we can both reuse and override outcomes. In this model, we can define whether an outcome is enabled or disabled for different processes.

In the above example, the INTERVIEW.CANDIDATE_CANCELLED combination is disabled. In other words, we’re saying that it’s simply not a permissible outcome for technical job applications. Consequently, the interviewer won’t be able to select it when recording the outcome of a technical job interview because our query only selects options where workflow_state_context.child_disabled = ‘N’.

Because workflow_state_option is not a direct child of workflow_state_hierarchy, we have to define a separate set of options each time a state is used. But this trade-off allows us to finely tune the options for each process.

Qualifying Outcomes

We also have the option of defining more detailed qualifiers for outcomes. There are two ways of doing this:

  1. You can create a fourth level in your BOM to define qualifiers under outcomes in the hierarchy. Due diligence should be taken with this approach. For example, the FAILED outcome is used for different states. Do you want to have the same qualifiers for different FAILED states? Maybe not.
  2. You can define your qualifiers in workflow_state_type but not tie them to any hierarchy; they’re free-standing. You then use workflow_state_option to list the qualifiers for the specific outcome context. This is what the above config shows, where the OVER_QUALIFIED and INSUFFICIENT_EXPERIENCE qualifiers are listed as options for the APPLICATION_CLOSED.NOT_HIRED outcome.

In either case, the application must recognise that a qualifier has been selected rather than a state or an outcome – workflow_level_type will indicate this – and update managed_entity_state.wf_state_type_qual_id with the selected value.

Some Table Configuration Data

You might like to see the underlying configuration data, table by table. Here we have the ids and the type_keys they refer to in parentheses. For the sake of brevity, only values related to the article are presented.


workflow_level_type

idtype_key
1PROCESS
2STATE
3OUTCOME
4QUALIFIER


workflow_state_type

idtype_keyworkflow_level_type_id
1STANDARD_JOB_APPLICATION1 (PROCESS)
2TECHNICAL_APPLICATION1 (PROCESS)
3INTERVIEW2 (STATE)
4PASSED3 (OUTCOME)
5FAILED3 (OUTCOME)
6MAKE_OFFER2 (STATE)
7SEEK_REFERENCES2 (STATE)
8APPLICATION_CLOSED2 (STATE)
9HIRED3 (OUTCOME)
10NOT_HIRED3 (OUTCOME)
11INSUFFICIENT_EXPERIENCE4 (QUALIFIER)
12OVER_QUALIFIED4 (QUALIFIER)


workflow_state_hierarchy

idstate_type_parent_idstate_type_child_id
11 (STANDARD_JOB_APPLICATION)3 (INTERVIEW)
22 (TECHNICAL_JOB_APPLICATION)3 (INTERVIEW)
33 (INTERVIEW)4 (PASSED)
43 (INTERVIEW)5 (FAILED)
51 (STANDARD_JOB_APPLICATION)8 (APPLICATION_CLOSED)
62 (TECHNICAL_JOB_APPLICATION)8 (APPLICATION_CLOSED)
78 (APPLICATION_CLOSED)9 (HIRED)
88 (APPLICATION_CLOSED)10 (NOT_HIRED)


workflow_state_context

idstate_type_idstate_hierarchy_idchild_disabled
11 (STANDARD_JOB_ APPLICATION)3 (INTERVIEW.PASSED)N
21 (STANDARD_JOB_ APPLICATION)4 (INTERVIEW.FAILED)N
31 (STANDARD_JOB_ APPLICATION)7 (APPLICATION_CLOSED. HIRED)N
41 (STANDARD_JOB_ APPLICATION)5 (APPLICATION_CLOSED. NOT_HIRED)N
52 (TECHNICAL_APPLICATION)6 (APPLICATION_CLOSED. NOT_HIRED)N


workflow_state_option

idstate_context_idstate_type_id
11 (STANDARD_JOB_ APPLICATION. INTERVIEW. PASSED)6 (MAKE_OFFER)
21 (STANDARD_JOB_ APPLICATION. INTERVIEW. PASSED)7 (SEEK_REFERENCES)
32 (STANDARD_JOB_ APPLICATION. INTERVIEW. FAILED)8 (APPLICATION_CLOSED)
45 (TECHNICAL_JOB_ APPLICATION. APPLICATION_CLOSED. NOT_HIRED)11 (INSUFFICIENT_EXPERIENCE)
55 (TECHNICAL _JOB_ APPLICATION. APPLICATION_CLOSED. NOT_HIRED)12 (OVER_QUALIFIED)


Clearly, setting this up is quite tricky. It should preferably be administered via an application with a user-friendly interface.

Alternative Sequences

You will note that a number of tables have a column called alt_sequence. This is used to order the list of values for the different selections presented to the user. Typically this will be in descending order based on usage, with the most frequently-used options at the top.

While this article described job applications, the model can be used for many types of workflows where the state of an entity needs to be managed over time. Alternatively, the model can serve as a pattern to customize for your own particular requirements.

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Find out how to identify all the factors of a successful visualization: think like your audience, find the right chart type, and take out the meaning of the data. View course Discover our other courses: