mark_roach - 9:32 am on Jan 20, 2012 (gmt 0)
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
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'