Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: identity columns

Re: identity columns

From: Ben Clifford <benc_at_hawaga.org.uk>
Date: Thu, 03 Jan 2002 18:22:33 GMT
Message-ID: <3C34A168.9A4FCA6@hawaga.org.uk>


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
Received on Thu Jan 03 2002 - 12:22:33 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US