A Closer Look at Woo Products

woocommerce admin panel
Figure 1

Summary: This is the second article in a series of posts about the process of building a simple WP plug-in that implements a temporal DSS database for users of WooCommerce.

In the previous post, we “wireframed” a data model that shows the objects of potential interest to this application.

At this point, excitement is building. We are beginning to see the boundaries and various objects of interest in this particular world.

Is it not time already to produce sample DDL, insert some test data, and query the SB (for sandbox, the name I have given to the dev version of this plug-in) database?

Not so fast.

Before we start slinging code around, maybe we need to understand this model a little better that we do.

Yes, there are those who fear “analysis paralysis,” and impatiently jump to the coding phase – particular when the PM is breathing down your neck.

In my experience, that’s the quick path to bug fix hell. A sign of this approach is the phenomenon of frequent minor releases.

What I am suggesting is that a little focused thinking about the problem domain, coupled with a willingness to design flexibly (in terms of breaking traditional data modeling “rules”), but not of course carelessly, is usually a good thing, even if we are talking about an apparently simple PHP/SQL plugin, and not some mega monster 500,000 line app.

Data modeling is primarily an art; it forces one to think ontologically. We are, in effect, digital philosophers. As such, we are well-advised to think as clearly as we can about some domain that we propose to model, and then make decisions about the nature of what we find as objects of relevance in that world.

For example, we have already seen, in v1 of the SB data model (see link above), a star schema in which a strong entity, Product, is associated with a number of weaker Price entities that depend on Product for their existence.

After all, there cannot be a price for something that does not exist. Or is that not really so? And what do we mean by this terminology?

Moreover, are prices a fixed notion?

Of course not; prices change all the time: inflation, discounts, and the rest of it.

They are only immutable, from a database perspective, when persisted at a specific point in time (which is partly why I am so interested in MariaDB 10.3, which natively supports temporal tables).

But at the risk of sounding like I am trying to make a bad joke, can an instance of a Product actually be priceless – not in the sense of NULL (as in unknown), or free (which would equate to a value of 0 in some currency unit), but in the sense of inapplicable?

It turns out that, in effect, in the WooCommerce universe, the answer is yes.

Let’s see how.

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.

For that reason, we only have to model product grouping in SB v1, which is a bit of a relief, as those extensions can be quite open-ended in terms of what a shopper is able to can do with them.

Since WooCommerce already utilizes the term “virtual” to describe a subtype of Product, I have nicked from C++ the notion of an “abstract class.”

An abstract Product instance of type “Grouped Product” does not have a price or inventory quantity.

It must, however, have an SKU (which can anything: but once entered, an SKU functions an immutable, unique key identifier for a Product in the WooCommerce schema).

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?

We will cover this in the next section.

But to wrap up this article, we’ll answer a question that was asked in the previous post: is the 0-N recursive relationship named “comprises” sufficient to capture the notion of a Grouped Product?


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.

However, it may be useful to note in passing that MariaDB supports recursion, which is perhaps regarded by some practitioners as an elegant extension of SQL (1).

Moreover, the vague jack-of-all-trades “comprises” could have been replaced with “bundled” – which would have been confusing, since the Woo documentation tends to use that term in reference to the Product extensions mentioned earlier. And “grouped-by” would have been too specific, and this recursive relationship eventually might turn out to be polymorphic.

We have covered a lot of ground in this article.

The fun part – and the only interesting one, from a programmer’s show-me-the-code perspective – which is deriving a workable data model from what we have discovered so far, will come soon.

I will leave you with this:

At the start of this piece I mentioned that data modelers often have to think ontologically about the nature of what they are modeling. I also remarked that data modeling – particularly to those who are really good at it – is not a lockstep discipline that follows some rigid methodology-du-jour, but in fact is something of an art, one that combines the skills of a programmer with the real world experience of a DBA.

With that in mind, allow me to ask you this:

In our temporal DSS model, should the crucial notion of a Checkout Price be modeled as a duplicated descriptor that appears in both the Product and Transaction entities?

Or should it appear in Transaction or Product alone?

And in either case, what would be the design rationale?

1. see https://mariadb.com/kb/en/library/recursive-common-table-expressions-overview/