Refining the Woo Pricing Analytics Model

SB model v2

In the previous section, 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.

The figure above show how I’ve refined our earlier “framewire”model to capture these semantics.

A new entity has been added to describe the abstract Grouped entity type discussed earlier.

In addition, I added a Product variation type entity, in order to support this flavor of Woo functionality.

I’ve also collapsed the star schema: there is now one Price entity. that is constrained the Price Type entity.  This will allow for extremely efficient querying of Pricing analytics used MariaDB SQL.

Another change involves making the Order Detail entity serve as the connector between Transaction and Product.

A Discount entity will serve to simplify to the semantics of the Woo extensions mentioned above. In this model, a bundling formation will be captured as an instance of a Discount type.

With this new and improved data model, we can begin the first phase of our next task: filling out our entities with attributes, and ensuring that the data types specified are consistent with those in Woocommerce.

For that, we will have to dig deep into the Woo schema.

Our goal at the end of this exercise will be to be in a position to write an adapter ETL script that downloads a given Woo Database, and loads it correctly to our DSS model.

Once this is achieved, we will begin to formulate SQL price-related analytical queries.

We will leave out Customer and Supplier, for the time being.

In addition, we will not be concerned with adding BOM functionality,unless it becomes necessary at some point for this data model to support a manufacturing application.

Such is not the case now, where 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.

After this verification exercise, we shall begin to explore the temporal functionality available in MariaDB 10.3, and see how it might be usefully support pricing analytics.

Finally, to answer the question that was asked at the end of the previous section: in my view the answer is both: a total sale price will appear in a Transaction table, and Order Detail will reveal the price of each line item via its association with Price.