[wp-hackers] when to use custom database tables

[Kevinjohn Gallagher]

Morning Steve (and Eric),
Playing Devil's Advocate a little, and like Eric this is based on personal experience only, I think that Custom Tables are fine if:1) You ensure that they are well architected2) You need them3) You need to write custom SQL calls anyway4) You need an n-to-n structure
There is a significant performance hit when attempting to create a correlation between CPTs (especially with defining or mandatory 1-to-1 meta data), and the limitations of WP_QUERY and query_posts especially in ordering / classification via metadata.
To give a real world example, for one of our sites recently, we had to move away from multiple CPTs due to the overhead of the inter-CPT dependancies. It was a sport site, where we had Matches, Teams and Players - which sounds simple, but the amount of mandatory metadata we needed to poll / sort / identify people on added quite a large overhead. The move to Custom tables with Custom Queries (oh how I love you ORDERBY X desc, Y asc and curse the query_posts 1-to-1 version) has really made a huge impact on the site and its editors. We've kept all the "news" stuff as CPTs, e.g. Match Reports, which now query a really slick class that returns specific data as needed.
Something we WordPress developers appear to be bad at on the whole (at least from the "larger" plugins I've seen) is actually planning how our data structure is going to look and work from start to finish. I can't recommend enough spending time architecting what data you want on the front-end and the back-end, and literally drawing on paper how you'll get it out.
Like most things with WordPress, where possible you should try and stay within the confines of the system; but it's your website - if the system isn't 100% working for you, change the bits you need.
Just my two cents.
Kev

Kevinjohn Gallagher    
+44 (0) 7788 718382 
KevinjohnGallagher at hotmail.com 
http://www.kevinjohngallagher.com 
http://www.linkedin.com/in/kevinjohngallagher 




> Date: Thu, 8 Dec 2011 16:46:53 +0000
> From: steve at sltaylor.co.uk
> To: wp-hackers at lists.automattic.com
> Subject: Re: [wp-hackers] when to use custom database tables
> 
> Thanks Eric, makes sense. I don't have much of an idea of the scale of
> the site yet, but it's the first one that has made me wonder if it
> might end up benefiting from custom tables.
> 
> I think erring on the side of CPTs is probably a good idea, though,
> and I'm not 100% I need custom tables, so I'll probably keep it
> simple. I guess if the site is successful enough that CPTs queries
> start impacting performance, there will be budget for porting to
> custom tables :)
> 
> Cheers,
> 
> Steve
> 
> 
> On 8 December 2011 16:24, Eric Mann <eric at eam.me> wrote:
> > ... Disclaimer - this is only my opinion based on personal experience and
> > is in no way a "rule" ...
> >
> > The only time you should be using custom tables is when you need to index
> > particular data fields for your content and the indexing features of custom
> > meta fall short.  So if you wanted to build a geolocation database for a
> > high-traffic website where sorting/filtering/indexing on multiple fields
> > was vital to business (for example), you might consider using a custom
> > table or two to store data.
> >
> > Just remember, there are tradeoffs to custom tables - CPTs come with all
> > the richness and goodness of the existing WordPress API (WP_Query,
> > taxonomies, etc).  Once you start delving in to custom tables, you'll be
> > spending more time reinventing the wheel than actually using the data.
> >
> > So without detailed specs, my general guideline would be to only use custom
> > tables if the project *absolutely requires them*.  Most projects don't,
> > though most of us are convinced a custom table would be easier than a CPT
> > with copious meta fields.
> >
> >
> > On Thu, Dec 8, 2011 at 4:08 AM, Steve Taylor <steve at sltaylor.co.uk> wrote:
> >
> >> Hi,
> >>
> >> I've managed every project so far with custom post types and
> >> taxonomies. However, I'm starting on a large project where I wonder if
> >> it might be better suited to using custom tables.
> >>
> >> Rather than detail all my specs, I'm just wondering, does anyone have
> >> any good rules of thumb for when custom tables become preferable to
> >> CPTs?
> >>
> >> I can give specs if necessary!
> >>
> >> Thanks,
> >>
> >> Steve Taylor
> >> _______________________________________________
> >> wp-hackers mailing list
> >> wp-hackers at lists.automattic.com
> >> http://lists.automattic.com/mailman/listinfo/wp-hackers
> >>
> > _______________________________________________
> > wp-hackers mailing list
> > wp-hackers at lists.automattic.com
> > http://lists.automattic.com/mailman/listinfo/wp-hackers
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers