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_actions | list of actions that will be completed by Action Scheduler |
| actionscheduler_claims | |
| actionscheduler_groups | |
| actionscheduler_logs | actions completed by Action Scheduler |
| woocommerce_sessions | mostly contains users’ carts |
| woocommerce_api_keys | API keys storage |
| woocommerce_attribute_taxonomies | categories, tags, etc. |
| woocommerce_downloadable_product_permissions | downloads access permissions |
| woocommerce_order_items | line items linked to orders |
| woocommerce_order_itemmeta | line items meta data |
| woocommerce_tax_rates | list of tax rates set manually |
| woocommerce_tax_rate_locations | location-based data on tax rates |
| woocommerce_shipping_zones | list of custom shipping zones |
| woocommerce_shipping_zone_locations | locations of the shipping zones |
| woocommerce_shipping_zone_methods | shipping methods for each zone |
| woocommerce_payment_tokens | payment tokens |
| woocommerce_payment_tokenmeta | payment token metadata |
| woocommerce_log | event log |
| wc_webhooks | webhooks |
| wc_download_log | download log |
| wc_product_meta_lookup | lookup table that makes it quicker to search for products inside orders |
| wc_tax_rate_classes | tax classes |
| wc_reserved_stock | stock 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.