Say I need to store information about the types of planes a user has. for each plane, I need to store 6 different numbers (average crew size, fuel used per flight, etc). There may be hundreds of customers, with perhaps no more than 5 to 10 plane types each.
which would be faster to query-joining a 3 column table (plane_ID, varName,varValue) with X rows per customer, or a 7 column table (plane_ID, aveCrewSize,...) with 1 row per customer?
I've heard that disk-seek per row is the biggest bottleneck, but on small test tables the first was faster than the second, even after the queries were cached
Part of what I'm doing is turning an Excel spreadsheet model into a database for an online application, and the spreadsheet is practically complex enough to be sold as a product on its own. As such, there's a lot of table-within-a-table stuff that's making me want to just put a bajillion tables in the database and use JOINs everywhere (which I understand is also bad for overhead). Would it be better to have a single table of constants or is removing constants not really a beneficial normalization? I'm stuck in MyISAM b/c this is on a shared host, so I don't really have the benefit of foreign keys or such things.
PS- I saw the Entity-Attribute-Value initially when reading a book on designing a site using the Zend Framework for PHP- I guess books aren't all that reliable after all