I'm fully convinced. SSN should not be used as a PK.
Can we also conclude that natural keys in general are only good if you sit in
an ivory tower and do unrealistic lab test?
Yong Huang
- "Bellow, Bambi" <bbellow_at_chi.navtech.com> wrote:
> Having worked for the government in a situation where we were actually
> tracking information BY Social Security Number, let me tell you the problems
> with it.
>
> 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE
> 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security
> Number
> 3) Not all Social Security Numbers are numeric
> 4) Not all Social Security Numbers which ARE numeric are 9 characters in
> length
> 5) Social Security Numbers can be changed by the holder
> 6) It is illegal to use the Social Security Number for any purpose other
> than that which the government specifically uses Social Security Numbers for
> (ie., the distribution of benefits). I'll bet *that* one is strictly
> enforced.
>
> HTH,
> Bambi.
>
> -----Original Message-----
> Sent: Wednesday, November 05, 2003 8:00 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Tom,
>
> I think using a natural key such as Soc. Sec. # as the primary key is a good
> idea. You don't need to maintain the sequence so there's no performance
> issue
> associated with sequences. There's no issue of gaps. No index root block
> contention. It doesn't seem to be industry common practice though.
>
> In your college student case, changing primary keys is rare so it's not a
> big
> problem.
>
> Yong Huang
>
> --- "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us> wrote:
> > Jonathan,
> >
> > I think your idea of a paper is a good one. But I think we need to back
> th
> > question up to what the requirements are.
> >
> > First, to me, a primary key should not be something that a user would ever
> > see or use. So the Soc. Sec. # is out. (A side issue - I used to work at
> a
> > college. Want to know how many times we had to change the Soc. for an
> > individual student because the parent filled the form out and used their
> > soc, or the kid used the wrong one?). Any id entered by a user is subject
> > to mistakes and changes. So the PK value must be protected from these
> types
> > of errors.
> >
> > The next requirement that may be needed is sequentiallity (is this a
> word?).
> > Does the application require that every sequence number be used.
> Sometimes
> > the answer is yes, and sometimes it just doesn't matter.
> >
> > These are the only two requirements I can think of. Based on the answers,
> > we then have options. Right now, Oracle sequences are working well for
> me.
> > I like the idea of SYS_GUID, just not sure where I would need it.
> >
> > Good idea and good luck!
> >
> > Tom Mercadante
> > Oracle Certified Professional
> >
> >
> > -----Original Message-----
> > Sent: Wednesday, November 05, 2003 8:19 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > The recent article that mentioned sequences got me to
> > thinking. I might pitch a more detailed article on sequences
> > to Builder.com. But a more interesting article might be one
> > that explored various ways to automatically generate primary
> > keys. So, in the name of research, let me throw out the
> > following questions:
> >
> > What mechanisms have you used to generate primary keys?
> > Which ones worked well, and why? Which mechanisms worked
> > poorly?
> >
> > I've run up against the following approaches:
> >
> > * Hit a table that keeps a counter. This is the "roll your
> > own sequence method". The one time I recall encountering
> > this approach, I helped convert it over to using stored
> > sequences. This was because of concurrency problems: with
> > careful timing, two users could end up with the same ID
> > number for different records. Is there ever a case when this
> > roll-your-own approach makes sense, and is workable?
> >
> > * Stored sequences. I worked on one app that used a separate
> > sequence for each automatically generated primary key. I
> > worked on another app, a smaller one, that used the same
> > sequence for more than one table. The only issue that I
> > recall is that sometimes numbers would be skipped. But end
> > users really didn't care, or even notice.
> >
> > * The SYS_GUID approach. I've never used SYS_GUID as a
> > primary key generator. I wonder, was that Oracle's
> > motivation for creating the function? Has anyone used it for
> > primary keys in a production app? What's the real reason
> > Oracle created this function?
> >
> > * Similar to SYS_GUID, I once worked on an obituary-tracking
> > application that built up a primary key from, as best I can
> > recall now: date of death, part of surname, part of first
> > name, and a sequence number used only to resolve collisions,
> > of which there were few. The approached worked well,
> > actually, because whatever fields we munged together to
> > generate a primary key gave us a unique key the vast
> > majority of the time.
> >
> > The SYS_GUID approach is interesting, but if you need an ID
> > number that users will see, and that users might type in
> > themselves (e.g. social security number), is SYS_GUID really
> > all that viable?
> >
> > Best regards,
> >
> > Jonathan Gennick --- Brighten the corner where you are
> > http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
> >
> > Join the Oracle-article list and receive one
> > article on Oracle technologies per month by
> > email. To join, visit
> > http://four.pairlist.net/mailman/listinfo/oracle-article,
> > or send email to Oracle-article-request_at_gennick.com and
> > include the word "subscribe" in either the subject or body.
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jonathan Gennick
> > INET: jonathan_at_gennick.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Mercadante, Thomas F
> > INET: NDATFM_at_labor.state.ny.us
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Yong Huang
INET: yong321_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Nov 05 2003 - 12:34:25 CST
