[thelist] MySQL database optimis(z)ation

[rudy]

> if i've got a set of different story types (news, reviews, events, etc)
> which all have the same content structure (title, body, image, external
> link, internal link, author, id), is it best to:
>
> 1. have different tables for the different story types

hi kris

that works, but it's a right mess to maintain

> 2. have the same table for all stories, and a separate table with id and
> story type (so i do my sorting through the second table, before pulling
> the content down from the first)

that works better, but why the separate table?  put the type of article
right into the article table

> 3. something else entirely...

suppose there's one type of article, amongst the various types that you
mentioned, that has some characteristic that only this one type of article
has, and none of the others

you could split that field off into its own table, but it's often better to
add that field to the common article table, and simply let it be null for
all the other types of articles

simplicity in design might result in extra application logic (e.g. testing
when a field is null) but not as much extra logic as you get when the
different types are all in their own tables...

rudy
http://rudy.ca/