[thelist] MySQL database optimis(z)ation

[Warden, Matt]

On Feb 4, Joshua Olson had something to say about Re: [thelist] MySQL...

>is the major downside of the following strategy
...
>table story
>~~~~~~~~~
>id
>story_type_id (FK to story type table)
>common_data_1
>common_data_2
>...
>
>table story_type
>~~~~~~~~~~~~~
>id (PK)
>type_name
>
>table story_type_data_type
>~~~~~~~~~~~~~~~~~~~~~
>id (PK)
>story_type_id (FK to story_type table)
>type_name
>type (1 for string, 2 for date, 3 for email, etc... used in data validation
>and selection of input/output method)
>
>table story_type_data
>~~~~~~~~~~~~~~~~
>story_id (FK to story table, part of PK)
>data_type_id (FK to story_type_data_type, other part of PK)
>value (a string)
>
>It's a very complex system, which is one downside, but it very extensible.
>Can you help us identify some of the other issues involved with such a
>structure?

It's actually a rather common design, and you're probaly asking because
you've used it before, right?

Normally, it's used as a "custom attributes" structure. For example, let's
say you have a table for members:

MEMBER
-------------------
ID	| numeric
NAME	| varchar
PHONE	| varchar
CATNAME	| varchar

but, Joe Blow doesn't have a cat, and wants to store his dog's name. and
his ICQ number. and his AIM screename.

now, you could add all these fields to the table, but where does it
end? next thing you know, you've got 100 fields/columns in your table, and
no row has more than 5 that are non-null.

so, you set up a structure like yours, where CATNAME, ICQNUMBER, AIMNAME,
etc. are *rows* in a separate table and id's corresponding to datatypes
(varchar, numeric, varchar -- respectively) describe the datatype of each
row. the need for this information is created because the very nature of
this design calls for the "loosest" datatype available (allowing both
alpha and numeric and date characters).

Summed up, the structure above aims to solve the problem of having 'n'
number of 'attributes' of unknown datatype to a given table, where n
varies by row from 0 on up to potentially infinity. The concept is
equivalent to a single table whose columns (number, name, and type) vary
by row.

most of this you probably already know, but i think it's good to know for
any programmer, as they'll most likely run into a DBA who likes this
structure at SOME point in their career (which explains why rudy wasn't
immediately familiar with it: he's never had to be at the mercy of a DBA).

--
mattwarden
mattwarden.com