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.
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.
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.