WooCommerce Database Explained: How It Works and Where To Find Data

The term “database” is widely used and known to millions of people, but the proportion of people who have it in their vocabulary without understanding what it describes is sizable.

Databases are at the core of so many digital services today that it is easy to overlook them entirely, but whether you run a shopping site or a business server, building an understanding of them is worthwhile regardless of whether or not you are directly responsible for administration duties.

Having a grasp of the underpinnings of what a database is and how it works is sensible, especially if you run a WooCommerce site and you want to make optimal use of all that this platform has to offer.

To that end, here is an overview of the database fundamentals to bring you up to speed.

Databases – the basics

In essence, a database is a system in which information is stored in an ordered way, allowing for details to be added, retrieved and modified as necessary by an overarching management system.

Order and efficiency is achieved by putting data into tables, with rows and columns allowing for information to be organized logically, in a manner that is both straightforward for human users to understand, and also for software to interact with.

Structured query language (SQL) is not the only programming language on the block, but it is by far the most commonplace in a database context.

In terms of what they can achieve, the answer is multifaceted. Whether storing data for websites and apps, or housing information such as media files, business documents and everything in between, a database will be the ideal solution.

Of course any system which has such vital roles to play in the digital age also needs to be monitored and maintained to ensure that it performs as intended. Performance can suffer due to blocking and deadlocking, as this occurs when multiple processes attempt to use the same resource at once.

Preserving data integrity with these features is important, although the work of administrators to minimize the recurrence of such snafus is equally necessary. A poorly optimized database can create lots of knock-on effects, such as slow page load speeds on websites which in turn might lead to higher bounce rates and fewer conversions.

WooCommerce Database Structure

Now that you have a foundational appreciation of databases, it is worth exploring their implications in a WooCommerce context.

Chiefly it is worth pointing out that the way customer data is stored by WooCommerce is not centralized; rather this information can be distributed among tables that are either customized for the platform itself, or based on the same solutions as all WordPress sites.

Unless you have made any changes yourself, WooCommerce will automatically associate customer data with the orders they have placed on your store. This applies irrespective of whether they have created an account or decide to checkout as a guest.

This all sounds good in principle, but in practice it means that it is perfectly possible for multiple copies of the same customer information to persist on your site’s database, because every order they make will essentially duplicate this. It may not be the most efficient approach, but it does have the advantage of meaning that any detail changes are recorded separately, which is convenient in the event that the delivery address changes, for example.

Likewise when orders are placed, WordPress user accounts will be created and stored for customers, with the wp_users table storing aspects like their email address, while the wp_usermeta is harnessed to handle their shipping details.

By its nature as a plugin, WooCommerce introduces a whole host of new tables to the database, each of which is specifically tuned to provide useful functionality for site operators. Here is a quick overview of these custom tables, and what function each serves.

actionscheduler_actionslist of actions that will be completed by Action Scheduler
actionscheduler_claims
actionscheduler_groups
actionscheduler_logsactions completed by Action Scheduler
woocommerce_sessionsmostly contains users’ carts
woocommerce_api_keysAPI keys storage
woocommerce_attribute_taxonomiescategories, tags, etc.
woocommerce_downloadable_product_permissionsdownloads access permissions
woocommerce_order_itemsline items linked to orders
woocommerce_order_itemmetaline items meta data
woocommerce_tax_rateslist of tax rates set manually
woocommerce_tax_rate_locationslocation-based data on tax rates
woocommerce_shipping_zoneslist of custom shipping zones
woocommerce_shipping_zone_locationslocations of the shipping zones
woocommerce_shipping_zone_methodsshipping methods for each zone
woocommerce_payment_tokenspayment tokens
woocommerce_payment_tokenmetapayment token metadata
woocommerce_logevent log
wc_webhookswebhooks
wc_download_logdownload log
wc_product_meta_lookuplookup table that makes it quicker to search for products inside orders
wc_tax_rate_classestax classes
wc_reserved_stockstock that is reserved to ensure availability at checkout

WooCommerce Products and Orders

In the above table, we have highlighted woocommerce_order_items and woocommerce_order_itemmeta for a specific reason: if you’ll ever need to manipulate the WordPress Database via PHPMyAdmin, for example, you will surely use those two tables as they contain all WooCommerce order data.

