This is the first installment in a series that blends website architecture, data structures, and SEO marketing into a collaborative design pattern.
Designing a product catalog is one of those "better get it right" projects that any e-commerce firm faces. When you discuss lifespans of projects, this one has the longest lifespan of them all. Since I've been through this a couple of times, I thought I would share my thoughts and designs as I delve into yet another one.
There are a lot of political and technical pressures put on a product catalog from many departments within an organization including IT, Marketing, Executive, Operations, and particularly the "Industry Expert" within any company. It is important to not only recognize them, but to appreciate them. At the end of the day, almost everyone is "right" in their desires to have the catalog data serve them in a certain way. As you put yourself in their shoes by doing a proper discovery before you start designing you should try to not only understand what they want, but why they want it.
Atomic Data
Your marketing team will call this "flexibile product information", your IT team may call this "dynamic product data", but at the end of the day, it's product data that is smashed into all of its discrete component pieces.
This is one of the first pressures that will be placed on you and you need to be prepared to deal with it properly. It is important to understand that there is a competing struggle in any database design... Flexible vs. Fast. If you think of a product as a construction made from legos, then the properties of those products are the individual lego pieces. The concept of "atomicity" means that you can assemble your lego construction with Red, Blue and Green legos to make a space ship... and then you can rearrange those same Red, Blue and Green legos and build a house.
Now you've all seen the non-atomic way of building a product. It's a row in a product table and it tends to look like this:
You are limited however when you decide to stock a product that has a "Sub Sub Type", or a product that only has one color, or a product that has two vendor brands on it.
You also have a design flaw where you are "numbering instances" of properties. In this case "Color1" and "Color2" are going to cause problems for you when you want to search by "Color".
There is also a failure to properly "atomize" the data with things like "SubDept" being equal to "Ladies Apparel".
Let's compare this model to one that is fully "fourth normal" or highly "atomic".
Lets analyze this model. The product is statically registered in a much abbreviated product table. It serves now primarily as a hook that you can hang things from. We've decided to establish all of our atomic types as "Type", "Gender", "Vendor", "Brand", and "Color". You can see how this can be reused. For the "Live Strong Velocity Ladies Sport Top" it makes sense that Color (to this product) "means" White and Yellow... but to other products the same property of "Color" could "mean" other colors.
You can also see the intrinsic hierarchy here that establishes "Apparel" as a "top category" over "Top" and likewise, "Top" as a parent category over "Tank Top". This enables you to still utilize hierarchies in your product data representations while granting you also the ability to search ad-hoc through your product data in a non hierarchical manner by using the raw properties.
I have taken an apparel data model and created a good sample of how the property to product mappings for a decent catalog could be structured:
This model describes the relationship between products and properties but also illustrates some of the intrinsic relationships between the properties themselves. For example, if you mapped a City to a product, you could "infer" what State and Country relationship existed by recursing through the Property-to-Property relationships.
So... which data model is right? The answer could likely be ... Both! It really depends on your requirements which we will discuss in Part 2 - Best Business Practices for Product Catalog Data Structures - Speed versus Flexibility.