Using Configuration Tables to Define the Actual Workflow

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.

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 Context Child Disabled Option 1 Option 2
APPLICATION_RECEIVED.ACCEPTED STANDARD_JOB_APPLICATION N APPLICATION_REVIEW
APPLICATION_RECEIVED.REJECTED STANDARD_JOB_APPLICATION N APPLICATION_CLOSED
.NOT_HIRED
APPLICATION_REVIEW.PASSED STANDARD_JOB_APPLICATION N INVITED_TO_INTERVIEW
APPLICATION_REVIEW.FAILED STANDARD_JOB_APPLICATION N APPLICATION_CLOSED
.NOT_HIRED
INVITED_TO_INTERVIEW.ACCEPTED STANDARD_JOB_APPLICATION N INTERVIEW
INVITED_TO_INTERVIEW.DECLINED STANDARD_JOB_APPLICATION N APPLICATION_CLOSED
.NOT_HIRED
INTERVIEW.PASSED STANDARD_JOB_APPLICATION N MAKE_OFFER SEEK_REFERENCES
INTERVIEW.FAILED STANDARD_JOB_APPLICATION N APPLICATION_CLOSED
INTERVIEW.CANDIDATE_CANCELLED STANDARD_JOB_APPLICATION N APPLICATION_CLOSED INVITED_TO_INTERVIEW
INTERVIEW.NO_SHOW STANDARD_JOB_APPLICATION N APPLICATION_CLOSED
MAKE_OFFER.ACCEPTED STANDARD_JOB_APPLICATION N SEEK_REFERENCES
MAKE_OFFER.DECLINED STANDARD_JOB_APPLICATION N APPLICATION_CLOSED
SEEK_REFERENCES.PASSED STANDARD_JOB_APPLICATION N APPLICATION_CLOSED
.HIRED
SEEK_REFERENCES.FAILED STANDARD_JOB_APPLICATION N APPLICATION_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 Context Child Disabled Option 1 Option 2
APPLICATION_RECEIVED.ACCEPTED TECHNICAL_JOB_APPLICATION N APPLICATION_REVIEW
APPLICATION_RECEIVED.REJECTED TECHNICAL_JOB_APPLICATION N APPLICATION_CLOSED
APPLICATION_REVIEW.PASSED TECHNICAL_JOB_APPLICATION N INVITED_TO_INTERVIEW
APPLICATION_REVIEW.FAILED TECHNICAL_JOB_APPLICATION N APPLICATION_CLOSED
INVITED_TO_INTERVIEW.ACCEPTED TECHNICAL_JOB_APPLICATION N TEST_APTITUDE
INVITED_TO_INTERVIEW.DECLINED TECHNICAL_JOB_APPLICATION N APPLICATION_CLOSED
TEST_APTITUDE.PASSED TECHNICAL_JOB_APPLICATION N INTERVIEW SEEK_REFERENCES
TEST_APTITUDE.FAILED TECHNICAL_JOB_APPLICATION N APPLICATION_CLOSED
INTERVIEW.PASSED TECHNICAL_JOB_APPLICATION N MAKE_OFFER SEEK_REFERENCES
INTERVIEW.FAILED TECHNICAL_JOB_APPLICATION N APPLICATION_CLOSED
INTERVIEW.CANDIDATE_CANCELLED TECHNICAL_JOB_APPLICATION Y
INTERVIEW.NO_SHOW TECHNICAL_JOB_APPLICATION N APPLICATION_CLOSED INVITED_TO_INTERVIEW
MAKE_OFFER.ACCEPTED TECHNICAL_JOB_APPLICATION N SEEK_REFERENCES
MAKE_OFFER.DECLINED TECHNICAL_JOB_APPLICATION N APPLICATION_CLOSED
SEEK_REFERENCES.PASSED TECHNICAL_JOB_APPLICATION N APPLICATION_CLOSED.SUCCESS
SEEK_REFERENCES.FAILED TECHNICAL_JOB_APPLICATION N APPLICATION_CLOSED
APPLICATION_CLOSED.HIRED TECHNICAL_JOB_APPLICATION N
APPLICATION_CLOSED.NOT_HIRED TECHNICAL_JOB_APPLICATION N INSUFFICIENT_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

id type_key
1 PROCESS
2 STATE
3 OUTCOME
4 QUALIFIER

workflow_state_type

id type_key workflow_level_type_id
1 STANDARD_JOB_APPLICATION 1 (PROCESS)
2 TECHNICAL_APPLICATION 1 (PROCESS)
3 INTERVIEW 2 (STATE)
4 PASSED 3 (OUTCOME)
5 FAILED 3 (OUTCOME)
6 MAKE_OFFER 2 (STATE)
7 SEEK_REFERENCES 2 (STATE)
8 APPLICATION_CLOSED 2 (STATE)
9 HIRED 3 (OUTCOME)
10 NOT_HIRED 3 (OUTCOME)
11 INSUFFICIENT_EXPERIENCE 4 (QUALIFIER)
12 OVER_QUALIFIED 4 (QUALIFIER)

workflow_state_hierarchy

id state_type_parent_id state_type_child_id
1 1 (STANDARD_JOB_APPLICATION) 3 (INTERVIEW)
2 2 (TECHNICAL_JOB_APPLICATION) 3 (INTERVIEW)
3 3 (INTERVIEW) 4 (PASSED)
4 3 (INTERVIEW) 5 (FAILED)
5 1 (STANDARD_JOB_APPLICATION) 8 (APPLICATION_CLOSED)
6 2 (TECHNICAL_JOB_APPLICATION) 8 (APPLICATION_CLOSED)
7 8 (APPLICATION_CLOSED) 9 (HIRED)
8 8 (APPLICATION_CLOSED) 10 (NOT_HIRED)

workflow_state_context

id state_type_id state_hierarchy_id child_disabled
1 1 (STANDARD_JOB_ APPLICATION) 3 (INTERVIEW.PASSED) N
2 1 (STANDARD_JOB_ APPLICATION) 4 (INTERVIEW.FAILED) N
3 1 (STANDARD_JOB_ APPLICATION) 7 (APPLICATION_CLOSED. HIRED) N
4 1 (STANDARD_JOB_ APPLICATION) 5 (APPLICATION_CLOSED. NOT_HIRED) N
5 2 (TECHNICAL_APPLICATION) 6 (APPLICATION_CLOSED. NOT_HIRED) N

workflow_state_option

id state_context_id state_type_id
1 1 (STANDARD_JOB_ APPLICATION. INTERVIEW. PASSED) 6 (MAKE_OFFER)
2 1 (STANDARD_JOB_ APPLICATION. INTERVIEW. PASSED) 7 (SEEK_REFERENCES)
3 2 (STANDARD_JOB_ APPLICATION. INTERVIEW. FAILED) 8 (APPLICATION_CLOSED)
4 5 (TECHNICAL_JOB_ APPLICATION. APPLICATION_CLOSED. NOT_HIRED) 11 (INSUFFICIENT_EXPERIENCE)
5 5 (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.

Tools in this post

Redgate Data Modeler

Design, update, and manage database schemas

Find out more