db Architecture question

[dgkindy]

I am trying to create a spare pares generator but having trouble figuring out the architecture for the tables. I am not sure how to explain this so I think the use of an example will work best. In the example I will use a mouse as a the part, keep in mind this is only an example and would not actually expect someone to buy spare parts for a mouse.

My part# Manufacturer# Description
23 LM3432 Mouse


My customer calls up and says they need to fix the mouse, they have two options, they can buy a replacement mouse or parts to fix.

My part# Manufacturer# Description Cost
23 LM3432 Mouse $50


N/A 3 LED $2
N/A 4 Wheel $3
N/A 5 Left Button $10
N/A 6 Right Button $10

Some how I need to show that the group of 4 items are a repair kit to fix the mouse or just buy the whole mouse.

 

[ajetrumpet]

My part# Manufacturer# Description
23 LM3432 Mouse


My customer calls up and says they need to fix the mouse, they have two options, they can buy a replacement mouse or parts to fix.

My part# Manufacturer# Description Cost
23 LM3432 Mouse $50


N/A 3 LED $2
N/A 4 Wheel $3
N/A 5 Left Button $10
N/A 6 Right Button $10

Some how I need to show that the group of 4 items are a repair kit to fix the mouse or just buy the whole mouse.

I would think something like this would call for two tables: one for the items that you carry, and one for the parts of those items. Relate the two by the item or itemID field. When you get that, what comes to mind is setting up sort of a "holding" query where you can create a total field (based on an item selection in a form maybe?) that adds together any record in the parts table related to it's parent record in the items table. If you can do that, the data would be a lot easier to retrieve , say, on a form. And, you could quickly compare prices between the item itself and the cost of all the parts.