Change the WordPress Database Schema?

For those developers who are coming to WordPress from other platforms such as .NET or other major database driven system where they are responsible for devising their own database schema, one of the problems that’s often seen in the WordPress-world is this desire to create sets of tables that may or may not interface with the existing WordPress tables.

Just as it takes time to learn the WordPress event-driven paradigm from, say, Model-View-Controller, or something else, it takes time to make sure you fully grok the stack on which you’re working.

And starting at the foundation of WordPress is the underlying database schema.

The WordPress Database Schema

No, this is not a post that outlines and details the schema for the WordPress database – this has been covered in detail in a number of other posts let alone the Codex which provides a really great example of how the database is laid out.

The WordPress Database Schema

The WordPress Database Schema

This isn’t to say that you should never create your own tables – there is a time and a place for that, but that’s not the point that I’m trying to make.

When it comes to introducing new functionality, some love to jump the gun and begin thinking about a set of database tables that they can relate to the existing system in order to create whatever feature or system they are looking to create.

But before doing that, take a step back and ask: Can the WordPress database schema already support this?

For those who are just getting into the application, it may not feel like it, but remember that the features that we’re used to seeing on the front end are more or less abstractions for the underlying data structure.

Case in point:

  • Posts are an abstraction for a content types
  • Categories and Tags are abstractions for taxonomies
  • Users are abstractions for accounts

And each of the above points (save for taxonomies) have their associated meta data which is extremely flexible in the amount of information that you can keep within the application.

To that end, make sure that you’re familiar with the actual database and the capabilities of the API before attempting to create your own tables and relating them to those that already exist. Instead, you may actually be creating more work for yourself and your team.

Not Always the Case

This isn’t to say there aren’t times when additional tables aren’t needed. Take, for example, what the guys are doing over at Rocket Genius – they create their own database tables for Gravity Forms – and rightly so.

They are introducing an entirely new type of data structure into the system. Thus, they need a new type of data store in order to manage all of the information that they’re keeping.

But here’s the the thing: In addition to those database tables, there’s an API that allows developers to easily read and write data – and there’s where the difference lies.

If You Are Going to Create More Tables…

Here’s the thing: The existing WordPress API makes it really easy to access the existing tables through a rich set of API calls. Regardless of if you’re looking to call into the taxonomy table, the meta table, or what have you, it’s easy to do so programmatically.

And although WordPress does allow you to interact directly with the database using $wpdb, that doesn’t mean that it should be your go to method of reading or writing data.

Instead, look first to see if an API function exists.

Furthermore, if you are going to be introducing tables – and there are times where this is appropriate (as we’ve seen) – make sure that you’re going to provide developers with the APIs they need to access the data your application is maintaining; otherwise, we’re left having to write raw queries against the database. This leaves room for a number of potential errors, security problems, and so much more.

So whatever the case may be: Use existing APIs, or introduce them for developers that will be using your work.

It makes working with the core application and managing the data not only that much more powerful, but also that much easier.

2 Comments

Good thoughts. When I first started WordPress development, I definitely made a lot of mistakes.

There are really two sins in my mind:
– Egregious use of custom tables
– Egregious lack of custom tables

I think the project should primarily determine whether or not custom tables are used, and how. The concern over the excessive use of custom tables turned into a manta that custom tables are never justified, which I think took the focus off the problem being solved in a negative way.

In my opinion, that bias created a lot of plugins with limited scalability and performance as developers performed gymnastics to stuff data that doesn’t fit into the existing schema, out of fear of being frowned upon by the community.

Best example: How many WordPress e-commerce plugins store orders as a post type and all related transaction data in post meta? Good luck building a report on that data when you have a million transactions. No one would ever design such a system like that from scratch unless they felt compelled to fit a square peg in a round hole.

Just my two cents.

    I think the project should primarily determine whether or not custom tables are used, and how. The concern over the excessive use of custom tables turned into a manta that custom tables are never justified, which I think took the focus off the problem being solved in a negative way.

    Yeah, there’s definitely a time and a place and a problem set for it, but it’s not as widespread as some people may think it should be.

    But I think experience teaches that, so it’s a matter of really just spending time with working on plugins, studying other plugins, and so on to grok it properly. Even then, it could be wrong, but it’s better than starting from nothing.

    In my opinion, that bias created a lot of plugins with limited scalability and performance as developers performed gymnastics to stuff data that doesn’t fit into the existing schema, out of fear of being frowned upon by the community.

    That’s when it’s gone too far, but performing those gymnastics may be not using the API properly. Or just not having the tables they need.

    Who knows without seeing the plugin :).

    Good luck building a report on that data when you have a million transactions.

    Great example.

Leave a Reply

Name and email address are required. Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>