[wp-hackers] Wordpress as a CMS

[Peter Westwood]

On 21 Dec 2009, at 19:05, Mike Schinkel wrote:

> On Dec 21, 2009, at 9:34 AM, Brad Williams wrote:
>> I fail to see the difference between storing your data in a meta  
>> table vs. a
>> custom table?  After a few thousands records wouldn't your custom  
>> table
>> suffer the same consequences as a meta table?  Ultimately it comes  
>> down to
>> your server setup and what it can handle, not what table you store  
>> the data
>> in.
>
> Relational databases were optimized to work relationally not key- 
> value pairs.  Index performance slows down when dealing with large  
> numbers of records and the meta table approach typically adds an  
> order of magnitude more records. Each meta value is another meta  
> record you have to loop through to collect the data for a post. With  
> a high traffic website this might be significant.
>
> Meta records also store their "structure" with each record, i.e. the  
> "key" in the "key-value" pair.  Relational tables store the  
> structure only once so meta records are much more space efficient,  
> especially if the records are using integer primary and foreign keys.
>
> Another consideration is that certain types of join queries are much  
> more complex or even impossible with meta tables. Let's say you want  
> to do a query for posts that represent real estate properties and  
> have the three meta values type, price, and city:
>
> SELECT
> 	post.ID,
> 	post.ID,
> 	post.post_title,
> 	type.meta_value AS type,
> 	price.meta_value AS price,
> 	city.meta_value AS city
> FROM
>  wp_posts post
>  INNER JOIN wp_postmeta city ON city.post_id = post.ID
>  INNER JOIN wp_postmeta price ON price.post_id = post.ID
>  INNER JOIN wp_postmeta type ON type.post_id = post.ID
> WHERE
>  city.meta_key='city' AND city.meta_value='Atlanta' AND
>  type.meta_key='type' AND type.meta_value='condo' AND
>  price.meta_key='price' AND price.meta_value<250000
>
> That's four joins with 6 criteria on string keys and a more complex  
> query.  Now let's look at a custom table approach:
>
> SELECT
> 	post.ID,
> 	post.ID,
> 	post.post_title,
> 	prop.type,
> 	prop.price,
> 	prop.city
> FROM
>  wp_posts post
>  INNER JOIN wp_custom_property prop ON prop.post_id = post.ID
> WHERE
>  prop.city.meta_value='Atlanta' AND
>  prop.type.meta_value='condo' AND
>  prop.price<250000
>

In my personal opinion if I was designing a site which needed this  
kind of functionality I probably would not start with the WordPress  
platform to develop it.

I would however start with the BackPress framework because of all the  
cool WordPress like functionality it provides like User Managment, DB  
abstraction, object caching etc and I can have whatever urls I want  
and optimise the database structure to make the queries zippy.

Then when I wanted to do the content management bit of the site with  
maybe some pages and a blog I would use WordPress and integrate the  
Users/Cookies etc so it was all seamless.

That way you get the best of both worlds - the performance and  
flexibility you want for the business side of the site and the  
familiarity of the content management side.

You could even integrate the management of the content for the  
"Properties" into the WordPress backend using a plugin as well so the  
user would only need one admin panel and one set of training.

I like to use the right tools for the job rather than fight against  
the one that doesn't do what I want how I want - I find it works  
better that way

If you have any BackPress questions then please let me know off list  
and I can point you in the right direction.

Cheers

Peter