In the majority of cases, plugins modify options, metadata or leverage already existing tables (most commonly the posts table) to create new functionality.
Custom post types, custom taxonomies, image manipulation, galleries, short-codes – none of these usually need their own database table.
This is because the database schema of WordPress (how the database is organised) lends itself well to development. The posts table can be used to store objects, and the postmeta table can be used to store additional information about these objects. In some situations, however, this becomes cumbersome and/or wasteful.
In today’s post we’ll take a look at the pros and cons of new database tables, how to determine their structure and how to create them in WordPress.
Considerations for Custom Tables?
Whether or not you should use a custom table for your plugin comes down to two factors: the structure and the amount of your data. I would argue that you only need a custom table if your data structure is radically different from the standard post model and you have large amounts of it.
To prove my point let’s start thinking about a plugin that uses Google analytics to create and store weekly reports, which consist of a number of data points. You could approach this in three ways:
A Custom Table
We could create a simple custom table which stores each week’s analysis on one line. A row in the database would contain an identifier, the date of the analysis, the number of posts read, links clicked, which country contributed the most visitors and so on.
Post and Postmeta
We could create an “analytics” custom post type and use that to store the ID and the date of the analytics. We could then use post meta to store the individual data points.
A Simple Array
Instead of using posts or a custom table we could simply use a single option in the options table. This would be an array, members of which would be the individual weekly numbers.
Since we are gathering data weekly this really isn’t a large dataset. Sure, it would be convenient to have a table that fits our needs exactly, but since we don’t is it worth polluting our nice WordPress database with a table that will only be used once a week? I would say no, especially because our data fits in well with the key-value approach of postmeta tables.
Choosing between the posts table and the simple array is not so simple. If our example only stores a year’s worth of historical data, an array may be a perfectly valid approach. At the outset it will contain 52 members, which are easy enough to manipulate.
If data collection goes on and on I would consider the post and postmeta approach, simply because we don’t want to load and manipulate a 260 member array after year five.
“In the real World lines are sometimes blurred, it’s not so easy to decide which path to take.”
Now, let’s change just one parameter of the example and see if anything changes. Let’s collect data every minute instead of every week. There are 525,948 minutes in a year, which means that if we use a simple array, by the end of year one it would bulge to having over half-a-million members. This is clearly not an efficient use of our database as it would take ages to compute anything.
The post and postmeta solution would bump into the same problem. While WordPress is optimised, at numbers of this magnitude if you make the slightest optimisation blunder your website may come to a grinding halt, not to mention how user searches may suffer from speed issues on your blog (since posts and all analytics would be stored in the same table). Keep in mind that your postmeta table likely contains 10+ data points for each analysis so your meta table would receive 5,259,480 rows a year.
This is a situation where a custom table may be appropriate. While this table would also be populated with half a million rows a year it would be separate from your other content. Since data points can be added as columns (or can use a dedicated meta table) you can perform operations a lot faster.
In the real world lines are sometimes blurred, it’s not so easy to decide which path to take. To help you along, here are some more concise pros and cons of creating custom tables:
The Pros
- You can schemas to suite your data structure exactly
- You do not have to use two database tables to store data
- You have control over field types and limits
- Your data is well-separated from other aspects of WordPress
- Exporting data may be easier in some cases
- Your application may scale better
- Otherwise complex queries can be made simpler
- Your data may be a lot clearer
The Cons
- You may be cluttering the WordPress database
- Manipulating table data is more difficult
- You will need to create your own UI
- You may be more prone to SQL errors and attacks
- You won’t have access to a lot of functions
- You need to maintain your database, perhaps throughout multiple versions
- You will need to do more on plugin activation, deactivation and uninstallation
With that in mind, hopefully you can decide whether or not a custom table is what you need. If it is, read on, I’ll show you how to create one the WordPress way.
Creating A Database Table
Database tables should be created upon activation. This can be done by plugin and a function into the activation hooks using the following method:
This function will run when the plugin is activated by the user. If you want to learn more about this take a look at our tutorial on WordPress plugin activation, deactivation and uninstallation hooks.
We’ll use the function we’ve just hooked to add our database table using the dbDelta() function. To use this function we will need a database name (that uses the WordPress table prefix), the database collation and an SQL query. The following example shows how the database is created, taking inspiration from our website analysis plugin:
Initially we take our collation from the one set in the WordPress config file – this is stored in the $wpdb variable – and we retrieve the prefix from there as well. We use the prefix to create the final database name. Using some SQL formatted according to certain rules we create a database table. We then include the file which contains the dbDelta() function and then execute it which will create our database.
Formatting Our SQL Query
There are a number of rules we must follow when formatting our SQL query. These are the following, taken from the Codex article.
- You must put each field on its own line in your SQL statement.
- You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
- You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
- You must not use any apostrophes or backticks around field names.
- Field types must be all lowercase.
- SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
These are imposed by the dbDelta() function, not SQL itself of course. This function needs to figure out differences between database schemas in order to efficiently and safely update tables when needed. As such, SQL must be formatted in a way which allows the function to ‘digest’ it easily.
Updating Database Tables
Over the course of time you may need to add additional features to your plugin. I only added views and clicks above, how about adding average page view time in minutes as well? That would require a new column and this is where dbDelta() comes in handy.
Before we do anything we should make sure we’ve added our version number to our plugin. This will help us determine when database changes are necessary.
Let’s assume that throughout the 1.x versions nothing changed within the database schema. At version 2.0 we add a column. Here’s how this could work:
I’ve added a single column, the blog_id which would help us make this plugin work for multisite installations. As you can see I’ve detected if the currently used database version is lower than the plugin’s version. If it is we use the same format to simply add a column. The dbDelta() function takes care of all the changes for us, we just need to supply it with the proper database schema.
Conclusion
Creating your own tables is usually not required. When it is however, the dbDelta function allows us to create modular, flexible and easily maintainable tables.
If you ever find yourself in need of a table you should always take care to use this approach because it is the only way to accomplish custom tables in a WordPress-friendly way.
If you know of a plugin which adds its own database tables let us know in the comments below and perhaps we can check it out to see if it does it correctly!
Tags:
