WordPress and WooCommerce SQL Database Schema VIEWs

3 min readMar 29, 2023

Contents

· Introduction
· Database Schema
· WordPress Database Schema
· WooCommerce Database Schema
· WordPress VIEWs
· WooCommerce VIEWs
· Links

Introduction

WordPress is the most used CMS in the world. WooCommerce, a WordPress plugin, is the one of most used e-Commerces in the world.

Some time ago I started to use both, coming from a Microsoft .NET and Microsoft SQL Server experience. WordPress ONLY uses MySQL, a database I have already used in the past. WordPress/WooCommerce database schema uses a lot the custom columns for flexibility, but it makes the queries more complex.

To make my life easier, I created many VIEWs devoted to getting all the usual information I need from the database. The VIEWs SQL code is available at the following GitHub repository:

GitHub Repository

IMPORTANT
All the VIEWs SQL codes consider a wp_ prefix for the database tables: please change it if necessary.

Database Schema

WordPress ONLY uses MySQL and to be customizable uses the custom columns concept where data is saved in metadata tables. Usually, when I wanted to create new columns in SQL Server I changed the table schema to include them. It works, but every customization depends on the developer to change the schema and the code that accesses this schema. WordPress may create as many custom columns as you want, but instead of changing the table schema, it saves the new column in a separate metadata table. The advantage of this is almost infinite customization, but the SQL queries may become a nightmare!

WordPress Database Schema

Below you find a link with a detailed description os WordPress tables:

Database Description

Below you find the main tables available ( in master-detail structure ):

wp_users
    wp_usermeta
    wp_comments
        wp_commentmeta
    { wp_posts }
wp_posts : Posts | Pages
    wp_postmeta
    { wp_term_relationships }
wp_term : Categories for Posts and Links | Tags for Posts
    wp_termmeta
    wp_term_taxonomy
        wp_term_relationships
wp_options
wp_links { DEPRECATED }

For instance, wp_posts is the Posts table ( the most important table ), and wp_postmeta is the Posts Metadata table. This second table will be used to save the Posts custom columns.

WooCommerce Database Schema

Below you find a link with a detailed description of WooCommerce tables:

Database Description

Below you find the main tables available ( in master-detail structure ):

woocommerce_api_keys
woocommerce_attribute_taxonomies
woocommerce_downloadable_product_permissions
woocommerce_order_items
    woocommerce_order_itemmeta
woocommerce_tax_rates
    woocommerce_tax_rate_locations
woocommerce_shipping_zones
    woocommerce_shipping_zone_locations
    woocommerce_shipping_zone_methods
woocommerce_payment_tokens
    woocommerce_payment_tokenmeta
woocommerce_log
woocommerce_sessions
wc_download_log
wc_product_meta_lookup
wc_tax_rate_classes
wc_webhooks

For instance, woocommerce_order_items is the Orders Items table, and woocommerce_order_itemmeta is the Orders Items Metadata table. This second table will be used to save the Orders Items custom columns.

WordPress VIEWs

The file below shows many query examples using the WordPress database:

wp.sql

The WordPress VIEWS available are:

CREATE-VIEW.vw_attachments.sql
CREATE-VIEW.vw_options.sql
CREATE-VIEW.vw_taxonomies.sql

At the beginning of each VIEW SQL code, you find an example of usage.

WooCommerce VIEWs

The file below shows many query examples using the WooCommerce database:

wc.sql

The WooCommerce VIEWS available are:

CREATE-VIEW.vw_brands.PerfectBrandsForWooCommerce.sql
CREATE-VIEW.vw_brands.WooCommerce.sql
CREATE-VIEW.vw_coupons.sql
CREATE-VIEW.vw_customers.sql
CREATE-VIEW.vw_products.sql
CREATE-VIEW.vw_products_brands.sql
CREATE-VIEW.vw_products_categories.sql
CREATE-VIEW.vw_products_images.sql
CREATE-VIEW.vw_products_taxonomies.sql

WooCommerce has a new Storage options called HPOS — High-Performance Order Storage, so there are now two VIEWs available: WooCommerce, the old way, and WooCommerce-HPOS, the new way.

\WooCommerce
  CREATE-VIEW.vw_orders.sql
  CREATE-VIEW.vw_orders_items.sql
\WooCommerce-HPOS
  CREATE-VIEW.vw_orders.sql
  CREATE-VIEW.vw_orders_items.sql