Hello,

My first post here, I'm just glad to finally find a place to post my road blocks. Thanks to the creators of dBforums.

I am charged with having to write an inventory program. We have about 2000 parts in inventory. Every part has a unique part_num.

We have multiple end products, each end product is an assembly (example: Part # 35A-59000), end products are a mixture of parts and assemblies, but everything has a part number. Assemblies can be 10 levels deep, and could get deeper in the future. Here is another way of explaining my scenario, as I am not sure I am explaining myself very well:

A car is an end product, an assembly in the car might be the drive train, an assembly in the drive train would be a transmission, an assembly in the transmission might be a sensor, an assembly in the sensor might be a wire harness, an assembly in the wire harness might be a wire with terminals attached and a part in the wire might be a terminal end.

The car, drive train, transmission, sensor, wire harness, wire and terminal end all have a unique identifier known as a "Part Number".

The terminal end on the wire could be used in other assemblies on the car, or it could be used in other wire harnesses, or it could be used in harness' on other cars. Regardless, I have to be able to connect all these levels of assemblies and know what quantities of terminals we use for all assemblies for a particular car, and for all cars and for all assemblies (because we may sell the wire harness on its own to a customer).

My concern is that I should not create a table that has "part_num" in column one and it relates to "part_num" in column two because I would be relating part number to part number....a no no within a table...am I correct????

So how do I relate a part number (car), to a list of part numbers (drive train), to a list of part numbers (transmission) etc. so that later I can look at the car and say..... we use 387 wire end terminals in the car, 47 of which are on the drive train, 21 of those are used on the transmission, 11 of which are used on the sensor and 10 are used on the wire harness, 1 is used on each wire in the harness.

I am wondering if I am going to have to assign "Levels" to the assemblies and sub-assemblies but then I would have probably more than ten levels of sub-assemblies between end products and individual parts, which I would like to avoid if I can ( I know that that might be a prerequisite for making this work, but need someone's help on determining that). But even if I do that, I believe that you would still be relating part numbers to part numbers.... I need help.

Thanks in advance,
HansZen

You are looking at a recursive relationship for your parts. This would allow you to have as many "levels" as you need.

part(PartNumber, ParentPartNumber, attributes)

http://www.databaseanswers.com/data_...ture/index.htm