Thinking about making your own WordPress plugin? When designing a plugin that needs to store a lot of data, it can be tempting to use custom tables in the database. WordPress comes with a lot of functionality for handling custom tables, though the alternative is using WordPress’ equally feature rich “custom post types”. This post dives into some of the pros and cons of using your own custom tables in a WordPress plugin based on my experience building Registrations for the Events Calendar Pro.
The pros of using custom tables can be summed up with the word “custom.” As the name implies, custom tables can be created specifically with the way your plugin needs to store data in mind.
Pro 1 – Complete Control of Your Structure
The most significant reason to use custom database tables is the ability to design your own schema. This allows for designing something that makes sense for your specific use case instead of trying to fit your use case to the existing structure of the “posts” and “post_meta” tables.
For example, the Registrations for the Events Calendar Pro plugins stores data submitted in a registration form for an event as well as payment information if the event has a cost. The bulk of this data can be stored in two rows in separate tables, an “entries” table that records the registration ID, date of registration, event registered for, and other details, and the “payments” table that records the payment ID, amount, registration ID associated with the payment, etc.
Now imagine trying to record this data into the “posts” and “post_meta” table. It makes some sense to store each registration as its own post as a “registration” post type, however, the best way to design your data storage system gets muddy after that. Should the “payment” data also be its own post type? Is it better to store “payment” data strictly as “post meta” of the “registration” post? Maybe the “payment” data for a registration could be a child post of the main registration.
Each of these options using custom post types has its own limitations. You also have to keep in mind that your plugin may add new features, and thus, need to build upon the existing ways data is stored. With your own custom tables, this can be as simple as adding another column or maybe another table to handle new data. You might need to take several complex turns to get the custom post types method to accommodate your new feature. It could even require rebuilding the whole storage system from scratch.
Pro 2 – Simpler Queries
To be fair, the WP_Query class in WordPress is very easy to use to retrieve most complex pieces of data. However, creating any kind of query from scratch for data stored in custom post types can get very complex in a hurry.
Using the Registrations for the Events Calendar Pro plugin again, consider the example of getting the total number of guests for all events. Using custom post types, the query might look something like this:
SELECT SUM(meta_value) FROM wp_postmeta AS m LEFT JOIN wp_posts as p ON m.post_id = p.ID WHERE post_type = 'rtec_registrations' AND meta_key = '_RTECguests'
In a custom table, this could be as simple as this query:
SELECT SUM(guests) FROM wp_rtec_entries;
Now consider something like an ecommerce plugin where you might need to query a customer, combined with an order, combined with a product. The complexity of your queries builds quickly which might lead to more bugs and more time trying to fix them.
Pro 3 – More Intuitive and Semantic
Along the same vein of the previous point regarding simpler queries, using custom tables gives you the ability to structure data in a way that seems obvious and name things in a way that’s easily understood.
Consider a filing system for your personal documents. You want to store things like your car insurance statements, canceled checks, and your birth certificate/passport. Which of these systems makes more sense:
You have two main folders, one labeled “Document locations” filled with index cards and another labeled “Documents” which is a series of numbered folders. If you need to retrieve a specific document, the first thing you do is look in the “Document locations” folder. Here you find index cards (ordered by document type) with a corresponding number in the “Documents” folder to find what you’re looking for. For example, you would look for “Insurance Statement” in the “Document locations” folder, get the index of where your insurance statements are kept, then look through the “Documents” folder to find that number.
You have as many folders as document types. To find a specific document, you look for the folder that has the title of the document and find it inside that folder. For example, you need your canceled checks so you look under the “Canceled Checks” labeled folder.
System 1 is basically using custom post types. System 2 is like using custom tables. Much like our “guests count” query example, the custom tables example (System 2) is not only shorter to write, but is much closer to how a person would choose to organize his or her own data.
Using custom tables also allows you to name columns and tables in a perfectly semantic way. It’s easier to understand “the sum of the guests column in the registration entries table” than “the sum of the meta values with the meta key guests from posts that have the post type registration”.
Pro 4 – Privacy
WordPress does an excellent job not mixing up post types unintentionally. This does not prevent this possibility completely, however. For example, maybe you install a plugin that displays the latest post from any post type. Now you can set your post types to be excluded from queries to prevent them being used in this type of a plugin, but maybe the plugin developer decides to grab the data for the latest post by querying the database directly. Now your “registrations” post type might show up on the front of your website, with the data your last registrant submitted in his or her form. Yikes!
Using your own custom tables adds an extra layer of protection in that other plugins probably won’t know about them. Even if they do, it’s less likely that the plugin will inadvertently reveal data that could be private.
People build plugins for WordPress for a reason right? Most of the tough things should be handled for you. That’s where we get to the cons of using your own custom tables. Much like everything else in WordPress, custom post types are extensible, standardized, and battle tested.
Con 1 – Maintenance
Much like having a custom engine built for your motorcycle will mean your mechanic might not be able to help you maintain it, you won’t be able to expect WordPress updates to stay compatible with your custom database tables. As WordPress adds more features and the requirements of websites change, you will be responsible for making sure the decisions you made for your custom database will still work.
Con 2 – Compatibility with Other Plugins or Multisite
The magic of WordPress is in its endless extensibility. That’s why you decided to create your own plugin right? Part of this magic relies on other plugins and themes doing things the “WordPress way.” Database optimizing plugins or tools may be less helpful or completely ignore your plugins custom tables.
Multisite can also prove to be a challenge trying to get your custom database tables to work smoothly. Typically WordPress will create a “posts” and “post meta” table for each individual multisite. There are also built in functions to deal with storing data properly relative to what sub-site is being used. Your custom tables will not be able to work with this system easily. You may need to add an extra column to the table to track the multisite installation being used or possibly even create (and take on the headache of maintaining) database tables for each mutlisite sub-site.
Con 3 – Security
Considering how large the project is and how many sites it’s active on, WordPress does very well with security. With the fairly recent addition of automatic security updates, any potential security issue with the WordPress database will be taken care of for you and your plugin users automatically if you elect to use custom post types to store your data.
This doesn’t mean that you can’t create very secure custom tables yourself. Just be sure to be diligent about sanitizing any user input, escaping variables before adding them to queries, and protecting who can write to the database and under what conditions.
Con 4 – Speed of Development
I would consider this the biggest disadvantage of using your own custom tables. You basically have to recreate many functions and features WordPress has built in for creating, updating, reading and deleting data from the database. This is precisely why you use a framework in the first place, to take care of the bulk of the heavy lifting when it comes to adding a common feature (like storing data).
For example, in the Registrations for the Events Calendar Pro plugin, the form builder uses custom tables to store data about the forms and the form fields as well as how they relate to each other. The total development and maintenance of this feature is going on 30+ development hours. In contrast, the email message builder feature uses a custom post type and has taken roughly 2 hours of development time.
To be fair, the email message builder makes a lot of sense as a custom post type since it uses the WordPress editor and shares many of the common elements of a post. However, in retrospect, the form builder (and form fields builder) could just as easily have its data stored as a custom post type and work fairly smoothly.
Need help with storing data for a project? I might be able to help. Contact me on the contact page.