Re: Database Design: unique PK across all tables

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