[wp-hackers] Database structure Wordpress

[Mike Schinkel]

On Dec 3, 2009, at 2:36 PM, mrmist wrote:
> In message <161617690912030712v5edc0858uc8b6d9bfaef26260 at mail.gmail.com>, Otto <otto at ottodestruct.com> writes
>> 
>> It's all well and good to have good database design, but the
>> difference is that good design does not equate to "faster" in real
>> world applications. The goal of "high performance" is often in direct
>> conflict with correct relational database design. Took me a long time
>> before I figured that one out.
> 
> One table per type in the way that's been described is not good relational design in any case, it's breaking normalisation rules.  If you were to introduce a table for "car" and a table for "van" and they ultimately contain the same information columns, that's poor design, and will lead to you having to do work in the code base which could otherwise have been done more efficiently through set based SQL.

For clarity I don't think what you are suggesting is what was proposed (or maybe it was, but it's not what I'm proposing):

Instead of two tables "car" and "van" you'd have three tables "vehicle", "car" and "van."  Vehicle would have shared content and then car and van would have only the fields that differ.

To use an example more concrete for WordPress, let's say you have two custom post types, "Products" and "Companies."  You could end up with three tables to manage this with one-to-one relationships between posts and products and between posts and companies:

wp_posts
wp_custom_products
wp_custom_companies

To see this structure in action look at Drupal's CCK module or the PODs plugin for WordPress.  And it is relationally correct.

But again, this should probably be an option to the current metadata table approach for only if and when it is needed.

> For example, in that case, you'd also need another table that holds the names of all the sub-tables, and to fetch everything you'd first have to collect all the sub-table names with one query, then loop through N more queries for each sub-table, or pass through horrific unions in your database calls. It's just messy.

Ugh, not sure where you get that, but yes it would be very messy.

> The "multiple tables for the same thing" sort of design thinking tends to be favoured by programmer types more than database types.

FYI, I was a database guy before I was a programmer.  :)

-Mike