sql - Representing ecommerce products and variations cleanly in the database

Question

I have an ecommerce store that I am building. I am using Rails/ActiveRecord, but that really isn't necessary to answer this question (however, if you are familiar with those things, please feel free to answer in terms of Rails/AR).

One of the store's requirements is that it needs to represent two types of products:

1. Simple products - these are products that just have one option, such as a band's CD. It has a basic price, and quantity.
2. Products with variation - these are products that have multiple options, such as a t-shirt that has 3 sizes and 3 colors. Each combination of size and color would have its own price and quantity.

I have done this kind of thing in the past, and done the following:

• Have a products table, which has the main information for the product (title, etc).
• Have a variants table, which holds the price and quantity information for each type of variant. Products have_many Variants.
• For simple products, they would just have one associated Variant.

Are there better ways I could be doing this?

Answer 1

I worked on an e-commerce product a few years ago, and we did it the way you described. But we added one more layer to handle multiple attributes on the same product (size and color, like you said). We tracked each attribute separately, and we had a "SKUs" table that listed each attribute combination that was allowed for each product. Something like this:

attr_id   attr_name  
1         Size  
2         Color  

sku_id    prod_id    attr_id    attr_val  
1         1          1          Small  
1         1          2          Blue  
2         1          1          Small  
2         1          2          Red  
3         1          1          Large  
3         1          2          Red  

Later, we added inventory tracking and other features, and we tied them to the sku IDs so that we could track each one separately.