David,
> You don't think having a separate reference table for each code
> lookup
> -- that is, making the datamodel more fully normalized will not
> impact
> performance? Probably nominally because there will be so few rows in
> them.
It will impact performance no more than, and perhaps less than,
evaluating a multi-stage CASE statement for each row in the query. And
this kind of performance concern is only an issue if you're trying to
run a public web site on budget hardware ... otherwise, the other
elements of your system will be more of a bottleneck than the query
parser! I regularly use queries and views that involve 6-9 tables,
three UNIONS, and two sub-selects in each UNION ... and still get a 2-3
second response time on the 500mhz Celeron production machine.
I actually use a single table to store all my miscellaneous reference
codes in most databases. Like:
CREATE TABLE misc_codes (
code_type VARCHAR(20) NOT NULL,
code_value VARCHAR(30) NOT NULL,
code_desc VARCHAR(200) NOT NULL,
CONSTRAINT codes_PK PRIMARY KEY (code_type, code_value)
);
This is an immensely convenient approach from a maintainence
perspective, although it has some drawbacks. For one, one has to be
careful to filter the codes by code_type *before* any aggregate
operators are applied, or duplicate codes will result in bad aggregate
values. If you have a completely star-topology database, it's probably
better to go the 100% normal way, and have a seperate table for each
code.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco