| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Database Design: unique PK across all tables
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). Received on Thu Oct 31 2002 - 13:34:19 CST
 |
 |