[thelist] MySQL database optimis(z)ation

[Joshua Olson]

Rudy,

I know that some versions of MySQL are not able to do subselects, but what
is the major downside of the following strategy (which is a *fairly*
normalized structure):

In the story table, place the common elements.  In a another table, place
the "extra" data with a "data type" field that identifies what type of extra
information it is.  The table structure may look like the following:

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?

TIA,
-joshua