[wp-hackers] Database structure Wordpress

[Otto]

On Thu, Dec 3, 2009 at 10:55 AM, Mike Schinkel
<mikeschinkel at newclarity.net> wrote:
> I agree on this point because Matt is (sadly) adamantly against it.

I used to agree with you.

Then I worked with it for a while and built some custom systems based
on similar principles.

Now I agree with Matt. :)

> However, you are making assumptions that are invalid; there are other ways to do it which is what but Drupal CCK and Pods for WordPress do; a central posts table and then specific tables for the unique fields needs for each post type.

How is this different than the existing metadata system, really? We
have postsmeta which can hold arbitrary fields associated with
arbitrary posts.

>> Metadata as columns doesn't make any sense either.
>
> I don't follow what you mean here.

In the original post, he discussed making a metadata key/value pair
into a new column in the posts table. For example, if you wanted to
add custom_data=bob to a post, then it would alter the table to have a
custom_data column and would put "bob" in it for that post.

> Actually, having tables like follow works quite well for performance:
>
> SELECT p.*, c.* FROM wp_posts p INNER JOIN wp_custom_products c ON c.post_id=p.id

Again, we have this. See postmeta, commentmeta, usermeta, etc.

> Unless you are talking about things like Map/Reduce or simply carrying redundant data across several tables to improve performance, I'm afraid I'm going to challenge you for concrete examples here that are backed up with something other than opinion.

Well, simply think about it a minute. Joining tables to obtain data is
slower than not joining them, obviously. So for something like the
posts table, you really only want data in it (as columns) that you
will always need, every time. Author, for example, is something you
usually will display on the page, so you really always needs it and it
makes sense to retrieve it, every time.

But, you don't always need custom_data=bob, do you? So instead of
mixing that into the main posts table, it's better to have it as a
meta value in a separate table. Indexed on the post id and the key
name, for quick lookups. Then, you can get that meta data only when
the program actually asks for it, on demand, sort of thing. Saves you
a lot of time since you're not getting it every time. If you were to
merge it into the main table, then you'd spend a lot of time getting
"custom_data = null" back from the database, when you didn't care
about the custom_data column in the slightest.

-Otto
Sent from Memphis, TN, United States