Blind Keys & Stuff

I got into an interesting discussion with a colleage today about building data access components, and passing arguments, often blind/surrogate keys.

I'll quickly state a point about blind/surrogate keys: They are good, I like them, I wish every DBA used them universally on every table. I much prefer developing software against that model and when I'm the data architect on a project, I do it that way. But having said that, for building frameworks and design patterns, you can't assume that you'll always have them.

So blind keys are called just that because they are blind to the user. The user doesn't type in some long integer or guid to load up some data on the form. Almost by definition though I'd maintain that if you have a blind key, you must have a visible key, that is, some combination of data that a user can use to uniquely identify an entity. We call these “Alternate Keys“ and they are ususally implemented in addition to blind keys as a unique index or constraint on your database.

Why do we use blind keys? We do it so that we can be more durable in the face of changing specifications, business rules - and perhaps originally because of poorly understood requirements. When a primary key changes (like an additional column added) it typically has the affect of trickling through all referencing table's foreign keys. “Oh sorry, I forgot to mention that “Customer Account Number“ isn't really unique any more since we acquired XYZ corp. We should add “Division“ to the primary key“. All of a sudden you have to add “Division“ to every table you had in your system that was referencing Customer to maintain the foreign key. This is fragile not just because it requires adding columns to the database, but also because it requries populating that column and doing some data migration. It also means that queries that were optimized and understood to be singleton's no longer work against our database.

But does creating a CustomerID blind key completely make you durable to this change? It get's you pretty far in your database but it doesn't help your application much. Getting back to my assumption that even though you have a blind key doesn't mean you don't have (and should use in your application) a visible key.

Users may pick a customer out of a list or a search result dialog, and you might open that customer up in a details form. I would assume that this form's argument would take some kind of key passed to it (and passed down to the business object and data access component). If you are starting with a grid form list of customers, possibly generated from search, it's pretty tempting to just lift the primary key (blind key column) out the grid and pass it to the other form.

Is this kosher? What if you decide reuse this Customer Details and call it from an Order form (i.e. click here to see customer details). If the Order is stored in the same database, it likely has the same customerId blind key - so that would be handy their too. But what if you are intergrating this with other systems. What if you are allowing a partner to call a web service to request customer details? Should the partner pass the blind key? I would think that the blindness of the key is not so blind anymore if you did that. That get's us back to the alternate key or the user key. This is about a service boundary and it's similar to the same service boundary that exists between the user selecting a customer from a grid. What on that grid let them know they were opening the correct customer? If the business rules are really poorly defined it may in fact be every column on the table - but let's not go there.

A Web Service should in fact act as a service interface. You may have written your Customer business object's Load method to accept a customerId, That doesn't mean you Web Service API should be the same. It in fact should probably take Account Number and Division and translate that some how into a CustomerID before turning around and calling the Customer.Load(custId) method.