Re: Primary Key of a table

amit_at_ix.netcom.com(Amit Bansal) wrote:

>In <4jebm3$5p_at_dlsn31.dal.mobil.com> Jay Mehta
><jvmehta_at_mra.ffx.mobil.com> writes:
>>
>>One of the consultant I worked with recommends only numeric primary
 key.
>>Numeric primary keys may be the most efficient. Numeric column uses 2
 

>>bytes to 21 bytes max to store a number, depending on the value and
 that
>>may be less compared to character or other datatypes. Compact size of
 

>>primary key may result in improved performance.
>>
>>Jay Mehta
>>DBA
>>Mobil Oil
>>
 

>Is the suggestion to use a dummy numeric key generated using a sequnce
>everytime a record is inserted in the table ?
 

>I think in the complex situations where the number of columns in a table
>which forms the primary key exceeds 4-5 and the key is used as a
>Foreign key in other tables then probably should go for a dummy numeric
>key. Otherwise we should use the table coulmns in the PK.
 

>Would welcome comments on this as I am in a process of designing a
>database currently and has followed this practice in past.
 

>Amit Bansal

Whilte the one, non-informational, numeric key philosophy can result in blazingly fast single-table searches, applying it to most modern, large systems results in a tangled mess of excessive joins, maintenance headaches, and ad hoc reporting nightmares. I have found that cascading master key structures (where the primary master table's key is part of the child's key) is a sound approach, if not taken to extremes. This reduces the number of joins, aides in referential integrity maintenance, and boosts the usability of most third party query and reporting tools.

Michael B. Openshaw
Integrated Medical Networks
(Opinions expressed are my own) Received on Tue Apr 02 1996 - 00:00:00 CEST