Re: Normalization-unique key vs. lookup key?

ditto!

Rule of thumb: If there is a natural primary key, existing columns in the table that uniquely identify the fields in it, use it. If not, then use a pseudo primary key (sequence or autonumber type field).

If there will be a lot, more than one, children to the above table, and there are a lot of columns (say 4 or more) you may want to consider creating the pseudo-key anyway. Be aware though, if you do choose to use a pseudo key, there is no relationship between the data in the other columns that relates to that key, and if you have to find that association you can't.

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
"Brian Peasland" <peasland_at_edcmail.cr.usgs.gov> wrote in message
news:3929400C.31D11311_at_edcmail.cr.usgs.gov...


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

> ========================================