The plan for the WooCommerce custom order table - Speeding up and improving reliability : TranslatorHQ

https://developer.woocommerce.com/2022/01/17/the-plan-for-the-woocommerce-custom-order-table/

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:

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.
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.
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.

Tables that are part of core data and metadata are:

wp_wc_orders: We accomodate most current core fields and important meta keys in this table.
wp_wc_order_addresses: We will use this table to store addresses associated with orders (e.g. shipping and billing).
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

`wp_wc_orders`

Name	Detail	Description
id	bigint(20) unsigned, primary key, indexed	Unique ID for order.
post_id	bigint(20) unsigned, indexed	Post ID, if the data is duplicated. This would be used for rollback if needed.
status	varchar(20), indexed	Status for the order.
currency	varchar(10)	Currency identifier in which payment was received.
tax_amount	decimal(26,8)	Total order tax amount
total_amount	decimal(26,8)	Total order total, including tax
customer_id	bigint(20) unsigned, combined indexed with billing_email	Customer ID (if not guest)
billing_email	varchar(100), combined indexed with customer_id	Billing email for customer
date_created_gmt	datetime, combined indexed with status	Timestamp when order created
date_updated_gmt	datetime, combined indexed with status	Timestamp when the order or any of its property was last updated
date_paid_gmt	datetime	Timestamp when the order was first moved into any of the paid statuses.
date_completed_gmt	datetime	Timestamp when the order was last moved into completed status.
parent_order_id	bigint(20) unsigned, indexed	Order ID of the parent, in case this order is a refund
has_refunds	tinyint(1)	Whether the order has refunds.
product_tax_amount	decimal(26,8)	Total product tax
product_total_amount	decimal(26,8)	Total product total, including tax
shipping_tax_amount	decimal(26,8)	Total shipping tax
shipping_total_amount	decimal(26,8)	Total shipping amount, including tax
discount_tax_amount	decimal(26,8)	Total discount tax
discount_total_amount	decimal(26,8)	Total discount amount, including tax
fees_tax_amount	decimal(26,8)	Total fees tax
fees_total_amount	decimal(26,8)	Total fees amount, including tax
payment_method	varchar(100)	Identifier of payment method
payment_method_title	text	Title of payment method used
transaction_id	varchar(100)	Transaction ID provided by payment gateway.
created_via	varchar(100)	The identifier for order creation source (admin, rest-API, checkout, etc)
ip_address	varchar(100)	IP address used to create the order
user_agent	text	The user-agent string of the web client that is used to create the order.
woocommerce_version	varchar(20)	WooCommerce version which was active when creating the order.
prices_include_tax	tinyint(1)	For internal use. Whether prices were including taxes when purchased.
coupon_usages_are_counted	tinyint(1)	For internal use. Whether coupon usage is counted.
download_permissions_granted	tinyint(1)	Marks whether download permissions are granted to order or not.

Proposed order core table schema

`wp_wc_order_addresses`

Name	Type	Description
id	bigint(20) unsigned, primary key, indexed	Unique ID for order address record.
order_id	bigint(20) unsigned, indexed	Order ID.
address_type	varchar(20)	Type of address (billing, shipping, etc).
first_name	text	First name
last_name	text	Last name
company	text	Company name
address_1	text	Address line 1
address_2	text	Address line 2
city	text	City
state	text	State
postcode	text	Postcode
country	text	Country
email	varchar(100)	Email
phone	varchar(100)	Phone
date_created_gmt	datetime	Date of creation for this record

Proposed order addresses table schema

`wp_wc_orders_meta`

Name	Type	Description
id	bigint(20) unsigned, primary key, indexed	Unique ID for meta record.
order_id	bigint(20) unsigned, indexed	Corresponding order ID.
meta_key	varchar(255), indexed	Name of the key.
meta_value	text	Value of the record.
date_created_gmt	datetime	Date of record create
date_update_gmt	datetime	Date 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.

`wp_wc_order_notes`

Name	Type	Description
id	bigint(20) unsigned, primary key, indexed	Unique ID for order note.
order_id	bigint(20) unsigned, indexed	ID of the order.
type	varchar(255), indexed	Type of note (private, customer, etc).
author	bigint(20) unsigned	The ID of the user creating the note. Will be `WooCommerce` for system-generated notes.
date_created_gmt	datetime	Timestamp when the note was created.
content	text	Contents of the note.

Order note

`wp_wc_order_notes_meta`

Name	Type	Description
meta_id	bigint(20) unsigned, primary key, indexed	Unique ID for meta
note_id	bigint(20) unsigned, indexed	ID of the note.
meta_key	varchar(255), indexed	Name of the key.
meta_value	text	Value of the record
date_created_gmt	datetime	Date of record create
date_update_gmt	datetime	Date 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.

The plan for the WooCommerce custom order table - Speeding up and improving reliability Print

Background and scope

Backward Compatibility

Database schema

General structure

Tables’ structure

wp_wc_orders

wp_wc_order_addresses

wp_wc_orders_meta