Re: identity columns

John wrote:

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

My gut feeling is that having a "nonsense number" as a primary key is often good - if it is not nonsense, then it has some meaning to non-db people, and one day they will (quite legitimately) want to change it.

> 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

I often do this - it has worked quite well, but can sometimes cause confusion - for example, if, later, it was decided to change the wording of "Denied" to "Rejected".

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

I have yet to find any easily obtainable immutable property of people - so you have to make one up.

-- 
Ben Clifford     benc_at_hawaga.org.uk
http://www.hawaga.org.uk/ben/  GPG: 30F06950
webcam: http://barbarella.hawaga.org.uk/benc-cgi/watchers.cgi
Work required: http://www.hawaga.org.uk/~benc/resume