Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: Database Design: unique PK across all tables

Re[2]: Database Design: unique PK across all tables

From: Robert Eskridge <>
Date: Thu, 31 Oct 2002 09:03:37 -0800
Message-ID: <>


Yeah, I've encountered it in databases for real time process control systems, but those were systems where relational databases were rejected for having too much overhead in the data dictionary. The PK was actually a pointer to an absolute location (sort of like rowid but even more basic). These systems were heavy read and update but almost zero insert and delete in a production environment. Selects as known in a relational database did not exist, all information retrieval was pre-interpreted to the PK's and specific attributes of the row. These are very difficult to work with and certainly not general purpose databases. Historical reporting was often snapshots taken and moved to a general purpose database like Oracle where the whole uniform PK stuff was forgotten.


B>   Our developers are proposing a database design for an OLTP application in
B> which each table has a PK of the same type and size.  In addition, each
B> possible PK value can belong to at most one table.

B>   Each table insert would require a call to the a single function to get the
B> next PK value and an additional table would be used to store the current set
B> of values.  (The developers want to put some additional meaning into a PK
B> value and a sequence would not be sufficient, hence the need for the PK B> generating function and current value table).
B>   I've never seen this done before and I would think this application would
B> suffer greatly from contention when performing a large number of concurrent
B> inserts.

B> Has anyone ever encountered a design like this? Is this a bad design?

B> Thanks.

B> Brian


Please see the official ORACLE-L FAQ:

Author: Robert Eskridge

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services

To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 - 11:03:37 CST

Original text of this message