Reference tables don't belong in the database!
Now I should admit that over half of the tables in my database violate that statement, so I guess I have a lot of work ahead of me if I want to comply with my new resolution – even to justify it; but I do believe there is a better approach – if only achievable in future development efforts.
To clarify, the content of these exiled tables rarely, if ever, change by any other means than direct statements by a member of IT; I don't mean transactional tables that are updated by procedural system logic. Germane to my point, they essentially enumerate references to objects that do not reside in the database - often not even within the organization’s domain – like countries, titles (Mr., Mrs, Miss), software configurations, status codes, etc.
For example, a title (Mr., Mrs.) belongs to the global domain ontology. It is not data, rather a concept to which we refer when capturing data about customers, users, vendors, etc. Yet we store our own versions so we can all play games when our systems need to integrate – I’m sorry, I don’t have an ‘Officer’, go fish. “Monsieur?”.. Je regrette, je ne parle pas francais. I digress.
Likewise, a status code seems fairly innocuous, until your trading partner receives it on an excel report or via an integration and then has to create their own local mapping that will gradually erode as each system evolves, adding – quite irresponsibly, I might add - to each partner’s technical debt. Even within our own organizations, we shouldn’t assume that our databases are accessible to all systems; there are indeed cases similar to this among integrated proprietary systems.
Since these references are pointing to objects outside the database realm anyway, I’m simply suggesting that we skip the middle man and point directly to the object with a URI (uniform resource identifier). It’s not a revolutionary concept, the ever-burgeoning semantic web movement has been pushing this agenda for years. Its just an adaptation that seems to make sense. That foreign key column in your ‘Customer’ table that references your ‘Title’ table becomes a simple varchar that contains a URI that can be scaled universally so that all consumers reference the same concept as opposed to an address within an isolated database.
The URI does not necessarily have to be dereferencable; if it were to follow follow convention, it could be human readable. Rather than an arbitrary status code, I might return “http://company.org/applicationx/status/pending.” It’s a bit verbose, but at least my sales rep would know what it means…. and developers could avoid the join when looking at data in the database. I could also format the URI as a RESTful URL where integrated applications could get a localized representation directly from the source, minimizing synchronization concerns. Most important, the practice will encourage consumers of data to anticipate change that was inevitable to begin with, rather than to assume that their will be some formal change process that never succeeds at anything but inhibiting change.
Imagine a trading partner who, or even an internal system that gives you direct access to all of its system codes and reference lists via RESTful url, advising that they are subject to some nature of change without warning. No formal change process that won’t work anyway, and as long as you account for the possibility of change, the integration maintains lubricated. And dealing with the change is not that complicated. You anticipate connection problems like a DNS – cache the known and slap on a TTL (time to live). Build generic processes to handle additions for those elements that drive process. And by all means, store the URI as is so you can handle cases when the partner no longer offers values that you have already consumed.
My initiative had less momentum a couple of years ago, back when virtualization was a Citrix desktop and “the cloud” was nothing more than a Visio stencil used to represent stuff beyond our comprehension. Oh, wait! It’s still used for that. But we’re far from that comfort, well into the days when the surface area of our systems extend far beyond the warmth and safety of our own domain, and the location of our data and executables is abstracted from us by virtual servers in private clouds. Identifying our resources in a uniform way that transcends localized addressing schemes seems a logical cooperation.
Subscribe