Note as of Sep 14 2022: the Custom Order Tables project has been renamed to High-Performance Order Storage (HPOS).
Hello everyone,
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_order_operational_data: We will use this table to store fields and flags that we use to maintain internal order state. We are keeping these fields separate from the core order table as changes to these fields are relatively more frequent.wp_wc_orders_meta: This table is similar in functionality to thewp_postmetatable, 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(320), 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 |
| parent_order_id | bigint(20) unsigned, indexed | Order ID of the parent, in case this order is a refund |
| 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 the payment gateway. |
| 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. |
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 |
| varchar(320) | ||
| phone | varchar(100) | Phone |
| date_created_gmt | datetime | Date of creation for this record |
wp_wc_order_operational_data
| Name | Detail | Description |
|---|---|---|
| id | bigint(20) unsigned | |
| order_id | bigint(20) unsigned, indexed with cart_hash | |
| created_via | varchar(100) | The identifier for order creation source (admin, rest-API, checkout, etc) |
| woocommerce_version | varchar(20) | WooCommerce version which was active when creating the order. |
| prices_include_tax | tinyint(1) | For internal use. Whether prices were included 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. |
| cart_hash | varchar(100) | Hash of cart contents, used when clearing cart when order payment is successful. |
| new_order_email_sent | tinyint(1) | Whether an email is sent for the order |
| order_key | varchar(100), indexed | Key used to allow anonymous order access for payment and related operations. |
| order_stock_reduced | tinyint(1) | Whether the stock is reduced for order |
| 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. |
| 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 |
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 |
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. |
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 |
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.
We will also continue to reach out via this blog with all the important developments and upgrade notes as they become available.
Changelogs
Feb 1, 2022
- Add table
wp_wc_operational_dataso that any columns related to specific features or operational details are in this new table. - The email field length is increased to 320 from 100 to accommodate most email structures.
- We are dropping fields that need computation and were not stored previously, such as
product_total_amount,product_tax_amount, etc.
These fields are already computed during run time and not stored. However, we plan to add them back if we realize that there are performance benefits to adding them during implementation. - We are adding new fields to
wp_wc_operational_data, which are not part of the core order record but are present for most orders anyway. These arecart_hash,new_order_email_sent,order_key,order_stock_reduced,recorded_salesandis_vat_exempt.
Leave a Reply