What I mean't was, and maybe this is different in different countries, if we have an employee who leaves us, realizes just what a nice guy I was to work with :(, and returns then one of two things will happen. If they return in a different tax year, they get the same empno, but for a different employment. If they return in the same tax year they get a different empno, for tax accounting reasons. Thus the same employee can reuse a key for a different logical employment, or have a different key for a different employment. The business rules mean that they are bad keys
Niall > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of TOMPKINS, MARGARET > Sent: 05 November 2003 21:25 > To: Multiple recipients of list ORACLE-L > Subject: RE: How do you genrate primary keys? > > > The thought is that if it is "internal" then you control it. > Of course, it doesn't mean you will do it right. ;-) Maggie > > -----Original Message----- > Sent: Wednesday, November 05, 2003 3:05 PM > To: Multiple recipients of list ORACLE-L > > > 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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); [EMAIL PROTECTED] > > > > > > > > > -----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" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > > > > > > 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
