Normalization-unique key vs. lookup key?

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. Received on Fri May 19 2000 - 00:00:00 CDT