rocknbil - 4:39 pm on Jan 20, 2012 (gmt 0)
The client does not think there will ever be any more than one associated sub item linked to a main item.
Ha . . . how many times have I heard that one. :-)
I'd do it something like so
Then you have
option: medium, small, large (required selection, may vary in price)
option: Scotchguard (add $2.50)
(I know the chairs don't vary in size, but bear with me - there may be other products that vary in size, or maybe the chair varies in covering/finish and that changes the price. Same concept.)
You use the required field to leverage whether the "base price" changes. For example, if small, medium, large vary in price, you'd leave the product table price at 0.00 and the base price is based on the option. If it's *not* a required option, you add the option price to the selected base price.
So you would have one unique value named "Scotchguard" (and your programming would make sure it's unique) that could apply to many items. This is a good step to database normalization - elimination of many rows containing the same values.
This gives you unlimited possibilities for expansion without restructuring the tables every time the client changes their mind (or, next year, those options change.) and it will happen. :-)
Then enter inventory and "what's in stock" - another set of tables join on these to make all that work. It makes for some pretty complex selects but once you work that out it's well worth it.
Here's one thread [webmasterworld.com] that explores the concept a little further (in a simplified way,) and another [webmasterworld.com], it's discussed often here.