Understanding the Woo Product Schema

wordpress data model
The yellow wp_posts row highlights how the  Product custom post type anchors the WooCommerce database schema

Let’s analyze how WooCommerce stores a Product instance as a custom post row in the wp_posts table.

Here is the query.

SHOW COLUMNS
FROM
    `wp_posts`

It produces a 24-row result.

ID  (PK)
post_author
post_date 
post_date_gmt
post_content
post_title text
post_excerpt
post_status
publish
comment_status
ping_status
post_password
post_name
to_ping
pinged
post_modified
post_modified_gmt
post_content_filtered
post_parent
guid
menu_order
post_type varchar
post_mime_type
comment_count

As you can see, most of these attributes have little to do with Products. This is due to WP core being a content management system. 

It is, however, extensible.

For example, many product-related attributes can be found in the wp_postmeta table.  These are stored as EAV pairs, which of course violates the 1st normal form of relational database design.

This scheme also allows the addition of n-number of attributes to a custom entity; however, this flexibility results in a performance hit — which WP tries to compensate for by keeping retrieved objects in memory. It also makes the model less understandable, particularly for end users wishing to write ad hoc database queries.

Let’s now take a look at the Product-related attributes found in the wp_postmeta table.

Here is the query:

SELECT DISTINCT
    q.meta_key AS 'ATTRIBUTE'
FROM
    wp_posts p
JOIN wp_postmeta q ON
    (p.id = q.post_id)
WHERE
    post_type = 'product'
ORDER BY
    q.meta_key

This query produces the following 35-row result.

_backorders
_crosssell_ids
_default_attributes
_download_expiry
_download_limit
_downloadable
_edit_last
_edit_lock
_height
_length
_low_stock_amount
_manage_stock
_price
_product_image_gallery
_product_version
_purchase_note
_regular_price
_sale_price
_sale_price_dates_from
_sale_price_dates_to
_sku
_sold_individually
_stock
_stock_status
_tax_class
_tax_status
_thumbnail_id
_upsell_ids
_virtual
_wc_average_rating
_wc_rating_count
_wc_review_count
_weight
_width
total_sales *

These are the descriptors associated by WooCommerce with a Product entity — except for Product Type, Product Category, and Product Tag.

Here is the query that returns those descriptors for every and any product in the Woo/WP database.

SELECT
    P.post_name AS "Prod Name",
    b.taxonomy AS "Prod Taxnmy",
    b.description AS "Prod Taxnmy Desc",
    b.parent AS "Prod Taxnmy Parent",
    b.count AS "Prox Taxnmy Cnt",
    c.term_group AS "Prod Taxnmy Group",
    c.name AS "Product Type"
FROM
    wp_posts P
INNER JOIN wp_term_relationships a ON
    (P.ID = a.object_id)
INNER JOIN wp_term_taxonomy b ON
    (
        a.term_taxonomy_id = b.term_taxonomy_id
    )
INNER JOIN wp_terms c ON
    (b.term_id = c.term_id)
WHERE
    post_type = 'product'

If you look at this on PhPMyAdmin, the results will look something like this.

Now that have all our candidate descriptors for a custom Product table lined up, our next step will be to eliminate those bearing information that is superfluous to a streamlined stock inventory analytics model.

Any serialized data should be decoded, in order for the data to be human readable. PHP serialized/deserialized data may pose an additional security risk.  

To make the custom products database readable and queryable by ordinary store owners, it is imperative to deserialize and normalize any encoded arrarys in the Woo schema.

 

* Note this field is inconsistently named Also, please note that MSRP is missing.  It can be added by purchasing an expensive plug-in from WooCommerce, or added and updated on a private Prodcut analytics.