Re: Test database for new installs?

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