Save a join or two

We have several queries that involve quite a few joins (usually around 6-7).
The queries involve one main table linked via foreign keys to several other
tables, including both lookup and regular tables.
So far, we have been linking our foreign keys from the main table to Identity
columns in the external tables; however, we are now thinking of linking them
to the actual values. If, for example, we have a lookup table which only
contains an Identity, a value, and some other information of no interest
to anything but itself, and the value is defined as unique, then wouldn't
linking the foreign key straight to the unique value save us a join on that
table? Would it increase the size of the database, and if so, would that
even matter, considering that using the former approach we would be joining
the tables and retrieving the same amount of information anyway?
Any advice would be highly appreciated...
Ido,
What you are debating here is whether to use a surrogate key in your look up
tables or not. You have identified that you will reduce the number of joins
you have by using one of your candidate keys as the primary key. This will
improve your database performance and may increase the amount of space used
marginally.
If you KNOW for sure that the values in your FK tables will NEVER change,
then I would remove the surrogate keys and use your candidate key. The
important thing is ensuring that any primary key does not change - this will
cause you big headaches.
Regards,
Mark.
"Ido Kalir" wrote:

> We have several queries that involve quite a few joins (usually around 6-7).
> The queries involve one main table linked via foreign keys to several other
> tables, including both lookup and regular tables.
> So far, we have been linking our foreign keys from the main table to Identity
> columns in the external tables; however, we are now thinking of linking them
> to the actual values. If, for example, we have a lookup table which only
> contains an Identity, a value, and some other information of no interest
> to anything but itself, and the value is defined as unique, then wouldn't
> linking the foreign key straight to the unique value save us a join on that
> table? Would it increase the size of the database, and if so, would that
> even matter, considering that using the former approach we would be joining
> the tables and retrieving the same amount of information anyway?
> Any advice would be highly appreciated...
>