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.
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.
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.
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