Over the years, we have frequently received requests to implement custom tables for various WooCommerce entities (orders, products, coupons, etc). This is something that we also wanted to do for some time, but have shied away from actually committing to implementation so far largely due to the complexity of making this type of change, without adversely affecting existing sites and extensions.

With this post, we announce with much excitement that we have started working on an implementation of custom tables for orders. This is a complex project, so we are going to reach out often to you for feedback as we continue with the implementation.

Background and scope

Back in 2017, WooCommerce 3.0 was released. This release included the introduction of CRUD classes that provide abstraction above direct database access. This was done to enable the possibility to migrate to different data stores for objects that use the CRUD layer later on, such as products, orders, or coupons. 

We still utilize wp_post and wp_postmeta table structures to store this information for WooCommerce stores even with the CRUD layer. While these WordPress-provided APIs and tables have served us well over the years, we now want to take a step further with a rock-solid and easy-to-understand database structure that is intentionally designed for commerce needs.

There are 3 main properties we’d like to improve by bringing custom order tables to WC core:

  • Scalability – by having dedicated tables (and therefore dedicated indexes, less busy tables, less read/write ops etc), we aim to enable shops of all sizes to scale further without the need for expert intervention.
  • Simplicity – being independent of WP posts should help in finding where underlying data lives, understand its structure and allowing developers to modify WooCommerce with more finesse.
  • Reliability – it should be easier to take and restore targeted backups, implement read/write locks and prevent race conditions.

We aim to complete the initial implementation by early Q3 2022.

Backward Compatibility

For this project, we expect that some work may be required by extensions and custom code developers to take full benefit of the new table structure. As we go further along in the implementation, we plan to publish upgrade guides to support the adoption of custom order tables.

During the rollout, we aim to keep this feature strictly opt-in in the beginning, giving everyone enough time to make their shops compatible.

Database schema

This is the first draft of the database that we would like to propose to build out custom tables. Please add any feedback that you may have as a comment to this post.

General structure

We envision the general table structure for orders to be consist of:

  1. Core tables: As implied by the name these tables will store properties of orders which are defined by the WooCommerce core. This includes fields from post table, as well as most fields from the meta table. This group of table is the central goal of the project, and we will be migrating data into these tables.
  2. Additional plugin tables: In the new structure we recommend that WooCommerce plugins who need to store frequent data for every order, ship their own tables as well. Currently, there is no set standard, so many plugins end up storing data directly into the post tables, just like us.
    We also plan to build guides and tools to facilitate building and migrating data into these tables as part of this project. This is not a requirement, but it’s recommended to keep the shops using those plugins performant.
  3. Meta table: Even with core tables and recommendations for plugin tables, we are providing a meta table. This should act as a store for any one-off values that needs to be associated with orders. Further, we will use also this table as the fallback location to migrate metadata which is not part of the core order model.

With this table structure, we hope to make progress on our goals of scalability, reliability, and simplicity. As always, any feedback or suggestion to make this better is welcome.

Proposed table structure for order data

Tables that are part of core data and metadata are:

  1. wp_wc_orders: We accomodate most current core fields and important meta keys in this table.
  2. wp_wc_order_addresses: We will use this table to store addresses associated with orders (e.g. shipping and billing).
  3. wp_wc_orders_meta: This table is similar in functionality to the wp_postmeta table, and will allow extensions to store one-off data associated with orders.

As stated above, we encourage plugins that store a large amount of data in the post meta table to have a dedicated table instead. We will still have the wp_wc_orders_meta as a backup solution to store any data for extensions that haven’t migrated, but we encourage developers to not use it for common order-related values.

Tables’ structure

