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?