[ale] Database consulting in return for homebrew?

[Michael B. Trausch]

On Sun, 7 Dec 2008 08:58:33 -0500 (EST)
Mike Harrison <meuon at geeklabs.com> wrote:
>    Use a "unique" record identifier on every table.
>    I name all of mine: "uniq"

Do, however, use a natural key instead of a surrogate.  Using an
auto-incrementing integer or a UUID is fine for some things, where you
don't have an a priori natural key or where you've denormalized for
performance and your natural key is spread amongst a few different
tables.  But, if you have a natural key present, use it.

>    Make sure any number fields (like a double(16,2)
>    will handle the number size needed, to the accuracy
>    required. "unsigned" is helpful, but if you need to
>    work with negative numbers, a pain.

MySQL doesn't have an arbitrary-precision field type?  It is of course
slower to use AP numbers, but sometimes you just don't have the ability
to constrain a numeric field based on what might get put into it in the
future.

>   "The FLOAT and DOUBLE data types are used to represent approximate
> numeric data value"
> 
>    That word "approximate" is important. MySQL will round
>    numbers to fit. I often do math to 6 decimal palces
>    in MySQL and this will bite your ass.

Sounds like MySQL still needs to gain NUMERIC support.  *shrugs*

>    Index, index. index. Can sure speed up things
>    as they get larger.

Do be sure not to index fields that have fields of a very limited
range.  For example, indexing an ISO 5218 gender code field is pretty
pointless, unless you create a subsetted index on values that are
neither 1 or 2.  Not sure if you can do that in MySQL or not, in
PostgreSQL, this is called a conditional index---very powerful and nice
to have when indexing is otherwise suboptimal.  Definitely don't index
if there are only a few values in the column ever, because unless
values are widely distributed, there will still be a table scan
required.  Try not to rely on columns with low distribution of values.

	--- Mike

-- 
My sigfile ran away and is on hiatus.
http://www.trausch.us/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: not available
Url : http://mail.ale.org/pipermail/ale/attachments/20081208/e93a66c0/attachment-0001.bin