Where to find Products in WooCommerce database Tables?

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

• Table: woocommerce_sessions
• Table: woocommerce_api_keys
• Table: woocommerce_attribute_taxonomies
• Table: woocommerce_downloadable_product_permissions
• Table: woocommerce_order_items
• Table: woocommerce_order_itemmeta
• Table: woocommerce_tax_rates
• Table: woocommerce_tax_rate_locations
• Table: woocommerce_shipping_zones
• Table: woocommerce_shipping_zone_locations
• Table: woocommerce_shipping_zone_methods
• Table: woocommerce_payment_tokens
• Table: woocommerce_payment_tokenmeta
• Table: woocommerce_log
• Table: wc_webhooks
• Table: wc_download_log

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.

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