Parts Database Table Design

[John Floyd]

I need to create a parts database to be able to lookup parts for different
machines. The problem I am having is trying to determine how to 'break-down'
the machines so that the parts can be found. I want to be able to use a drop
down list to select a machine name, then an area of the machine, then an
assembly, ect.
Two problems:
1- Some of the machines are small machines and do not need to be
'broken-down' as much in order to find a specific part or list of parts. For
example, One of the machines only reqires a machine name and sub-assembly to
find the part I would need. Another larger machine would require machine
name, area, assembly, sub-assembly and possibly more break-down to find a
specific part.
2- I would like to incorperate a drop down list of machine names, areas,
assemblies, sub-assemblies, ect. to eliminate the possibility of user error
when entering, or extracting, information. The problem I have is how to
link, for example, an assembly name to a specific area of a specific machine
when the same assembly name may or may not be used in another machine.
Thank you for any help or guidance you can provide.

 

[Graham Mandeno]

Hi John

Have you considered using a TreeView control, instead of individual combo
boxes?

Your hierarchy of "Units" could be represented by a single table, with
fields as follows:
UnitID (autonumber primary key)
UnitName
UnitType (a code representing machine/area/assembly/etc)
ParentID (the UnitID of the "containing" unit)

If each part is used only once, then your parts table can have a field for
the UnitID of the containing Unit.

Otherwise, as is more likely, if parts can be used in multiple assemblies,
you will need a junction table to represent the many-to-many relationship
between units and parts:
PartID
UnitID