Need to create a BOM database

I am working on an application that could use the Bill of Material database methodology.
The application consists of items, sub items, and sub-sub items down to 5 levels.
Each item has an ID and a name.

For Example:

Main Item - Level 0   No parent

Item A        Level 1
      Item A1      Level 2
           Item B1     Level 3
           Item B2
      Item A2
           Item B3
           Item B4
           Item B1
      Item A3
           Item B1
           Item B4
           Item B5

Item A is the parent of Item A1.  Item A1 is the parent of Items B1 and B2.
Item B
      Item A5
           Item B1
           Item B5
      Item A2
           Item B5
           Item B4
           Item B1
      Item A6
           Item B1
           Item B3
           Item B5
I am using only one table. However I believe that I need an Item master and an Assembly table.
I worked with the BOM years ago and have forgotten the database structure.

This is what I have:

tblMaster
Columns:
     ItemID
     ItemLevel
     ItemParentID

I need to query the database as folllows:
For a given Mstr_ID and Level, Return alll the immediate parents.
SELECT ItemName, ItemParentID, ItemParentName from tblMaster WHERE ItemID=B3 and ItemLevel=3.

Should return A6 and A2.

I need a component table that has only one row for each item that is at level 1 or lower.
tblItemComponent

Columns:

CompID - Foreign key to the ItemID of tblMaster
CompName

Can you supply references?  I remember enough to understand the references.

Thanks,

     




Start Free Trial