Re: Normalization-unique key vs. lookup key?

It is generally good practice to have the primary key be a MINIMAL key. If you concatenate the unique sequence number with any other field, then the primary key ceases to be minimal. All of this is just sound database theory. As with any theories, it may make sense to break the rules once in a while. You have to ask yourself how the data will be used.

For example, let's suppose the column A is the unique sequence number and column B is the extra column in the table. If you only search the table on column A, then have only column A in the index. If you search the table on column A and column B, then use a concatenated index. If you search on column B, then a concatentated index of (A,B) will not be of any use to you and you'll probably want two seperate indexes.

> It seems to me that it would be better to create a concatenated primary
> key with the lookup field on the leading edge, but since this violates
> the rules of normalization I need to justify it. Can I do so?

The question is "why do you feel that this is better?" If you can justify your feelings, then implement your solution.  

> As far as I can see, the rationale for excluding extraneous fields from
> the primary key is to keep the key, and it's related index, as small as
> possible. This rationale seems to fail however if you need to create an
> index on the extraneous field anyway, doesn't it? Do you lose some
> efficiency by using a unique index to select a range of records?

The rationale for making the key minimal is not to keep the index small. It's to help eliminate data anomolies, redundancies and lossless joins while preserving dependencies. So the rationale does not fail when creating extra indexes. The Normal Forms do not have anything to do with indexes. They do have a great deal to do with the Primary Key. In Oracle, the Primary Key field automatically generates an associated index. This is how Oracle guarantees uniqueness but Normal Forms and Normalization Theory does not specify anything relating to indexes.

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================