This question is in relation to my need for a hierarchical structured database for hardware parts with Assemblies / SubAssemblies / Components. I have been told by a MicroSoft Access Database MVP that I will have problems with:
"You're in trouble if they don't fit that pattern, e.g. if you have one that
looks like this:
main assembly,
subassembly,
sub-subassembly,
component.
or like this:
main assembly
component."

Yes, we have these hardware assembly types (as well as the three below) so I have stopped building the data for MS Access tables.

Has anyone seen examples of a database (and/or tables) structured for hardware assemblies and subassemblies?

A. Key database writers have pointed me towards Joe Celko's book "SQL
for Smarties." Mr Celko has written "TREES & HIERARCHIES IN SQL" due out in April 2004 from Morgan-Kaufmann publishers. (Published database literature on hardware assemblies seems to be almost nonexistant).

B. Key terms in this search:
Bill Of Materials or BOM
trees, tree structure
hierarchies

C. Description of problem:
I am/was building tables for a new MS Access 2000 DB to control hardware data
for rod assemblies (rods move landing gear doors, ailerons, rudders, cargo
doors) for aircraft.

So a typical comparison of two would look like AssemblyE and AssemblyF below
(breaks down into 2 sub-levels).
And an extreme example would look like AssemblyRodG below (breaks down into
3 sub-levels, but we could anticipate 4 sub-levels).
(So far, I have compiled the data into 3 tables: Assemblies, SubAssemblies,
Components).

AssemblyE
ComponentRodJ
SubassemblyLeftEndA
ComponentX
ComponentY
ComponentZ
SubassemblyRightEndB
ComponentR
ComponentS
ComponentT

AssemblyF
ComponentRodK
SubassemblyLeftEndB
ComponentR
ComponentS
ComponentT
SubassemblyRightEndD
ComponentM
ComponentN
ComponentP

AssemblyRodG

ComponentRodL

SubassemblyLeftEndB
ComponentBearingR
ComponentWasherR
ComponentNutR

SubassemblyRightEndFittingE
ComponentFittingN
ComponentForgingM
ComponentBushingQ
ComponentBushingU
ComponentBearingV

Thank you for any guidance toward finding key writings or examples that you
might be able to provide.

Jim Jocius
CAD Designer/Administrator
CR Aerospace
Elgin, Illinois, USA

you issue is more or less one of object oriented design and parent objects (trust me). If you go to the microsoft office website you can down load a sample database that allows you to track family tree's. It may help.