Problem is, orders are also stored in some other WordPress tables, and so are WooCommerce products. Here’s a quick overview of the tables involved.

Finding WooCommerce Orders in the WP Database

WooCommerce order data can be found in the following tables:

  • woocommerce_order_itemmeta
  • woocommerce_order_items
  • comments (order_note)
  • postmeta (shop_order)
  • posts (shop_order ID)

Finding WooCommerce Products in the WP Database

WooCommerce product data can be found in the following tables:

  • wc_order_product_lookup (product ID or product_variation ID)
  • wc_product_meta_lookup (product ID or product_variation ID)
  • postmeta (product or product_variation)
  • posts (product ID or product_variation ID)

When to use the WordPress DB

The first step to impactful leveraging of a database when running an e-commerce site is knowing how to make the most of queries to retrieve data efficiently.

When reviewing orders, for example, you can narrow down your search according to a specific date range, which is useful if you want to assess performance within a given window of time, or pinpoint a particular order further down the line. This is also applicable to other variables, such as product weight.

Likewise it makes sense to get a handle on how to export data from WooCommerce so that you can analyze and process it elsewhere, perhaps using spreadsheet software. This can be useful for all sorts of purposes, such as filing taxes and carrying out market research into your audience.

Ultimately it will pay to get to grips with the basics of SQL if you want to make full use of a database for online retail, and as this is one of the most approachable programming languages around, it should be straightforward.

Related content

  • WooCommerce: Get Order Info (total, items, etc) From $order Object
    As a WooCommerce development freelancer, every day I repeat many coding operations that make me waste time. One of them is: “How to get ____ if I have the $order variable/object?“. For example, “How can I get the order total“? Or “How can I get the order items“? Or maybe the order dates, customer ID, […]
  • WooCommerce: How to Add a Custom Checkout Field
    Let’s imagine you want to add a custom checkout field (and not an additional billing or shipping field) on the WooCommerce Checkout page. For example, it might be a customer licence number – this has got nothing to do with billing and nothing to do with shipping. Ideally, this custom field could show above the […]
  • WooCommerce: Create a Custom Order Status
    All WooCommerce orders go to either “processing”, “completed”, “on-hold” and other default order statuses based on the payment method and product type. Sometimes these statuses are not enough. For example, you might need to mark certain orders in a different way for tracking, filtering, exporting purposes. Or you might want to disable default emails by […]
  • WooCommerce: Allow Users to Edit Processing Orders
    How can WooCommerce customers edit an order they just placed and paid for? I swear I looked on search engine results and other places before coming to the conclusion I needed to code this myself. For example, a user might want to change the delivery date (if you provide this on the checkout page). Or […]
  • WooCommerce: Add Column to Orders Table @ WP Dashboard
    The WooCommerce Orders Table, which can be found under WP Dashboard > WooCommerce > Orders, provides us with 7 default columns: Order – Date – Status – Billing – Ship to – Total – Actions. This is used by shop managers to have an overview of all orders, before eventually clicking on a specific one. […]

Rodolfo Melogli

Business Bloomer Founder

Author, WooCommerce expert and WordCamp speaker, Rodolfo has worked as an independent WooCommerce freelancer since 2011. His goal is to help entrepreneurs and developers overcome their WooCommerce nightmares. Rodolfo loves travelling, chasing tennis & soccer balls and, of course, wood fired oven pizza. Follow @rmelogli

4 thoughts on “WooCommerce Database Explained: How It Works and Where To Find Data

  1. I have tried two WooCommerce product / Google Sheets sync plugins and was very disappointed with the results.

    I used csv import / export as I have a physical disability and find that method much more accessible than wp-admin. 99% of our products are variable, virtual downloadable products, with a growing number of quantity restrictions.

    I have a lot of database and spreadsheet experience.

    Could anybody tell me whether it is possible to connect a hosted csv file and have the WordPress systems periodically sync with it?

    I use IONOS to host media files so that I can organise them and not get swamped with duplicates.

    If I could host a csv and have it periodically imported, I could work MUCH faster.

    Any advice would be great

    Thanks
    Sarah

    1. Hello Sarah! WP All Import can do that automatically

  2. Rodolfo – does Woocommerce use WordPresses database exclusively?

    1. Yep. Can you give me some more context though?

Leave a Reply

Your email address will not be published. Required fields are marked *