I'm working on an e-commerce site that will be selling clothing. I was able to represent simple products just fine, but I'm stumped on adding product variants and then representing the products in inventory to manage the stock levels.

I'm using variants as the different options a piece of clothing can have (e.g. color and size). A product could have zero or more variants. Each variant has its own options. So, for color, we have yellow, green, blue, etc. and for size we might have small, medium, and large. I'm able to represent these relationships just fine and everything is working great. You can see my diagram (its a bare bones listing) here: http://joshhuckabee.com/files/products.png.

My issue now is figuring out how I can represent this in inventory so that for each possible variant combination the stock level and price could be different. For example, Shirt A comes in Red, Yellow, Blue, and Gold and its possible sizes are Small, Medium and Large. Simple multiplication says there's going to be 12 different inventory line items each with their own possible stock levels and prices. My question is, how do I get from my existing model to those 12 line items? I've been banging my head on this all day with no breakthroughs.

Any help is greatly appreciated!

Regards,
Josh