I am working on a tools inventory and purchasing Db and trying to work out a way to manage the possibility of multiple sets.

Example I need 5 wrenches. Those 5 wrenches could come from a set that contains 6 wrenches. Or they could come from a set of say 11 wrenches. I would also like to assign costs to indvidual wrenches and wrench sets so that if it more cost effective to buy the set of 6 even though I only need 5, I can easily extract that information. I have a basic structure, with a Tools, Manufactures, and Suppliers table. I do not know how to proceed from here. Your help is greatly appreciated.

Sounds almost like a 'Bill of Material' (BOM) requirement, where you would define a hierarchical (self referencing) relationship between items.

Your 'Tools' record would be expanded to support or include tool 'sets.'

The basic structure would look something like this:

ToolID, ComponentID, ComponentQty

The ToolID and the ComponentID both have a foreign key relationship to the Tools table (to maintain the integrity of the data.) The ComponentID can also self-referencee another Tool Bill of Material ToolID. If the componentID does NOT 'point' to a child component in the BOM table, then it is a 'leaf' in the tree - there are no further recursions required for this record. If it does have one or more 'children,' then further recursion is needed.

The same tool can be referenced by many 'parents,' just as when building a car, 4 wheel assemblies would be referenced and used.

Tools
ToolID, ToolDescr, Cost, Unit Of Measure
1000, 3/8" Combination Wrench, $1.50, ea
1001, 7/16" Combination Wrench, $1.70, ea
1002, 1/2" Combination Wrench, $2.00, ea
1003, 9/16" Combination Wrench, $2.25, ea
1004, 5/8" Combination Wrench, $2.55, ea
1005, 11/16" Combination Wrench, $3.05, ea
1006, 3/4" Combination Wrench, $3.55, ea
1007, 5 Piece Combination Wrench Set, $10.00, ea
1008, 7 Piece Combination Wrench Set, $13.00, ea
1009, 2 Lb Hammer, $14.00, ea
1010, Mechanic Tool Set, $24.00, ea

BOM
ToolID, ComponentID, ComponentQty
1007, 1002, 1
1007, 1003, 1
1007, 1004, 1
1007, 1005, 1
1007, 1006, 1
1010, 1007, 1
1010, 1009, 1

In this BOM example, you have two bills shown - bill number 1007, which contains 5 wrenches, and bill 1010, which contains a 2-pound hammer and the wrench set...


In this approach, your individual tools or tool sets are described in the tools table.

The relationship between tools and tool sets are contained in the BOM table.

That looks simple and I think I can make that work.

Thank you very much.