Batch Report Beta

css responsive app
Galaxy S5 view of the report, which is written using CSS flexbox

This is the STOREBOSS financial analysis report I am currently developing.

The purpose of this merchant-facing app is to allow a store owner to upload transactions from a credit card processor to a custom table, and perform various comparative analytics.

Currently, this is not possible to do in WooCommerce.

The app is written in PhP, and uses JS on the front end, and MariaDB on the back.

Once I am done developing the beta version of Batch Totals Report (the name of the app ), I will turn it into a stand alone WP plug in.

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.

Refining the Woo Pricing Analytics Model

In a previous post, we  discovered that a Woocommerce Product instance is by default a “Simple Product.”

We also learned that three other Product types are available: 
a) Grouped product
b) External/Affiliate product
c) Variable product

Any of these four Product types can optionally be “virtual” or “downloadable,” which are in effect a subtype of Product.

There are also extensions sold by WooCommerce that at first blush appear to specify additional Product types.

These are described here.

However, upon closer examination, we realize that these various product bundling extensions are not in fact new Product types, but are more or less a way for a store owner to market products using various forms of discounting or promotions.

Thus, extensions can simply be modeled as Discount Types.

We will not be concerned with BOM functionality for now, since the majority of Woo users are most likely small business store owners with no need for complex recursive M:N functionality with respect to Products.

 

A Closer Look at Woo Products

woocommerce admin panel
Figure 1

WooCommerce supports the notion of “grouped” products. This is implemented by a store owner creating a Product abstraction, sometimes referred to by Woo in the documentation as a parent product.

This parent instance of a group of products does not itself have a price.

Its functional role is to visually link via some theme UI interface X number of products and presented to a shopper as a unit.

This unit is not invariant, as Product instance is a group can be purchased separately, by removing items from the Cart.

Moreover, there is no discount for buying grouped products.

In other words, being grouped does not affect the normal retail price in any way – unless a coupon is applied at checkout, the total cost is arithmetically additive.

The shop owner’s ability to group products is built into Woo core; in other words, it’s free.

In order to present a customer with more sophisticated marketing offers, a store owner must purchase extensions, such as the one for Product Bundling, or another for what is referred to in WooLand as a Composite Product.

These extensions are not free.

WooCommerce utilizes the term “virtual” to describe a subtype of Product, 

Once the sku and Type are specified, a shop owner user is free to link n-number of other Product types (except for abstract classes: in other words, you can have groupings of grouped products) to this parent instance.

In the WooCommerce world, a Product instance is by default a “Simple Product.”

Three other Product types are also available:
a) Grouped product
b) External/Affiliate product
c) Variable product

Any of these four Product types can optionally be “virtual” or “downloadable, which are subtypes of Product.

In general, the notion of virtual products refers to intangible products, such as a subscription to some pricey newsletter.

A downloadable product subtype is something tangible (such as the zip file for a plug in, or the .pdf of some newsletter) that you can download your computer.

This Product class hierarchy provides flexibility to the shop owner.

In can be confusing at first, but speaking generally, what the designers of Woo mean by the term “virtual” is something that you cannot see or touch, and downloadable is typically thought of as an executable of some sort.

There are additional complexities to Product that we need to keep in mind.

For example, what exactly are Variable Products, and what is meant by Custom Attributes?

A class of Grouped Product types is not a true M:N BOM (bill-of-materials) case, which usually leads to elaborate complexity in order to implement correctly, and often entails a significant performance hit.

 

Woo: Relational in Name Only

In case you didn’t already know this: Woo uses a hybrid quasi-relational / EVA NoSQL data model. It is implemented on top of WP’s underlying, equally non-relational meta schema, which runs atop MySQL or MariaDB.

The WP data model was designed initially to provide data persistence to an early CMS system. It allowed the addition of n-number of attributes to a post entity type, as needs evolved.

At its core,  the WP data model consists of rows  in a centrally important table that can be joined via a SELECT statement to a vertically structured (i.e., “thin”) table which specifies whatever attributes (instantiated as rows) WP would need, as time went by, for its post and, later, page types.

This has performance implications.

Also, partly because of WP’s flexible,”meta” structure, Woo does not make “normal” usage of Primary and Foreign relationships — business rules are implemented via PHP code. This leads to maintainability issues.

Moreover, Woo uses serialization, which makes its db non portable and violates the relational model’s scalar rule.