Re: One to One relationships

In message <c0d87ec0.0305071030.31b2898a_at_posting.google.com>, --CELKO-- <71062.1056_at_compuserve.com> writes
>>> In general there isn't a good natural key to identify people,
>that's why most databases use various surrogates (like SSN.) <<
>
>I call the SSN an external, verifable key and hence "nautral "ay
>surrogatge key, I mean a key in the system, created by the system and
>never exposed to the users (it can be binary for all we care). Hash
>codes, row ids, etc. An artificial key is one we make up internally
>and do expose to the users (needs check digits, syntax rules,
>constraints, etc.).

OK. I've seen instances when supposedly trustworthy keys go wrong. The UK also has a SSN code that is supposed to be a unique identifier. There have been instances of duplicate codes being issued to two different people.

Personally I prefer to restrict the term "natural key" to situations where uniqueness is enforced by the laws of physics. That doesn't happen very often. I use "surrogate key" when someone creates a code that is supposed to be unique.

>
>>> I've proposed a natural key for people; the precise latitude,
>longitude, altitude and time of their birth. I don't claim that it's a
>practical system.
>
>Nope! Someone did a study on Astrology and found that in New York
>City, there are enough maternity ward rooms within 3-4 meters of each
>other vertically or horizonally that almost 100 children per day are
>born with the same spatial and temporal co-ordinates.

I did say *precise* latitude and longitude. I've worked on systems where latitude and longitude are measured to a precision of 1 metre and the company that made the measurements could have given us figures to within a centimetre.

-- 
Bernard Peek
bap_at_shrdlu.com
www.diversebooks.com: SF & Computing book reviews and more.....

In search of cognoscenti