In article <39259517.6D8E7B94_at_dced.state.ak.us>, Calvin Crumrine
<Calvin_Crumrine_at_dced.state.ak.us> wrote:
>I have a table which has both a single unique field (sequence
number)
>and a non-unique lookup field (user ID). Normalization is
considered
>good programming, but the normalization rules seem to dictate
(or maybe
>just recommend) that I use the unique field as the primary key,
which
>creates an index on it, and establish a separate index for the
lookup
>field.
>
>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?
>
>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?
>
>Does anybody know why, or why not, I should do this? TIA.
>
>
If you create a concatenated key then the only way to find the
user rows when you do not already know the sequence would be to
do a full table scan. You want the user to be indexed. Since
users are non-unique in this table then using a sequence as the
primary key makes sense if any other table needs to this data.
Since this sequence is unique and uniquely identifies each and
every row in the table there is no need or benefit to
concatenating another column to it. Technically doing so would
violate the rules of normalization because the row column values
would not be dependent on the entire key, but really only on the
sequence.
- Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!
Received on Mon May 22 2000 - 00:00:00 CDT
