Re: identity columns

John,

Most of my experience is in data warehouse design so my opinion may be a little slanted.

Are you building the company's employee records system? If this is the case, then you could create an EmployeeId field and your application would be the source of record. If not, what does the employee records system use to uniquely identify an employee? This would probably be your safest key.

But back to email address...Are all employees assigned an email address? How soon after hire? Is there a standard by which the address is assigned (ie first initial + middle initial + last name + tie breaker)? Is it possible for the email address to change? Do all employees belong to the same mail domain? If so, you could just carry the email id instead of the entire address. I know I'll get flak for this question, but what about using SSN, or SSN + last name? I am currently consulting for a large government agency. They use first initial + middle initial + last initial + the last four digits of SSN to uniquely identify it's employees. Once its assigned, it doesn't change even with a name change.

When you create a surrogate key, you normally have a translation table that will translate the natural key to the surrogate key. That is how you are able to find the correct employee record when you need to change the employee phone extension, or employee name. You really do need to come up with a valid natural "identifier" whether you are going to use surrogate, or natural keys in your database.

I usually don't let the extra storage space dictate whether I use natural keys or not. Suppose you can use the email id portion of the address only. Let's say it's an average of 10 bytes across all 100,000 employees. If the 100,000 employees generated 10,000,000 orders, the natural key would only generate an additional 60 meg of storage, minus the half meg for not carrying the surrogate key in the employee table. 60 meg is nothing (my VLDB warehouse experience may be swaying my opinion though:).

As far as creating a multi-column foreign key. You would just migrate all columns.

For what it's worth,
Russ

"John" <ru_re_re_move_zel_at_ecl_re_re_move_ipse.net> wrote in message 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 - 09:00:12 CET