One of the foundations of structured data design is Entity-Relationship (E-R) modelling.
A long time ago, when I first saw the E-R diagram of a reasonably complex trading system, I was fascinated by its image of logically interconnected boxes: an impressive “skeleton” where the little box on the bottom right might be affected by the ripple effects of a data change originating somewhere on the distant top left. It gave me a strange impression of power.
With experience, I came to realise that, when human input is involved, the strict implementation of relational integrity can turn out to be more fragile than powerful: it often clashes with reality, making users want to punch the screen.
– Computer: “You cannot add the contract without a Client Code”.
– User: “Are you serious. -Ok, ok… let me add the Client”.
– Computer: “To add a Client you need administrator permissions”.
– User: “(expletive), (expletive)” sound of screen cracking.
This is how you end up with contracts against an unknown “dummy” client or altogether the wrong client. The Software Architect will be smugly satisfied that nothing – technically – is wrong. The Financial Director will reach for the calming pills.
But what if you substituted the dash in “Entity-Relationship” with a comma: “Entities, Relationships”?
The slight change in punctuation is meant to represent a move from a “strict”
bond to a “looser” one, where Entities enjoy full independence until you decide to turn Relationships on or off. Replaying the earlier scenario…:
– Computer: “New Client Code detected. Please go ahead and create the contract”.
The administrator later logs in. She reads a system notification concerning a New Client Code and makes the appropriate adjustments.
In this case, the system “knows” that the Contract and Client Entities are related and warns the user. However, instead of enforcing the Relationship upfront, it “tolerates” the exception whilst keeping track of it so the administrator can rectify it later.
It’s simply a different approach specifically aimed at eliminating “relational fragility”. It does come at a cost: as the system cannot assume data integrity, there is a need to pre-plan and deploy creative reconciliation, query optimisation and warning strategies. The benefit? No more user workarounds which force unplanned reconciliations (riskier, more expensive) and no broken screens 🙂 .
In short: next time you work on the physical implementation of a E-R model to manage structured data, think of the costs and benefits of E,R. Think of your users as humans.
The approach suggested here is informed by experience. Throughout my career in IT, I have come across this many times and, having seen enough horrors caused by strict E-R enforcement, I always favoured pre-emptive solutions of the E,R type.
I must, however, acknowledge the inspiring influence of Nassim Nicholas Taleb‘s insights on antifragility. They vindicate past choices that I would otherwise have described as “common sense”. More importantly, they provide a solid theoretical framework for more deliberate, upfront thinking of truly powerful structured data handling, particularly in scenarios where the user community responsible for managing the data is large and distributed, thus more error prone and difficult to educate.
Beyond the obvious
E,R can do more than mitigating the fragility of strict E-R implementations: it opens the door to non-exclusive, optional relationships. It is also break-up-friendly: relationships made, modified and destroyed with little impact on Entities. Food for thought.