Re: identity columns

Use of email address is a bad choice. In short: the key should not carry information by itself (as it then is likely to change - certainly the case for an email-adress).
Use of an "Identity column" is ok if you assure that the value is generated and used consistent.
In general, a "surrogate key", such as an "Identity (autogen) column", is in fact a good idea. But you must then declare constraints (such as unique index) on the (natural) candidate-keys to keep the database healthy.

In the case where more than one coulumn is required to make a candidate key, it would be ok to make a single artificial (surrogate) key (again observing the constraint on the natural candidate key).

I would say that your own feelings for what is good and what is bad are sound ...

"John" <ru_re_re_move_zel_at_ecl_re_re_move_ipse.net> skrev i en meddelelse news:JnQY7.359$8j1.340017305_at_news.netcarrier.net...
> There seems to be a lot of talk in this NG about the good, bad and ugly
> aspects of primary keys - especially Identity columns. I've been working
> with sql databases for 5-6 years and the last 1.5 years I've spent
designing
> databases from scratch, so I am far from any kind of expert. (lucky if I'm
> novice <s>)
>
> I'd like to know - are you against the datatype "Identity" or are you
really
> against having an "artificial" key in *every* case?
>
> Here is simplified example from the database I'm currently working on: (I
am
> using a relational DB - specifically MS Sql Server 2k - I have to work
> within certain constraints - lots of responses say "use OODB", or "use
this
> product", those are not options for me)
>
> I have a table called Employee - the candidate primary key is the
Employee's
> Internet Email Address, but I resorted to using an Identity column called
> EmployeeId.
> Furthermore - I also have a table called "Order" where 1 Employee can
create
> many orders - so - if the Employee primary key is EmailAddress does it
make
> sense to store something that large in the "Order" table? There are many
> other tables where EmployeeId is a foreign key - am I wrong to think I
> shouldn't be storing something large like email address all over?
>
> I guess the advantage of using something like "EmailAddress" would be that
I
> don't have a "nonsense" number in my database - however - if EmailAddress
is
> a primary key then I can't change an Employee EmailAddress without
changing
> it *everywhere* - in fact - I'm not sure which Employee attribute could
> never change.
>
> Granted - I only use Identity columns as a "Last Resort" - for example:
> An order can be in one of three states: Pending, Approved, Denied
> So - I have a table called OrderStatus where the primary key is char(1)
and
> the values are 'P', 'A', 'D' It's nice because there is meaning to the
> primary key - why didn't I do that for Employee? There are 100,000 of
them.
> Names can conflict with each other - I can't guarantee I'll always have an
> email address, etc. So - I resorted to using an Identity column.
>
> One more question - sometimes there are two columns making a table
unique -
> without creating a single artificial key - how would you *easily* create a
> foreign key?
>
> Thanks for any and all replies -
>
>
Received on Thu Jan 03 2002 - 10:35:23 CET