Andreas, Andrew,
> You might be right for small dbs, but a temperature database will likely
> contain millions of rows, if filled in the real world. varchar will
> probably make the table several times bigger than needed.
Yeah, INT keys are useful for performance reasons. It depends on the table.
I often find that text keys are often more useful for short lookup lists
because it allows me to avoid linking in dozens of tables in a star-schema
OLAP database. There I usually find that the efficiency gained by the
smaller size of the INT column is more than offset by the cost and decrease
in estimate accuracy inherent in adding a bunch of lookup tables to the
query.
Also, for tables where the text key is required in the table, *adding* an
additional INT column as a key is no improvement in performance.
> Additionally, I regretted *every* case where I decided to use some text
> data as key, sooner or later.
Well, obviously you and I have had different workloads then.
> We will probably find that *everthing* each of us does will offend somebody
> else. I'm not too keen to get into "best practice" wars. That's what IRC is
> for ;-)
<grin>
> I would like to see a number of samples, which demonstrate different design
> methodologies/philosophies, so we are offending everyone with equal
> opportunity.
Yeah, that would be great.
--Josh
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco