Product Object for Ecommerce in NoSQL

Question

I'm working on an ecommerce app that will be using a NoSQL document database. I want to have a general purpose product that can be used for different types of products, ranging from coffee to furniture to electronics.

I thought one way to achieve this is to have "attributes" and "values" so that depending on the product, there can be different ones and I can use them as "options" for the user to select on the front-end.

For example, in the case of a tshirt, attributes and their values could be:

{
   "productOptions": [
      { "colors": ["red", "white", "blue"] },
      { "sizes": ["small", "medium", "large", "extra large"] }
   ]
}

There are 4 issues that I'm trying to solve here:

1. It's fairly typical that each unique configuration will have a different SKU number
2. In some cases, it's also fairly common to have a different price point for a particular SKU. For example, the regular tshirt price could be $9.99 but the extra large could be $12.99.
3. Another possibility is that a particular configuration option may not be available. Using the Product object example above, red color may not be available in extra large size
4. Most ecommerce systems will also allow merchants to upload an Excel sheet for importing their data into the database as nobody would like to create even 20 products with several options manually

This means, each product option/configuration may have to be represented with its own SKU and price in the database, meaning individual entries. Something like:

{
   "id": 123,
   "sku": "987-rs",
   "name": "Quality Brand Tshirt",
   "description": "100% Cotton Small Red",
   "price": 9.99
},
{
   "id": 234,
   "sku": "987-rxl",
   "name": "Quality Brand Tshirt",
   "description": "100% Cotton XL Red",
   "price": 12.99
}

I also thought I could have both the attribute/value in the database as well as all available SKU's, i.e. both examples above used togehter. This would allow handling product options easily on the front-end while still allowing for individual SKUs and pricing.

Another challenge with general purpose product design is that in some cases, there's no actual product configuration until the user creates it. For example, coffee or pizza. It would make no sense to create every combination of toppings and assign SKUs ahead of time. This situation is not unique to food products. The same scenario actually applies to regular products like clothing if any customization is involved.

I'm sure many others have already tackled this scenario and wanted to see how they handled it.

Answer 1

For design questions like these there is ALWAYS more than one answer. Also, not sure how it works on DBA Exchange but on Stack Overflow this question would get flagged as for soliciting an opinion and closed.

That said, it seems what you are trying to do is to build a master product list and also drive your UX. This is very difficult to do with a single data set. For your UX, you will need to build a matrix for your product and its variants that drive its behavior. For instance, if the shirt is out of stock for small sizes in the color red, you need to make that clear to the user before they add it to their cart. This matrix is essentially the cross product for every variant for each product. In your shirt example this appears to be sku, size, color, price, and also availability.

Using the attribute pattern you show at the top where you have product options listed as an array that hydrates the product page with choices is an approach I often see, especially since different products have different variants. Shirts have color and sizes, computers have CPU, memory, storage, etc. But how you represent the matrix of variants is quite specific to how you want to handle that data. A simple method is to create a second array off the root that has every combination of color, size, sku, price and availability that is referenced in your code in the client as a multi-dimensional array, such that if a user selects an extra-large shirt, your code looks for that item in the array that matches all the selected variants, then changes the price or disables add to cart if that combination of variants is not in stock, etc. whatever reflects the current state of that product and selection of variants.

The master data set in many cases, represents each product and every distinct variant as a separate document. This is because each of these is a different product with different inventory levels. Whether you get it this way from suppliers is another matter. Maintaining inventory levels for different products is tricky with a single document and causes big issues in high concurrency scenarios where multiple processes are trying to update the same document at the same time. Often the data set used by the UX is generated and updated from this master data set via some CDC (ChangeFeed, ChangeStreams, etc.) to reflect availability of items across all its variants or the addition or removal of variants as well. This way, if an item goes out of stock, the data set that drives the UX is updated, then once that is done, another CDC picks up the change to flush the cache for the variants or the product page itself, depending on how you construct these.

As mentioned, this is just but one way to handle products in a retail scenario. There are an innumerable set of ways to do this. Given that NoSQL databases are often used to provide data in the way it is used, one approach you might consider is to talk to the UX people to understand how they are designing the UX such that the data coming from the NoSQL database is served in the way that is most efficient.