Re: What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000?

"Bob Badour" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>
> Then I can only conclude you lack the ability to comprehend relatively
> simple written english.
>

Well, then apparently you are so good at English, and less good at being
precise.  If natural keys and surrogate keys were in fact the same thing,
then why would we have multiple terms for these things?  They are different,
as far as building databases is concerned.  Surrogates keys, as defined, are
keys used instead of a natural key.  And as far as the following:

>That might be a good definition of an IDENTITY column, but it has no
bearing
>on surrogate keys. By equating the identity columns with surrogate keys,
you
>only confuse yourself and potentially any similarly uneducated readers.
>Nothing about a surrogate key requires a DBMS to generate it.

I did not come up with any of this terminology.  An identity is an
artificial key.  It is used as a surrogate key.  It is not a natural key.  A
surrogate key is not required to be created by the DBMS, but there is no
reason why we cannot us a mechanism built in to provide it.

>I am not my name and my name is not me. My name is a surrogate for me
chosen
>for simplicity (simple for an english speaker to say), familiarity
(familiar
>for english speakers) and stability (my name changes rarely).

You name is part of what makes you you, because everyone has a name.  It is
technically not a reasonable key, but it possibly part of a key (like the
MPAA does with actor/director names, just by adding a number, or forcing a
name change.)

>> Why?  Do you want the user typing, remembering, or dealing with the
>> difference between ID=320983902 and 320984902 or 320983903?

>That depends. If I were creating an identifier for a credit card, I would
>want at least seven more digits, and yes I would want users typing,
>remembering, swiping and dealing with the numbers.

Strangely enough, this is done with credit cards specifically so it will be
hard to rememeber.  On the other hand, I would not use a credit card number
as the primary key of a credit card table.  Why?  Because I would not want
that key spread around the database in other tables.  A surrogate key here
would be used to obscure that fact, leaving only a single point of contact
with credit card numbers to be secured.

>Preventing the user of the data from seeing the identifier for the data is
>just plain stupid.

Yes, I am plain stupid.  That is why when you go to a form to choose the
type of credit card you are going to use it shows you 320983902-Visa,
320984902-MasterCard, 320983903-Etc.  Or even VS, MC, ET.   No, humans like
to see Visa, MasterCard, etc.   I am not implying that a user would be
disallowed from using a value, just that there is little reason to ever
present an ugly value to a user, except when that is precisely the desire.

Why I am plain stupid is that I am continuing this discussion and getting
called stupid every few seconds by a person who is not actually having a
discussion that could possibly lead anywhere. If you don't see my point, and
I clearly cannot see your point through all of the muck and anger, then why
waste time.  I have these discussions so I can improve my opinions that I
have carefully crafted over 11 years, and that I frequently give to others.
If it is a matter of taste, then I don't mind anyhow.  I like hearing others
opinions, and as to why my ideas are wrong if they are (and some are.)

-- 
----------------------------------------------------------------------------
-----------
Louis Davidson ([EMAIL PROTECTED])
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)