What is the structure of WooCommerce database and where to find products and orders?
Recently we helped a client switch from Shopify to WordPress+WooCommerce. Shopify is a great platform and this was the best choice at the time when the client needed a quick eCommerce store. Later due the resource constraints and limitations of the platform it was best to migrate the store from Shopify to wooCommerce.
One of the reasons was to have super control on the database layer that wooCommerce provides. As you know, WooCommerce is just a plugin to WordPress. WordPress uses MySQL/MariaDB for storing all the objects. WooCommerce being a plugin needs to reside on the same database for the critical functions it provides.
Let us explore how wooCommerces stores various stores data structures.
I am sure wooCommerce has its reasons but I found it surprising that it decided to keep the store orders into the wp_posts table. wp_post table is the table to store all pages and blog posts. I would have simply chosen a separate table to store all the order and order details information. For e.g in a typical ERP software, you will always find something like order_header and order_lines tables. This is not the case with woocommerce design.
Another reason I find it odd is that the column names in wp_posts are better suited for posting activity like post title, post date etc.
Using wp_posts to store order header seems like a design done in hurry.
So let us get into the details
Products are located mainly in 2 tables:
wp_poststable with a
wp_postmetatable with the corresponding
post_idby product (the product ID).
Product types, categories, subcategories, tags, attributes and all other custom taxonomies are located in the following tables:
wp_woocommerce_attribute_taxonomies(for product attributes only)
Product types are handled by custom taxonomy
product_type with the following default terms:
Since Woocommerce 3+ a new custom taxonomy named
- The product visibility with the terms
- The feature products with the term
- The stock status with the term
- The rating system with terms from
List of woocommerce Tables
Note that the posts tables is used by Custom Post Types, and WooCommerce uses those for products, coupons, and orders.
Can you imagine any true inventory management or ERP system storing Product information and Orders into the same database table?
The following tables are installed with WooCommerce. Each table name will be prefixed with custom WP Database Table Prefix e.g. “
wp_". The default prefix is “wp_” but you can choose any prefix during install.
||Stores customer session data, such as carts.|
||Stores API Keys used for the REST API.|
||Stores global attribute taxonomy names for products.|
||Stores product access permissions for downloadable products (granted after purchase).|
||Stores line items which are associated with orders.|
||Stores meta data about order line items.|
||Stores tax rates you define in the admin area.|
||Stores locations (postcodes and cities) associated with the above tax rates.|
||Stores shipping zones you create in the settings area.|
||Stores locations associated with your shipping zones.|
||Stores shipping methods linked to your shipping zones.|
||Stores customer payment tokens (used by gateways).|
||Stores meta data about payment tokens.|
||General logging table (alternative to file based logging).|
||Stores any webhooks that have been setup on the store.|
||Logs user downloads of downloadable products.|
Later we will show how to build reports and dashboards using the above table structure. Stay tuned!