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_posts table with a post_type like product or product_variation,
  • wp_postmeta table with the corresponding post_id by product (the product ID).

Product types, categories, subcategories, tags, attributes and all other custom taxonomies are located in the following tables:

  • wp_terms
  • wp_termmeta
  • wp_term_taxonomy
  • wp_term_relationships
  • wp_woocommerce_termmeta
  • wp_woocommerce_attribute_taxonomies (for product attributes only)

Product types are handled by custom taxonomy product_type with the following default terms:

  • simple
  • grouped
  • variable
  • external

Since Woocommerce 3+ a new custom taxonomy named product_visibility handle:

  • The product visibility with the terms exclude-from-search and exclude-from-catalog
  • The feature products with the term featured
  • The stock status with the term outofstock
  • The rating system with terms from rated-1 to rated-5

List of woocommerce Tables

Table Details

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?

Table Overview

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.

Table NameDescription
woocommerce_sessionsStores customer session data, such as carts.
woocommerce_api_keysStores API Keys used for the REST API.
woocommerce_attribute_taxonomiesStores global attribute taxonomy names for products.
woocommerce_downloadable_product_permissionsStores product access permissions for downloadable products (granted after purchase).
woocommerce_order_itemsStores line items which are associated with orders.
woocommerce_order_itemmetaStores meta data about order line items.
woocommerce_tax_ratesStores tax rates you define in the admin area.
woocommerce_tax_rate_locationsStores locations (postcodes and cities) associated with the above tax rates.
woocommerce_shipping_zonesStores shipping zones you create in the settings area.
woocommerce_shipping_zone_locationsStores locations associated with your shipping zones.
woocommerce_shipping_zone_methodsStores shipping methods linked to your shipping zones.
woocommerce_payment_tokensStores customer payment tokens (used by gateways).
woocommerce_payment_tokenmetaStores meta data about payment tokens.
woocommerce_logGeneral logging table (alternative to file based logging).
wc_webhooksStores any webhooks that have been setup on the store.
wc_download_logLogs user downloads of downloadable products.

Later we will show how to build reports and dashboards using the above table structure. Stay tuned!