Re: identity columns

>
> 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.

Hmmm. I think you were replying in a confused way to the confused thesis I had presented, and as a result the above is as confusing to me as I'm sure that my writing is confusing to you. So...

I do actually agree that latitude / longitude would be appropriate to the task. The operative phrase is "to the task". We say that task gives the latitude, longitude context. Observe that the art of database design separates task from the tools, there's no context in a table of X,Y coordinates. To say that X,Y is better than sequence number is incorrect because it implies that X,Y closer fits the task. The real primary key of something in a database design is really TASK+key, so TASK+X,Y or TASK+sequence really aren't all that different, because the major distinguishing factor in the key is completely outside of the design of your database... It's assumed in a design that the thing you are designing is for a particular task, yet, it's impact on the longevitiy of your systems is huge!

I would say that because TASK is so big in the overall unique identification of TASK+key for a database object, the choice of key being natural or autogenerated is really relevant. TASK is always relative, in and of itself, so no matter what you pick key to be, it is going to be ultimately relative too.

> 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.

Saying that SSN is suitable to be a primary key does not automatically make it a preferred key because it has some pseudo real world mapping, and an id doesn't. It only makes it a convenient key for those humans who happen to know everyone's social security number. The rest of us go by names, which are duplicate, or names and addresses, which violate 1nf.? Received on Wed Feb 13 2002 - 19:48:11 CET