I have a condition where I've identified a business key that has the following characteristics:
- Is unique within the system/table
- Is used by business users to lookup unique records within the application
I was happy to see a real business key until I found out that they are not durable. The business has a process where they rename this key by placing a "z" in front of it to indicate it is not in use anymore (still unique within the table however). This process means this key fails to meet one of Dan's criteria that business keys should not be editable. If that is a strict rule, then I'm forced to use an identity column on the table (which I'm trying to avoid if possible).
This condition makes up roughly 10% of the data so it's not insignificant. It can be argued that this is a poor business practice and should be discussed with the business to see if it can be changed, but this will probably not happen any time soon.
So I guess my question is whether or not a business key MUST be non-editable (no exceptions) before it is considered as a business key candidate or if there are exceptions to the rule which are accompanied by some approach to account multiple business keys in the hub. It's a shame that we could not gain passive integration for "most" of these records due to this key be editable.
If we did use this editable business key to gain passive integration benefits, I would think we would need a status tracking satellite to let us know when that original business key no longer exists in the source due to the business process that renames the key. May also need a same as link at some point to let the business know these two keys are, in fact, the same thing.