WooCommerce: The Need For Custom Order Tables

Scaling WooCommerce stores while ensuring optimum performance and pushing a store live from staging while keeping all the orders and customers intact are significant problems stores face, even today. 

Each time a consumer places an order on a WooCommerce store, it adds dozens of order data and entries to the WordPress postmeta database table – which creates a major problem for stores that handle huge volumes of orders and eventually results in performance degradation. 

Hence, WooCommerce received several requests over the years to resolve this issue and make separate and custom tables for each WooCommerce entity – without affecting the site and existing extensions. 

And finally, WooCommerce is now working on the custom order tables implementation to solve the bottleneck issues and ensure more store stability. 

In this article, we introduce you to the WooCommerce custom order tables – what is it, the new tables added to the structure, and how they can benefit your WooCommerce store. Let’s begin!

Before WooCommerce Custom Order Tables

As eCommerce stores grow, they need to store more data and customer information in their databases, which was a major problem back when WooCommerce started. And initially, WooCommerce was never meant to store or handle such a vast amount of data. 

Things changed when WooCommerce introduced a new version of WooCommerce 3.0 and encouraged developers to use CRUD objects. CRUD stands for Create Retrieve Update and Delete – the common database operations for data. It opened up many possibilities for developers – including abstraction of data from its implementation and providing an abstraction over direct database access. 

You’re more likely to still use wp_post and wp_postmeta database table structures to store different data, including orders, coupons, and products; however, the problem begins when it’s time to scale your WooCommerce store, and you receive hundreds of customer orders per day. 

Each placed order calls update_post_meta() – which means separate queries each time to insert data with no solution for a bulk insert for post meta. This is where the idea of custom tables comes into the picture. 

You can move the data out of the postmeta into the custom tables, which significantly reduces the total number of order insert queries by 80% – thus, minimizing the order volume bottleneck. 

Let’s learn more about the features and what’s new with the WooCommerce custom order tables. 

What’s New With The WooCommerce Custom Order Tables? 

Custom order tables is a solution that provides an easy-to-understand and solid database structure – specifically designed for eCommerce needs. It uses the WooCommerce CRUD design to store order data in a single and flat table – optimized for WooCommerce queries with minimal impact on the store’s performance. 

Bringing custom order tables to the WooCommerce core improves these three essential properties for eCommerce stores. 

  1. Scalability

As discussed earlier, the rise in the number of customers and customer orders increases the load on your store’s database – making it inefficient to handle customer order requests and deliver a quality user experience. In addition, managing one database with thousands of rows posed a serious problem. 

With WooCommerce custom order tables, you get dedicated tables for data like orders and order addresses, and thus dedicated indexes, fewer read/write operations and fewer busy tables. This feature enables eCommerce stores of all shapes and sizes to scale their business to their maximum potential – without expert intervention. 

  1. Reliability 

WooCommerce custom order table will make implementing and restoring targeted data backup easier. You’ll no longer need to worry about losing orders, inventory numbers, or client information with reliable backup in custom order tables. 

In addition, it’ll also facilitate implementing read/write locks and prevent race conditions. Moreover, WooCommerce will stay untouched in case you want to restore WP. 

  1. Simplicity 

You no longer have to go through a single huge database to locate underlying data and WooCommerce entries. 

Instead, you can easily browse through the separate tables and easy-to-handle entries, independent of the WP posts, to find data or understand the table structure. It also lets you easily develop new plugins, implement designs for shops and products, and modify WooCommerce with more finesse. 

Backward Compatibility 

During the rollout, WooCommerce aims to provide WooCommerce custom order tables as an opt-in feature instead of a default update. 

This backward compatibility will give enough time to the existing eCommerce stores to work with WooCommerce, adopt the new database structure and make their shops compatible with the new feature. 

Thus, before your developers can take complete advantage of the table structure, they’ll be given enough time to work around the feature. WooCommerce also plans to publish regular upgrade guides to support custom order tables adoption. 

Requirements For WooCommerce Custom Order Tables 

Here are the basic requirements to use WooCommerce custom order tables: 

  • You must also have the ability to run the WP-CLI commands in your WooCommerce environment if you wish to migrate your existing order data. 

This command will be critical in migrating order data from post meta into the new order tables. 

