How to model "products" in an online store application

Question

I'm building an online store to sell products like "Green Extra-large, T-shirts". I.e., the same shirt can have many sizes / colors, different combination can be sold out, different combination might have different prices, etc.

My question is how I should model these products in my Rails application (or really how to do it in any application).

My current thinking is:

Class Product
  has_many :variants, :through => :characteristics
  has_many :characteristics 
end

Class Characteristic
  belongs_to :product
  belongs_to :variants
end

Class Variant
  has_many :products, :through => :characteristics
  belongs_to :characteristic
end

So each product will have one or more characteristics (e.g., "Color", "Size", etc), and each characteristic will then have one or more variants (e.g., "Red", "Blue", etc).

The problem with this method is where do I store price and inventory? I.e., a given product's price and inventory are determined by the variants its characteristics take. (Green might be more expensive than red, large might be out of stock, etc).

One thought I had was to give products a "base_price", and let variants modify it, but this seems overly complex (and might not work).

Answer 1

I have seen two solutions to this kind of dilemma. The first is to try to use characteristics to define subordinate products to the "main" product. The challenge here is that in addition to your thoughts for far, in most cases the product will evolve with new manufacturers that bring new aspects to the table. For example, one manufacturer may make a cheaper product, but have a different application method for the logo or stitching that may be significant enough to track.

I think that carrying a non significant product number for each product and then attaching the characteristics as attributes works out the best. It is easily searched and extensible. If a group of products are strongly related, a ProductGroup that the individual products attach to works well.

In tables:

            ProductGroup
            --------------------
            ProductGroupID
            ProductGroupName
            ProductGroupDescription

            Product
            --------------------
            ProductID
            ProductGroupID
            QtyOnHand
            BasePrice
            ProductColorID
            ProductSizeID

            ProductColor
            ------------
            ProductColorID
            ProductColorName

            ProductSize
            --------------
            ProductSizeID
            ProductSizeName

            ...more attributes...

The advantages here are that you can easily query for specific attributes, attributes are "flexible" in that more can be added (and old ones adjusted: if you started with "Red" but then added another "Red" to the color pool, you can change them to "Maroon" and "Bright Red".

You can control price and inventory are at the detail product level (although more tables may be required to account for sourcing costs).

This all assumes that your characteristics are universally shared. If they are not, your characteristic subtable approach can work by creating a join table between characteristics and the product detail tables and populate as needed. This will require more business logic .to ensure each product category gets all characteristics necessary. In this latter case I would use "prototype" products in the base product table (with Qty and Cost of 0) that I would clone the characteristics from and then adjust as each new product is entered. As you move forward, when a new variation appears, having a "clone this product" function that allows you to just adjust the differences from the base product would be valuable.

Finally, as far as managing the inventory and pricing, this is going to happen at the UI layer. Being able to generate queries for related products (product groups) and manage all the pricing for related products will go a long way to making this livable.

Answer 2

Just a quick-note. You can always try and take a look at the sourcecode of some other e-commerce products like Spree and Substruct they probably already answered that question for you.