WordPress has grown to be a huge portion of the internet and therefore has been proven to scale really well in the right conditions.
At its core, it’s just a PHP application interfacing with a MySQL database, but sometimes you need additional functionality with the database.
Every time I find myself storing lots of records in the options table, I stop and consider if I should create my own custom database table.
Sometimes a Plugin Outgrows The Default WordPress Tables
Some signs that your plugin might be outgrowing the default tables:
- Database queries for custom data are too slow
- The data doesn’t fit any existing content types
- You will be storing a really large number of records (over 10,000)
- You need to query all parts of a record (it shouldn’t be serialized)
If you’re nodding your head with all those issues and requirements, then you should probably create your own custom table.
Here are some other questions you need to consider when deciding to create your new table.
Management Overhead
I’ll be real.
Database management isn’t fun.
Once you step down the road of managing your own database tables, you might agree.
Have you ever seen this screen after you update WordPress core?
That’s WordPress’ way of letting you know that they’re about to update the database because a table has changed structure and they need to migrate your data.
If you really need a database table, consider it carefully because if you ever decide to change the structure of your table to add in a little extra information, you’ll have to handle that for every single person that updates to the new version.
I don’t mean to convince you away from adding your own tables. Really, adding your own tables opens up a world of possibilities, but you should be absolutely sure you want to take on the overhead.
How Will You Control Your Table Structure?
Custom database tables have a certain structure, or schema, that outline how they’re laid out.
Often, in some PHP frameworks, there are ways to lay out the schema of tables in code so that when the program runs, you can interact seamlessly with the data.
Laravel handles this problem in migrations, where tables can be created through a set of instructions that adds, removes, or edits parts of the database.
Unfortunately, WordPress doesn’t have a great way of handling this.
It provides a global $wpdb to run queries and interact with the database directly, but no formal way to store database structure within your code (and therefore in version control).
For a better way, you’ll probably want to look into libraries written by some really smart people to handle this problem:
Both of these are great libraries, but either way, you’ll need to decide how you want to store your table structure within your code so it can be replicated across environments consistently.
How Well Do You Know SQL?
The libraries I mentioned above help with creating and managing the structure of your custom table, but you’ll still need to know how to write the queries that create the structure.
You’ll need to know:
- How to create a table
- What the different data values are and when to use them
- SQL’s very specific syntax
- Collation and Charsets
There are tons of resources to learn SQL and database design and I’ve spent a lot of my time digging in and learning from them.
However, is this something that you are interested in getting into?
Creating custom tables will require some maintenance and good design.
In Summary
Adding custom database tables can be powerful additions to your plugin. Especially when dealing with large amounts of data.
However, you definitely want to know what you’re getting into before you decide to take the leap.
Be sure your need for a custom table outweighs:
- Database Management overhead
- Setting up a custom system to save table structure
- Learning specific SQL syntax and writing your own custom queries
Consider your options, and make the choice that’s right for you.
If you decide not to create a table, that’s great! There’s no shame in that!
If you DO decide to create a custom table, I hope I provided some guidance around sticking points I’ve experienced over the years.
That’s all for this now, see you next week!
