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.

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:

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:

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

workflow_state_type

workflow_state_hierarchy

workflow_state_context

workflow_state_option

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.