Your design works well if you know you are only ever going to have one additional associated item. If you subsequently decide to add more associated items then you will need to add more columns and update your sql accordingly.

I don't think the alternative suggestion (as described) works particularly well though. The only link between rec1 & rec2 is the the fact they have "chair" in their description and you are correct that to write the sql to combine them is complicated (and inefficient).

I would suggest 2 seperate tables:

Items with the following columns

Item_Name, Item_Price

chair | 20.00
sofa | 40.00

Service_Items with the following columns

Service_Item, Associated_Item, Service_Item_Price

scotch guard | chair | 12.50
scotch guard | sofa | 10.50
cushions | chair | 20.00

The following select will then retrieve all the options for the chair item.

Select Item_Name, Item_Price, Service_Item, Service_Item_Price, Item_Price+Service_Item_Price as Total_Price
from Items, Service_Items
where Items.Item_Name = Service_Items.Associated_Item
and Items.Item_Name = 'chair'

Thanks,

The client does not think there will ever be any more than one associated sub item linked to a main item. So i don't think an additional table to hold associated sub items is necessary. The client can pay for the additional features when they are ready.

It would be interesting to read what others thinks as well.

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

product
id|title|url|description|retail|cost|etc....

product_options
id|option_name

product_option_values
id|option_id|prod_id|option_value|retail|cost|required

Then you have

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

rocknbil,

thanks for your take on the subject. however, i should of been more clear on what services items are. the client isn't selling chairs, they are selling services that can be applied to chairs, sofas and rooms. They are a carpet cleaning service that clean; room carpets, chairs and sofas. so there are no sizes. there is a fixed unit price for each service item and associated item.

i think that would change your concept a bit. sorry for leading you down the wrong path.

Would you still go for separate tables for "main items" and "associated items" to leverage the possibility the client may want more than one sub item per main item in the future?

or

Would you worry about the future later and keep original design with item/sub item pair records in one table?

After reviewing mark_rouch's post and rocknbil's suggested links, it appears that there is nothing wrong with using one table where each service item/price record also contains its associated sub item/price. It seemed obvious since there was a one to one relationship between items and sub items. The only down side, it appears, is that it limits its capability to the present.

Separating them into two tables leaves the door open and makes it easier to employ future capabilities without drastically affecting the existing architecture and programming.

Would that be a fair assumption?