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

I prefer simple approaches that are easy to code. In your case I'd just have a table of products with a simple description of the product rather than trying to create fields for each possible variation of all possible products. This table would contain the price of the item, supplier etc.

I'd also have a product hierarchy table which might have 'Chocolates' at one level with 'Dark' and 'Light' as children under the parent of 'Chocolates'. You could have any depth (or width) of hierarchy and this would allow you to make easy searches for any type of product.

Code:

insert ProdHierarchy ( id, childName, parentName )
values ( 12, 'Dark', 'Chocolate' )

insert Products ( id, name, price, supplier, prodHierarchy ) 
values ( 123, 'Dark chocolate, 200g with hazelnuts', 0.8, 1234,12 )

Your approach is simply an EAV design which tends to be frowned upon on this forum - I'm definitely not against this methodology though () if it suits the application however I can't see how it improves on a simple name field that the user could search. Just my 2c.

Mike

i recently did a consulting job for someone with a product inventory that involved multiple sizes and colours and so forth, and he had a "hieararchy" as well

you would not believe the complexity when this hits the queries

my sincere advice: do not bother with the hierarchy

in the real world, every different variation has its own "stock keeping unit" and barcode (do a search for GTIN and GS1)

keep your database simple and your queries will also be simple

I agree that hierarchies can be a little more involved (especially if you have lots of them) but how would you provide the functionality he's after? His initial method certainly looks quite complex to me.

Out of interest what was so bad with the hierarchy on that job? was it poor coding, too many hierarchies, poor performance or just the use of hierarchies in the first place? I only ask as I recently responded to a post with a simple example of some hierarchy code and it didn't look like complex code to me. I may of been wrong to suggest a hierarchy (here and there) but I've certainly found them quite useful in the past. I'm only curious and not after a religious war on the subject

Mike