I am currently designing the product/product variation tables for my e-commerce site.
my tables so far are as follows (ive taken columns that arent related to the problem out).
PRODUCT(prod_id*, name, desc, price) //product details
VARIATION(var_id*, name) //colour, weight, strength etc
VARIATIONS(vars_id*, name) //blue, green, 100g, 200g, 5x etc.
PRODUCT_VARIATION(prod_var_id*, prod_id, var_id)
this seems like alot of tables for the job
what i need to do is have products that can have two variations, for instance:
selling chocolate, variation 1 is weight, variation 2 is colour. there are two different weights. 100g and 200g. 3 different colours, white, milk, dark.
you can buy all colours at 100g, but only milk at 200g.
the way i can see this in a database is, having two PRODUCT_VARIATION_CHIOCES tables, one primary the other secondary. With the secondary holding the key of the primary.
So primary is colour, secondary is weight.
PRODUCT_VARIATION_CHOICES_PRI(prim_id*, prod_var_id, vars_id)
PRODUCT_VARIATION_CHOICES_SEC(prim_id, prod_var_id, vars_id)
I think there are two many tables, and it gets tricky when working out the price. would the price be in the PRI and SEC tables?
Any help is greatly appreciated.