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]
> "Louis Davidson" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > The problem is with how it is used.  If you start giving users access to
> > identity based values, you get into a bad spot where they want to make
> > changes to the value (in my line of work, we don't like the numbers 666
in
> > account numbers) so using identities for user values is a bad idea.  I
use
> > them only for internal pointers that are never presented to users, since
> > they are not modifiable.  I could use guids, or characters, or whatever
> for
> > keys and no one would be the wiser.
>
> Keys are logical identifiers. They identify data for the user as well as
for
> the dbms. Preventing the user from seeing the identifier is just stupid.
>

Why?  Do you want the user typing, remembering, or dealing with the
difference between ID=320983902 and 320984902 or 320983903?  I certainly
don't.  Invariably they would want something that they understood.  Or what
about a GUID:  6969B66E-6A7A-4E89-B2D9-B35799B335C1 vs
DCF5DBC5-73B5-4009-9BBC-9312CFD6AD9D.  Yick.

Kind of like you have a user name, an email address, fingerprints, an SSN
and DNA, etc that all identify you, but you only use a few of them here.
DNA is ugly, but as perfect of an identifier, but I don't know what my DNA
is, but it still exists.

>
> > > Natural keys are nothing more than familiar surrogates.
> >
> > You are kind of right here, but it is generally true that natural keys
can
> > change, because in the world, things can change.  Identities cannot
> change.
>
> It is not a "kind of" rightness. The statement is obviously and
> self-evidently right to anyone with at least a minimal education in the
> fundamentals of data management.
>
I suppose.  But I don't think that the statement means anything.  I don't
agree that all natural keys are familiar surrogates.  Not every natural key
was initally randomly chosen.  Some level of thought was placed to choosing
a name, hence it is not just a familar surrogate.  A surrogate key (the word
surrogate meaning to take the place of, and the definition of a surrogate
key is: A unique primary key generated by the RDBMS that is not derived from
any data in the database and whose only significance is to act as the
primary key.  I would remove the word primary from the sentence and we have
what I would agree with the definition)

They are very much alike, but I don't think you can state that a natural key
is a surrogate, ever.  They are both keys, in that they both are
determinants, which is why we have called them keys.


-- 
----------------------------------------------------------------------------
-----------
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 :)