Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    Elgin, IL
    Posts
    2

    Question Hierarchical DB solution/recommedation?

    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

  2. #2
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    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.
    Attached Files Attached Files
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •