Products and Options: The Problem

February 19, 2009

Products and Options: The Problem

I’m not an expert in inventory systems, warehouse management, production or product distribution of any kind. However, as ROSA LOVES continually evolves I will probably become one (or at least in certain areas). At ROSA LOVES our inventory is currently simple: we sell shirts. When we originally launched, everything came together extremely quickly, mainly because it had to. As it has become better known we’ve experienced various growing pains, one of them being inventory management. Now there are many pains we experience in this area, but I’m going to focus on the problem from the perspective of the store/inventory management software, as well as the underlying database schema. On to the problem.

As I said, ROSA LOVES sells shirts. However, it may not always be so focused, and the various options for shirts may change. The problem of products and their myriad of possible options is a well-navigated space, however I’ve not run across any public discussion from a developer’s point of view (hence my urge to post).

With any product to be sold, there are typically various options that must be managed at an inventory (and often a pricing) level. Consider, for example, the Hope Transfusion t-shirt. If you’ve never ordered a shirt from the site, a quick glance at that page will show you that it is available in one of two “styles” (men’s and ladies’), and in turn, one of several sizes.

A narrow-minded model of this then would express each of those options directly as tables (and associated software objects). So there might be 3 main tables: products, styles, and colors. And then there would be join tables binding the options into quantifiable items (ie, Hope Transfusion in ladies’ small, men’s large, etc).

This works fine until we either want to add a new kind of product or another set of options. It doesn’t scale well at all, so let’s abstract it a bit. In reality, what we’re talking about are products and their options. Specifically, what we call a “product” (like the Hope Transfusion shirt) is more of a parent entity, and each possible combination of options is a “child.” You can’t buy a Hope Transfusion, but you can buy a ladies’ medium. Recognizing and dealing with this addresses the problem of options, and we’ll expand on this later.

However there’s still one more problem: a store may very well contain many different kinds of products. Addressing the issue of combined product options works as long as you can assume the different kinds of options. The reality is of course, that you can’t. It makes sense for a t-shirt to have a group of options in the “style” realm, but that group might not apply to something like a pair of jeans, shoes, and probably not something even more removed like a camera. What emerges here is the notion of a product type, and not necessarily a type as in a class in object-oriented programming. I’ve started referring to this as a product’s stereotype.

Here’s where we’ve arrived:

A product is actually the parent of child products produced by combining all of the possible options available to that product’s type. The child products are the actual vendable goods, and inventory and pricing can then be done in a realistic manner.
The parent products are based on product stereotypes. It is the stereotypes that determine the groups of options available for composing the children. An example stereotype might be “Basic T-Shirt,” and may include two option sets: “Sizes” and “Styles.”

In the next post I’ll get into a few specifics, as well as provide an example database schema and object model.