Complex (For me anyway) Parent / Child relationship problem

[RDKDCRL]

Hello everyone!

I am in need of some expert help which I hope members of the forum can provide.

I am currently developing a database that will enable my department to account for tools and test equipment and one of the design criteria is causing me a headache. Let me explain:

I have a table listing all tools as individual items, each with a unique ID.

Criteria:

1. Each tool can be accounted for as an individual item. e.g. Screwdriver A.

2. Each individual tool may (or may not) be part of a set of tools, the parent tool set being listed as an individual tool in the table. e.g. Screwdriver A, part of Screwdriver Set, Cross Point.

3. Each individual tool may (or may not) be part of many sets. e.g. Screwdriver A, part of Screwdriver Set, Cross Point and Screwdriver Set, Various.

4. Each set can be part of other sets. e.g. Screwdriver Set, Cross Point can be part of Toolbox X, Y and Z (Toolbox X, Y and Z also being listed as an individual tools as they are different)


I have tried using a self join on another field but this fails criteria’s 3 and 4.

I have also tried using an additional table (tbl Sets) with Parent and Child ID but then I will have to include every single tool (that is not accounted for as part of a set) in that table. In other words ToolID = 1, ParentID = 1, Child = 1, and this would amount to several thousand additional records.

I believe that the way forward is probably using an additional table (tbl_Sets), but is this possible to create this multi relationship connection with, I believe, a left join?

Any help would be really appreciated, especially when it doesn’t make my head explode.

Many thanks in advance.

 

[plog]

How deep are your sets? Can a set that contains a set, be itself an item in a set?

Say Screwdriver A is part of Set 2. Say Set 2 is part of Set 5. Can Set 5 be part of Set 9? Etc. Etc.? Is there a finite level to how deep sets are, or do you have to account for the possibility of an indefinite set level?

Also, what do you want the output of this system to be? Some report that lists what every set contains? If so, when it encounters a set within a set does that then further break down whats in that subset or does it simply list Set 3 as a component of Set 8?

 

[RDKDCRL]

Thanks for your reply plog..

I think it would be wise for me to plan for the possibility of the depth of 10 sets, as some of our special tools sets can be rather complex. Obviously I would prefer the possibility of an undefined depth, but I am a realist!

The output will be varied, in this case the tool listing will be used as a reference for another side of the database which we will store holdings, receipts, issues, calibration and safety check cycles. On the basis of issues, forms and reports will be made available listing all tools issued, with the applicable sets broken down into their component parts. For instance, Engineer A will get a complete list of what he has on charge. Additionally, the option to print out a schedule of each set would be in order to allow our store and workshops to place this schedule with the equipment when it is in the store, and also used for temporary loan.
Many thanks

 

[plog]

I'm not entire sold on my idea, but here it is as a starting point for a conversation:

4 tables: Items, Tools, Sets, SetItems

Items will be a master table of sorts that has an autonumber primary key called ItemId and a field called ItemType which determines if that item is a Set or a Tool.

Tools will contain actual, individual tools (i.e. screwdriver, hammer, nail, etc.) and the data that goes with them. Its primary key will be ToolID which is its ItemID in the Item table.

Sets will contain collections of individual tools, this table will probably only have 2 fields SetID which is its ItemId from the Item table and SetName.

SetItems will be a table of two fields SetID from the Sets table and ItemID from the Items table (this allows both Sets and Tools to belong to sets).

Then, for a first level set query (shows all items in a set, but doesn't break down sets of a set) you would bring in your Sets table link it to the SetItems table and link that to Items which would display all the items of each set. It would look like this:

Sets--SetItems--Items

For a second level set query (shows all items in a set and all items in sets in a set, but doesn't show any sets that may appear in that second set) you would recreate your Level 1 query and then bring in SetItems again and Left Join Items to it, then bring in Items again and Left Join the second SetItems to it. It would look like this (left join represented by -->):

Sets--SetItems--Items-->SetItems-->Items

Then for each level deeper you add -->SetItems-->Items to the end of the previous level.