RE: Database Design: unique PK across all tables

If the application were to use a table-driven sequence; i.e., " select max(keynum) +1 from sequence_table;" then it would indeed be database neutral; however, as you stated, that technique does not scale. Oracle sequences; i.e., those employing sequence.nextval, are an Oracle construct and are not database neutral.

SYS_GUID is not database neutral either. A sequence generating the primary keys for all pertinent tables in the database is probably better than using sys_guid. The latter would be used when you want to guarantee uniqueness not only within a database, but between databases as well.

If you really need database neutrality, do a google search on "uuidgen". I presume this is the program upon which sys_guid is based. It's available for nearly all *NIX flavors. I'm not condoning its use, just letting you know that it's available. We have one system which uses uuidgen here, much to my dismay.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

-----Original Message-----
Sent: Monday, November 04, 2002 5:34 AM
To: Multiple recipients of list ORACLE-L

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.

Jared.Still_at_radisys.com 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" <bandrews_at_paychex.com>
> Sent by: root_at_fatcity.com
> 10/31/2002 05:33 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: Jared.Still_at_radisys.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.com
-- 
Author: Brian P Andrews
  INET: bandrews_at_paychex.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.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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).