Unfortunately I've been forced to re-invent the wheel and design a web-based product catalog / shopping cart system from scratch for one of my clients. I'm trying to build a DB schema that can be used for any type of product, including those that might have multiple colors, sizes, etc. I've attached a .sql file of my create table commands (the DB is PostgreSQL) but below is a brief description of how I see it working.
The product table holds some information about the product which right now is very basic. Then, each product can have a number of variables associated with it (such as color, size, language, etc) via the product_variable table. Then, each product_variable has a number of different allowed values for it which are stored in the product_variable_value table (such as R, G, B for color, S, M, L for size, etc).
Since the quantities stored for each "product" aren't in the product table, I need a way to map a single record to each "iteration" of a product based on its variables. This is also a way of telling what combinations of variables are allowed. I decided to create another table, product_sku, which holds the quantity and price for each combination of variables. Then, the product_sku_variable_value table "maps" several different variable values back to the sku. So, through many many table joins, you'll see that a SKU is a Polo Shirt, Size XXL, Color Red.
Does anyone have any general comments for this? How should I handle a situation where a product has only one sku, ie, it has no variable data? Also, I was wondering if it would be possible to ensure that when mapping variable_values to a sku, how one could enforce to make sure that all variable entries for a product are mapped back to the sku. For instance, you wouldn't want a sku that has just a color entry but no size.
I'm sure this has been done many times before and would love some input from anyone else out there who's done something like this. Thanks so much!