E-commerce product variations

[shapeshifterx]

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)
PRODUCT_VARIATION_CHOICES(prod_var_id, vars_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.

Thank you,
Andrew

[hooray]

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)
PRODUCT_VARIATION_CHOICES(prod_var_id, vars_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.

Thank you,
Andrew

Ok I can see two ways to go with this, both with only 3 tables;
the first would be to simplify what you have:
PRODUCT(product_ID*, name, desc)
VARIATION(var_ID*, colour, weight, strength, price) // and any others
PRODUCT_VARIATION(provar_ID*, product_ID, var_ID)

going with your example of chocolate, this would mean an entry into the variation table for each variation, so
VARIATION("1001, white, 100g, 5x, £3" "1002, white, 200g, 5x, £5")

the other way, all i have done is simplified further:
PRODUCT(product_ID*, name, desc)
VARIATION(var_ID*, variation, value)
PRODUCT_VARIATION(provar_ID*, product_ID, var_ID)

so the variation table would include these kind of entries:
VARIATION("1001, colour, white" "1002, weight, 100g" "1003, colour, dark")
this is a simpler method in the short term, I personally favour this way, since I try to have as few variables in each table as i can.

I hope this is helpful to you.