Re: Normalization-unique key vs. lookup key?

Brian Peasland wrote:

> 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.
>

I'm thinking of a concatenated index of (B,A) however. Putting the search field on the leading edge of the index makes it usable, doesn't it?

>
> > 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.
>

Takes less space, less work. Of course space is cheap and work is minimal for only one table. Question is, will performance suffer? Of course justifying a violation of normalization to my boss requires a more formal justification than just 'because I feel that this is better'.

>
> > 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

Since Normal Forms deal with the Primary Key, and in Oracle the Primary Key generates an index, then in Oracle the Normal Forms deal with indexes. Simple logic, but note the qualification 'in Oracle'. I'm not sure I follow your statement about the rationale, except to agree that *if* the rationale has nothing to do with indexes then it doesn't fail simply because you need to create extra indexes. If, however, it has to do with redundancy, then isn't it less redundant to store the only field you will be searching on the leading edge of the primary key, even if it's the rest of the key which is unique? Received on Mon May 22 2000 - 00:00:00 CDT