Re: identity columns

For people in the U.S., SSN is generally a perfect PK. Yes, there have been duplicates issued by the SSA, but they are extremely rare, and if your universe of people is known (i.e., not potentially anyone, but rather a company's employees), then SSN is the way to go. If you should happen upon a duplicate, change the first character to an A. If this happens, you'll need another column that contains the true SSN for tax reporting purposes.

"Ben Clifford" <benc_at_hawaga.org.uk> wrote in message news:3C34A168.9A4FCA6_at_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 - 21:38:41 CET