Unnecessary Joins Question

SQL Apprentice Question
I have a question that has been nagging me for some time. I have a large
database where several parts have JOINs to what would be the equivalent of a
drop-down list in the user interface (e.g. a list of types). The (types)
table only has a TypeDescID field (PK, identity) with a TypeDesc field
(Unique, nvarchar(15)) and thats it.
Since this occurs in several places a
JOIN has to be created on any select statement needed to pull information
from the FK table containing the TypeID which causes added processor
utilization. Would this be a scenario where one would denormalize by
removing the TypeID field altogether, making the TypeDesc the PK and FK thus
eliminating the need for extra JOIN clauses during SELECT statements which
would ease the processor load?


Thanks in advance!




Celko Answers
>> I have a large database where several parts have JOINs [unh? what does that mean? Queries have joins, DDL does not] to what would be the equivalent of a drop-down list in the user interface (e.g. a list of types). <<


.. or the equivalent of wheels on a horse?


>> The (Types) table only has a TypeDescID field [sic] (PK, identity) with a TypeDesc field [sic] <<


Gee, too bad that IDENTITY cannot be a primary key, that fields and
columns are nothing alike and that "Types" is too vague to be a valid
data element name. I am assuming that you did not commit OTLT with
this "Types" table; that would be a total disaster.

By definition the suffix "type_id" makes no sense (which is it? A type
or an identifier -- it cannot be both!!). If you followed ISO-11179
and proper design, this would be something like FoobarTypes (foo_type,
foo_description) with appropriate constraints.



>> Since this occurs in several places a JOIN has to be created on any SELECT statement needed to pull information from the FK table containing the TypeID which causes added processor utilization. Would this be a scenario where one would denormalize by removing the TypeID field [sic] altogether, making the TypeDesc the PK and FK thus eliminating the need for extra JOIN clauses during SELECT statements which would ease the processor load? <<


JOINs are not that expensive; if they bother you, then hide them in a
VIEW. However, trying to get the description typed correctly every
time is a royal pain.


>> Any help would be greatly appreciated. <<


Start refactoring your design until you at least get the data element
names right. After that, sit down and design a foobar type (or
whatever it really is) encoding. Newbies often just sequentially
number a list and think this is the same thing as designing an
encoding. Thank God that Melville Dewey did not do this, or public
libraries would never work :)