How about using a stored procedure/function to encapsulate the sequence or SYS_GUID value? That's database-neutral. Under the covers, it's whatever optimal mechanism you want, but from the app-developer perspective, it's a function returning a PK value...
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, November 04, 2002 6:33 AM > Thanks everyone. > > We're going forth with using a single sequence w/o any additional "meaning" put > into the PK. > > Ian's recommendation of using the sys_guid function would be ideal, however, we > are > trying to remain database neutral at this point. > > > [EMAIL PROTECTED] wrote: > > > Brian, > > > > As you pointed out, the design of this function will play > > a rather important part in the performance of this app. > > > > The first thing I would question is the use of this column > > as a PK. A generated number should be fine. PK's should > > not carry any information in them, they're just an ID. A series > > of sequences or any non-serialized method of generating > > them would be appropriate. > > > > Regardless of whether this function generates a PK or a > > UK, it needs to be designed to prevent serialization. > > > > e.g. Using a single row table with some kind of counter, or > > any similar one-at-a-time key generation will really limit > > the scalability of the app. > > > > HTH > > > > Jared > > > > "Brian P Andrews" <[EMAIL PROTECTED]> > > Sent by: [EMAIL PROTECTED] > > 10/31/2002 05:33 AM > > Please respond to ORACLE-L > > > > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > cc: > > Subject: Database Design: unique PK across all tables > > > > Our developers are proposing a database design for an OLTP application > > in which each table has a PK of the same type and size. In addition, each > > possible PK value can belong to at most one table. > > Each table insert would require a call to the a single function to get > > the next PK value and an additional table would be used to store the > > current set of values. (The developers want to put some additional > > meaning into a PK value and a sequence would not be sufficient, hence the > > need for the PK generating function and current value table). > > I've never seen this done before and I would think this application > > would suffer greatly from contention when performing a large number of > > concurrent inserts. > > Has anyone ever encountered a design like this? Is this a bad design? > > Thanks. > > Brian > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > 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.com > -- > Author: Brian P Andrews > 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.com -- Author: Tim Gorman 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).