General Table Structure Of WooCommerce Custom Order Tables

Now that we’ve seen how custom order tables can benefit your eCommerce store, let’s look at the table structure and what’s different with the new structure. 

Here are the new tables you can see in the general table structure for orders. 

  • Core tables: As the name suggests, core tables will store WooCommerce core defined order properties. These properties include most of the meta table fields and fields from the post table. 

WooCommerce considers this group of tables to be the central goal of the custom order tables project and aims to migrate data into the core tables.

  • Meta table: Meta table acts as a storage for any order-related one-off values. You can also use this table as a fallback location to migrate metadata, which isn’t part of the core order model. 
  • Additional plugin tables: Currently, there isn’t a standard solution to store plugin data and information, and several plugins end up storing data in the post tables. The new order table structure brings a solution for WooCommerce plugins that require storing their frequent data for each order and shipping. 

While it’s not a mandatory requirement – it’s the perfect solution if your store uses multiple plugins to ensure reliable store performance and uptime during peak traffic. WooCommerce also plans to build helpful tools and guides to make building and migrating data into these tables a breeze. 

Here’s the proposed table structure for order data: 

Here’s a list of the new tables that are part of the metadata and core tables with their purposes: 

  • wp_wc_orders: This table accommodates most of the current core fields and crucial meta keys. 
  • wp_wc_order_addresses: This table will store addresses associated with orders, including shipping and billing addresses. 
  • wp_wc_order_operational_data: WooCommerce will use this table to store flags and fields they use to maintain the internal order structure. They’ll keep this field separate from the core order table because of the more frequent changes in these fields. 
  • wp_wc_order_meta: This table will function similar to the wp_postmeta table; however, it’ll also allow extensions to store the one-off data related to the orders. 
  • wp_wc_order_notes, wp_wc_order_notes_meta: Previously, wp_comments and wp_commentmeta stored order notes. But, these new tables will now handle and store the order notes. 

Thus, WooCommerce encourages eCommerce stores that use a lot of plugins and store huge amounts of data in the post meta table to use this update and leverage dedicated custom tables. 

In addition, it also encourages developers to not use the wp_wc_orders_meta for common order-related values, as it’s a better backup solution to store extension data that hasn’t been migrated yet. 

You can also view the individual new table’s structure here

FAQ: WooCommerce Custom Order Tables 

Here we answer some frequently asked questions about the WooCommerce custom order tables. 

  1. When will the custom order tables feature be released?  

The WooCommerce development team aims to complete and release the initial feature implementation by Q3 2022. Currently, the team is tracking the project on GitHub

  1. How is the table structure going to be? 

Here’s the proposed table structure design for order data by the WooCommerce team. 

  1. Does it also help include more email structures? 

Yes, with the increase in the email field length from 100 to 320 – you can accommodate most of the email structures. 

  1. Will I be able to store fields I couldn’t before? 

Yes, the feature also drops fields that need computation and weren’t stored previously, such as product_tax_amount and product_total_amount. 

Conclusion 

WooCommerce custom order tables will play a major role in scaling large eCommerce stores and solving the most important performance bottleneck issue: a high volume of orders. 

While the project is still under implementation and set for release in Q3 2022, you can keep up with the project with the official GitHub project board.

You can also expect regular updates, development notes, and announcements on this blog as the implementation progresses and WooCommerce makes the updates available.  

David Mainayar

Co-Founder at PeachPay and with a background in economics, David has been obsessed with WooCommerce ever since he discovered its transformational potential for the entrepreneurial underdog. His mission is to help level the playing field for small and medium-sized enterprises as well as independent retailers. David loves travelling to central Europe, reading books by N.N. Taleb, and learning about ancient Roman and Napoleonic history.

Questions? Feedback? Support? Leave your Comment Now!
_____

If you are writing code, please wrap it between shortcodes: [php]code_here[/php]. Failure to complying with this (as well as going off topic, not writing in English, etc.) will result in comment deletion. You should expect a reply in about 2 weeks - this is a popular blog but I need to get paid work done first. Please consider joining BloomerArmada to get blog comment reply priority, ask me 1-to-1 WooCommerce questions and enjoy many more perks. Thank you :)

Your email address will not be published.