Design Assembly/SubAssemblies?

[Tony Saladino]

How do I create a database that can display multiple levels of sub-assemblies?

I'm trying to allow the customer to create these assemblies and easily access them.

Currently I have a part table that has the following fields:
Part_Id, Assembly. (There are other fields, but they aren't important to this)
Part_Id - Unique field to identify part (auto-increment)
Assembly - YES/NO to identify if this part is in fact an assembly made up of other parts.

I then have an assembly_part table that identifies parts in an assembly. It has Part_Id, Assembly, and Assembly_Part_Id.
Part_Id - Unique field to identify part (auto-increment)
Assembly - YES/NO to identify if this part is in fact an assembly made up of other parts.
Assembly_Part_Id - The Part_Id of the assembly that this part is a member of.

A set can then be created that has a one to many link between the Part_id of the "Part table" and the "Assembly_Part" table. This works great for showing one level of assemblies in a part list, but if I have multiple/indeterminant levels of sub-assemblies, I have no good way of allowing the user to see all the parts of the sub-assemblies.

Are there any suggestions of a better way to handle this, or a way to make this method display multiple sub-assemblies?

Thanks for the help. If this is a question that should be addressed elsewhere, let me know.

Tony

[Tim Kiebert]

Tony,

I have recently started building a maintenance app for our factory. My first stage which I have pretty well worked out I think is a multi level system to show all the equipment and the sub assemblies. I have it built to allow 5 levels. All the parts are in one table so that other sections of the application ie work orders, service history and the like can be related to a part at any level. You may get some ideas from this. It may not suit exactly because I am basically describing what is in the factory as opposed to picking parts to build an assembly. However the process of adding a part to a piece of equipment (which I am just at the stage of setting up) may work. Each part at the top level is set to be unique. Lower level parts have to be unique only within the part it belongs to. I have left my laptop at home today (so i can get some maintaing done) so when I get home (about 7 hrs from now)I will strip some of the test tables and forms, and add some instructions and post what I have so far.

[csda1]

Tony,

You have embarked on probably one of the two most difficult database problems to do (the other is a full family tree) within the confines of a database that is not a fully relational database system (these are few available like that).

You would have a table of subassemblies (which could also be a single part) related in a 1-to many of all parts and subassemblies that it uses.

Assuming you can have any number of levels and subassemblies could be used at any level, the best way is to do it programmatically (meaning Xbasic). You could then build a full list re-itterating through each subassembly until you got all of it's individual parts.

However, in most cases you can limit the levels of subassemblies, and if small enough (let's say 5 or so) can be done simply with 1 to many set relations.

[Tony Saladino]

Tim,

It sounds like I'm set up almost the same way you are. I've got a top level table that contains all parts/assemblies and they're treated as equals.

I've then got a table that associates any part/assembly with an assembly. This can be taken down as many levels I want to set up the set to do. I had discussed with my partner going 5 levels deep, as both you and Ira suggest, but she feels that we shouldn't limit the customer.

I would be more than happy to look at any information you but out there to see if it helps, but I don't want you to go through all that work knowing that I may already have something similar.

Ira,

Assuming that I'm going to iteratively walk through each sub-assembly to make a full list of parts, what is the best way to display this? I was envisioning doing something with the tree control, but after reading the documentation I don't believe that's a simple or even possible thing to do.

I guess the easiest thing to do would be to display the name of the assembly it's part of next to each part. And for lack of a better idea, I'm assuming I would drop the list into a browse.

Tony