Enforce the Mutually Exclusive

How to implement status and other mutually exclusive data items correctly

What is it?

It is the rigorous enforcement of values which must logically be mutually exclusive to ensure that there can only ever be one, unambiguous value at any one time.

How does it work?

In many business processes, we are tracking a process of some kind through multiple stages. Be it a complaints case, a house sale or the journey of an account into collections or any one of a thousand other business processes. In each case, we need to know what the status of that case is to know what we need to do next to see it through successfully.

Usually, the value we hold for the case status is mutually exclusive with other possible values for the same status - a sale cannot be both in progress and completed, a customer cannot be both dead and alive and an account cannot simultaneously be open and closed for example.

This logical mutual exclusivity needs to be considered and enforced in your IT system.

As with all things, there are many different ways to do this, some, much better than others.

Lets explore this by following an example of a complaint case. In the example, I’m going to use a business process that allows a complaint case to exist in one of the following status (your complaint tracking process may have more or fewer status than this but this is not important for the example).

Allowable values for complaint status:

· Complaint Received

· Under Investigation

· Escalated

· Resolution Proposed

· Closed

· Appealed

Before we discuss the system design, we need to consider the full requirement around the status, it is rarely enough in most business processes to only know the current state, we usually have to be able to know when the status changed and hold the history of the status for MI, business improvement and audit purposes. This audit trail becomes critical if a case is ever appealed to an industry ombudsman or should end up in court even.

This need to track history can influence the underlying system design and two example designs are below. Sticking with our complaints case for illustration purposes, both the designs below represent the same complaint (Complaint 1).

While both of these designs seem to deliver to the requirements, my view is they both exhibit similar weaknesses. Let’s take a closer look by throwing a few issues at these structures…

Clarity

Lets consider that our complaints process calls for all complaints that are not closed within ten days to be automatically escalated to a head office team.

Let’s consider that with complaint 2, a resolution has been proposed to the customer on day 9 and the customer hasn’t yet responded. In this case, the escalation is made. This would result in the following data:-

What is the status of the complaint on the 11th Jan?

Is it in resolution proposed or escalated?

What is the correct next step?

Rules can be documented and created to determine what should be displayed as the case status of course but if this is determined from the structures shown then there is much more to consider.

We need to consider that most systems share data with other systems ‘downstream’ – maybe we don’t charge interest on a customers account or we stop other actions such as while a complaint is in progress such as chasing up on a debt owed. We’ll almost certainly be using the status somewhere in our MI. The fact is that any consumers of this data now also need to derive this status based on the same logic and anything other than an identical implementation will result in a different understanding of the status.

Even if this is done, we have generated a further structural issue for the business…if you ever change the way the complaints process works, we now need to change everywhere these rules are implemented thus increasing the change cost and how long a change will take. Further, each system that needs to be changed likely has its own change frequency and set of priority changes to be made so it becomes very likely that not all systems will be updated to the new logic at the same time.

In the worst case scenario, in large organisations where systems have been in place for some time, I have found that data can be being used downstream without the owners of the source system even being aware (more on how to control for this in another article later – I’ll add the link in here once I’ve written it). In those cases, when you make a change in the complaints system those downstream uses of the data in your organisation continue to use the data in the ‘old’ way without any knowledge in some departments that they are working off incorrect data. This is the worst type of failure – a silent one - as it is the sort that can go unnoticed for a significant time and allow large scale issues to build up. These failures can result in costly, large-scale remediation projects that attract regulator and media interest.

My final note on this is that all of these issues are possible while everything is operating normally – without any form of IT failure or outage. We haven’t even started to consider what would happen if we introduce that. Consider for a moment how much more troublesome this can get when one of these status updates is delayed for some reason - say it’s coming in from another system and the file can’t be processed for a couple of days. This could play havoc then with the dates recorded and the status recorded – never mind what other downstream systems would make of that late change in data.

A Better Way

The key to getting this right is to ensure that the system allows for a single status to be set on a case. I need to be specific here; I don’t just mean the introduction of a drop down in the system for status or something. I mean this must be implemented at the database level in a way that the system simply doesn’t have the ability to store more than one value.

You can do this by creating a “Current Status” field in the database that is set directly by all sources and it can only possibly hold one value. This forces absolutely a single status to be held. All systems downstream then take this status and No further processing is allowed and if there are any issues, the source system is updated with the new rules and, yes, all downstream systems take that new status.

But what about the requirement to store the history I hear you say? No problem, we still have a history table but this history table is not in the master – it is the supplementary data…we actually add to and amend the history table only when we have a change in the current status – it reflects changes to the current status field and nothing else.

My suggestion would look something like this in the database:

It is important to note here that the history table is updated based on when the complaint headline table is updated (not the complaint headline being derived from looking at the history table). This is vitally important.

What this means is that the current status of the complaint can only be held in the Current Status field in the Complaint Headline table and, since there is only one place, one box, into which we can put that status and only enough space in that box to hold one status, that only one status can ever be held at a time.

All downstream systems and processes must also use the current status field (unless for some reason the need the history). If you so desire you could further enforce this by applying a different level of security over the history information.

In an alternative to the above, a reasonable retrofit solution if you already have the history table in a structure one of the two aforementioned ones is to create the “Current Status” field and have this populated within the database of the complaints system. Use whatever rules and logic are required to drive this status field and use this everywhere that status needs to be presented - including in downstream systems. While I think this is not as good as it can result in issues with having to update the logic, this solves the majority of the issue without having to change all the updates and rules around populating your current history tables.

Final Note

Ultimately, the thing you need to achieve to get this right is to ensure that, when you have values for something which are mutually exclusive, ensure that you only store this information in one place and to do this by enforcing that at the database level, not through some system logic.

First Published 31st January 2021

All views expressed in this article are solely those of the author

© Alex Cowhig 2021