Re: DECODE

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