Parts Database Table Design

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.

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

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


"John Floyd" John wrote in message
...
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.