RE: How do you genrate primary keys?

Except of course that internal employee ids also can get reused, and the converse the same individual can have more than one employee id.

Niall

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> Behalf Of TOMPKINS, MARGARET
> Sent: 05 November 2003 14:10
> To: Multiple recipients of list ORACLE-L
> Subject: RE: How do you genrate primary keys?
>
>
> Social security numbers are notoriously bad natural primary
> keys. Did you know that they are re-used? Yes, it's true.
> Generally, they don't get re-issued until after one of the
> users dies, but it's been a problem in the past and still is.
> What do you do with people who don't have SSNs? Foreign
> nationals and others that work for US companies oversees or
> provide goods/services generally do NOT have SSNs. An
> internal employee id would be a much better choice if a
> "natural" primary key is needed.
>
> Respectfully,
> > Maggie Tompkins - CAD SQA
> > Corporate Applications Division
> > Technology Services Organization - Kansas City
> > Defense Finance and Accounting Service
> > 816-926-1117 (DSN 465); Margaret.Tompkins_at_dfas.mil
> >
>
>
> -----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
> >
> > 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).
>
>
> __________________________________
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: TOMPKINS, MARGARET
> INET: MARGARET.TOMPKINS_at_DFAS.MIL
>
> 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: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.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).