|A table design question regarding items with sub items.|
I'm trying to design a item price list table but I'm not sure how i should do it.
The client has a number of service items. Some of those items don't stand alone and are dependent on other service items. For example:
item 1: Chair - $20
item 2: Sofa - $40
item 3: Chair Scotch guard - $12.50
item 4: Sofa Scotch guard - $10.50
You can purchase a Chair item with or without purchasing its associated scotch guard item, however, you can't purchase any Scotch guard item on its own.
Initially, i designed the item price list table with each item record consisting of the main item/price and its associated sub item/price as follows...
rec1: chair | 20.00 | scotch guard | 12.50
rec2: sofa | 40.00 | scotch guard | 10.50
However, when i showed my design to another more experienced programmer, he suggested i separate the main and sub items and treat them as individual items as follows...
rec1: chair | 20.00
rec2: chair scotch guard | 12.50
rec3: sofa | 40.00
rec4: sofa scotch guard | 10.50
But the more i have to deal with his design during programming, the more i think my design is better. With his design, i have to create a link between main and sub items where i didn't have to before and creating a mysql query is more complicated.
I just don't see the benefit to his design over mine.
What do you think?
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'
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
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.
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?
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?