Re: identity columns

In message <52e031b9.0202121735.26662b23_at_posting.google.com>, tj bandrowsky <tbandrow_at_unitedsoftworks.com> writes
>> >>> If you insist on a true natural key then get your employees to
>> >specify the place and time of their birth. Getting the time to a
>> >precision of 1 second and the latitude, longitude and altitude to a
>> >precision of ~1ft should guarantee
>> >uniqueness. <<
>
>I think this makes the point that the entire notion of natural keys is
>absurd. You mentioned using position, but there is no concept of
>absolute position for anything, we all exist in relative terms.
>That's Einstein for ya, and it's human nature too.

I wouldn't go as far as saying that the notion is absurd. A time and latitude/longitude/altitude positioning system is perfectly adequate for the task. The aim is to find a system, however artificial, where uniqueness is guaranteed by the laws of physics. If you accept the many-worlds hypothesis then even this doesn't guarantee uniqueness.

My point is that there is a spectrum from true natural keys (which are rare) to completely useless keys. The SSN is close to being a natural key because duplicates only exist in rare pathological conditions. That's because there is an effective system that tries hard to maintain uniqueness. Near the opposite end of the spectrum is the forename/surname pair. Within a small group it will work most of the time.

For a database designer the key issues are the probability of getting a duplicate, and the amount of effort needed to handle the clash. In the case of the SSN (if you can guarantee to get the true value) duplicates will be so rare that the additional effort to program around duplicates is unjustifiable.

At the opposite end of the spectrum you may have an algorithm to generate a loginID from the users' names. When the second John Smith joins the company a simple algorithm will fail.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti