[wp-hackers] Database structure Wordpress

[Otto]

You're not going to get a whole lot of support for this idea. Mainly
because it's been demonstrated that such a design is less efficient
and slower than the current scheme of keeping things together.

Let's say you have a table for each post type. What happens when you
need to pull from more than one post type and interleave them together
based on, say, date? Now you're pulling from two tables. And since you
want metadata to be columns, they don't match each other either. So
you need two SELECTs and then PHP code to mix them up. Whereas if you
keep all posts in the same table with a post_type column, it's one
SELECT and no special PHP.

Metadata as columns doesn't make any sense either. The only reason to
have a column is if every post, or most of them, will have that data
filled in. Also, having a column usually means pulling the data from
that column every time, else you'd have to requery for it later
anyway. And if you have to requery for it anyway, then it's more
efficient to store the data in a separate meta table, using key value
pairs. Indexing makes such a query faster, and the space requirements
are much less since you don't have extraneous columns.

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.

WordPress's current design is geared towards very high performance,
and pretty much ignores "correct" design. There are a few improvements
that can be made with regards to indexing (and many of them have been
made in 2.9), but redesigning to be "correct" simply ain't going to
get a lot of traction, sort of thing.

-Otto



On Wed, Dec 2, 2009 at 7:31 PM, Florian Woegerer <nexflo at gmail.com> wrote:
> The Database structure of Wordpress should be rethought if Wordpress is going to move to a more and more CMS's based system. (last week's CMS Award seems that this is indeed the case)
>
> In times of PDO, Doctrine etc. creating a sweet Database layout shouldnt be the biggest issue no more?
>
> Why not update the sql tables names when creating a new page type? i.e. we create a new post/page type titled "cars"...why not create a new database table entitled cars with all default fields in there. Custom fields would simple add a new Field/Column into that table. in this way we would have an extremly clean database design, which other applications can tap into easily as well. This would result in a high performance database layout and offer a great new way to design flexible, scalable applications.
>
> The benefit would be extremely great too all powerusers/cms users etc. and normal "blog" users wouldn't feel a thing as for them nothing changes. (sure the huge amount of plugins would need a wrapper till things are updated)
>
> Wordpress doesn't get bloated, but just more mature.
> Using convention over configuration would allows wordpress to tap into existing databases which have an incremental id and use that data instantly. (if the database layout fits a certain style and some additional fields (published, author etc. are added)
>
> would love to hear some thoughts on this
>
> Thanks Florian
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>