Re: Artificial Primary keys

In message <c0d87ec0.0202040802.78106b93_at_posting.google.com>, --CELKO-- <71062.1056_at_compuserve.com> writes
>>> No, it's artificial. There's an arbitrary coding scheme that links
>the
>numeric values in the ISBN to real-world data. There's no inherent
>connection between the number 56592 and the publisher it identifies.
><<
>
>The test for a natural key is one that "occurs in nature" -- meaning
>the reality that the database is supposed to model. Physical reality
>is only one thing we might model -- how many accounting systems are
>total works of fiction <G>?
>
>ISBN's come from a trusted outside party and are verified by everyone
>in the book trade in the whole world. The book trade is the external
>reality. The iSBN is a natural key in that reality.

It really should be and if you trust the third-party then you can treat it as if was a natural key. In the US and UK it is reasonably safe to do that.

There are limits to the value of the ISBN as a key. There are places where it is not safe to assume that the numbers are unique. ISBNs only exist on books published since the mid 1960s. Book-club editions may have an ISBN on the inside because the publisher has re-used the plates that were used to print an earlier edition, but book-club editions are not really published and so should not bear an ISBN.

Using a key that you don't control is a calculated risk. Database designers must understand their data well enough to know how big a risk that is.

>
>Now if you make up your own SKU code for your bookstore, then you have
>an artifacial key. You can still put come checks on this key and
>verify it. It is simply useless outside your store in the reality you
>are modeling. A surrogate key would be a GUID or IDENTITY or ROWID or
>hashing or whatever the engine creates and nobody should ever access
>it because it is for system use only.

...unless it is published. There is no real difference between an identity column and an invoice-number or SSN. All of them are artificial. In practise the reason we accept invoice-numbers is that the organisation that issues them stamps them with its imprimatur and has auditors who will attest that the numbers are valid. The value of any such key depends entirely on its reliability. If we trust the organisation that issued or controls them then we can treat them as if they were natural keys.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti