Re: identity columns

In message <JnQY7.359$8j1.340017305_at_news.netcarrier.net>, John <ru_re_re_move_zel_at_ecl_re_re_move_ipse.net> writes
>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?

I'm against using an artificial key where there is a usable natural key. There are many situations where there is no natural key available. The commonest one is where there is a need to identify a person, people have no natural keys. The nearest equivalent is the SSN which is simply an artificial key generated by a trusted third-party. Other artificial keys are things like invoice numbers.

>
>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.

You could call it payroll number or some similar name. Using the employee's e-mail address is only useful if there is a mechanism to make sure that each employee has one and only one address and that each address refers to one and only one employee. One way of doing this would be to assign a payroll number to each employee and assign the number as their e-mail address, making addresses similar to the old Compuserve addresses.

In essence, use a natural key only when you have the ability to ensure that it is unique.

>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?

There are overheads either way. Either you store a large key or you store a smaller key and spend some processing time to decode it. Which is more important to you?

>
>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.

There are no easily available natural unchanging attributes of an individual person that can be used as a primary key. If you insist on a true natural key then get your employees to specify the place and time of their birth. Getting the time to a precision of 1 second and the latitude, longitude and altitude to a precision of ~1ft should guarantee uniqueness.

>
>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?

There's no theoretical requirement for a foreign key to be a single column.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti