Re: Primary Key Question

In article <399BFF7E.57D5C654_at_yahoo.com>,   Tim Ringwood <tringwood_at_e2gotech.com> wrote:
> I was told early on in the world of DBA stuff that a primary key
 should
> normally
> be a sequence number I generate. At times I have use two keys from
 other
> tables
> when the table in question is a join table. The person I am now
 working
> with
> doesn't think you need a sequence number if the table in question is
 an
> "end node".
> (I don't know the correct term, but a table in which isn't refrenced
 by
> any other
> table). Instead the primary key is made up off user inputed data in
> combination
> of sequence numbers from other tables (at times having 4 fields making
> up the
> primary key). How do people feel about this? Hot debate here!
>
> thanks for any feedback!
>
> tim
> tringwood_at_yahoo.com
>
>

IMO, you use a sequence when there is no appropiate candidate primary key, or it is made up of many columns, or it is made up of long varchar data. Many designers use sequences indiscriminately. This results in the creation of extra indexes, as one never ever searches on that primary key.
Example:
The system I am currently working on (I didn't design it), has tables logging changing. The overall definition of those table is primary key (sequence number)
foreign key (to the table to be logged)
timestamp
column name
old value
new value

In this case the combination of the foreign key and the timestamp would have constituted a proper primary key, and the sequence number is used nowhere.

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.