idbigint(20) unsigned, primary key, indexedUnique ID for order.
post_idbigint(20) unsigned, indexedPost ID, if the data is duplicated. This would be used for rollback if needed.
statusvarchar(20), indexedStatus for the order.
currencyvarchar(10)Currency identifier in which payment was received.
tax_amountdecimal(26,8)Total order tax amount
total_amountdecimal(26,8)Total order total, including tax
customer_idbigint(20) unsigned, combined indexed with billing_emailCustomer ID (if not guest)
billing_emailvarchar(100), combined indexed with customer_idBilling email for customer
date_created_gmtdatetime, combined indexed with statusTimestamp when order created
date_updated_gmtdatetime, combined indexed with statusTimestamp when the order or any of its property was last updated
date_paid_gmtdatetimeTimestamp when the order was first moved into any of the paid statuses.
date_completed_gmtdatetimeTimestamp when the order was last moved into completed status.
parent_order_idbigint(20) unsigned, indexedOrder ID of the parent, in case this order is a refund
has_refundstinyint(1)Whether the order has refunds.
product_tax_amountdecimal(26,8)Total product tax
product_total_amountdecimal(26,8)Total product total, including tax
shipping_tax_amountdecimal(26,8)Total shipping tax
shipping_total_amountdecimal(26,8)Total shipping amount, including tax
discount_tax_amountdecimal(26,8)Total discount tax
discount_total_amountdecimal(26,8)Total discount amount, including tax
fees_tax_amountdecimal(26,8)Total fees tax
fees_total_amountdecimal(26,8)Total fees amount, including tax
payment_methodvarchar(100)Identifier of payment method
payment_method_titletextTitle of payment method used
transaction_idvarchar(100)Transaction ID provided by payment gateway.
created_viavarchar(100)The identifier for order creation source (admin, rest-API, checkout, etc)
ip_addressvarchar(100)IP address used to create the order
user_agenttextThe user-agent string of the web client that is used to create the order.
woocommerce_versionvarchar(20)WooCommerce version which was active when creating the order.
prices_include_taxtinyint(1)For internal use. Whether prices were including taxes when purchased.
coupon_usages_are_countedtinyint(1)For internal use. Whether coupon usage is counted.
download_permissions_grantedtinyint(1)Marks whether download permissions are granted to order or not.
Proposed order core table schema
idbigint(20) unsigned, primary key, indexedUnique ID for order address record.
order_idbigint(20) unsigned, indexedOrder ID.
address_typevarchar(20)Type of address (billing, shipping, etc).
first_nametextFirst name
last_nametextLast name
companytextCompany name
address_1textAddress line 1
address_2textAddress line 2
date_created_gmtdatetimeDate of creation for this record
Proposed order addresses table schema
idbigint(20) unsigned, primary key, indexedUnique ID for meta record.
order_idbigint(20) unsigned, indexedCorresponding order ID.
meta_keyvarchar(255), indexedName of the key.
meta_valuetextValue of the record.
date_created_gmtdatetimeDate of record create
date_update_gmtdatetimeDate of record update
Orders meta table

Order notes

Additionally, we will also create dedicated tables to store order notes, instead of storing them in the wp_comments and wp_commentmeta, so that we can store them independently of post records.

idbigint(20) unsigned, primary key, indexedUnique ID for order note.
order_idbigint(20) unsigned, indexedID of the order.
typevarchar(255), indexedType of note (private, customer, etc).
authorbigint(20) unsignedThe ID of the user creating the note. Will be WooCommerce for system-generated notes.
date_created_gmtdatetimeTimestamp when the note was created.
contenttextContents of the note.
Order note
meta_idbigint(20) unsigned, primary key, indexedUnique ID for meta
note_idbigint(20) unsigned, indexedID of the note.
meta_keyvarchar(255), indexedName of the key.
meta_valuetextValue of the record
date_created_gmtdatetimeDate of record create
date_update_gmtdatetimeDate of record update
Order notes meta

Note that this draft proposal is not final and very likely to be revised as we get more feedback, do more research, and discover more information during implementation.

Next steps

We are using this GitHub project board to manage the project, you can follow the project and provide feedback as we implement it. Currently, we are working on preparing an MVP implementation so that we can assess and modify the project in more detail.