[AccessD] BOM - Table Structure For Handling Common Parts InVarious Assemblies/Sub-Assemblies.

I'd say the best approach would just be to have a separate ID table.
ItemID and ChildID (which be the itemID of any child parts).  That gives
you a one too many relationship, and it doesn't matter if the same part
is a child of multiple parts.

How to build a SQL view of this....well, honestly, that's not my forte.
I tend to build classes in code (VB, asp, and now .Net).  In those
cases, this type of table structure is easily broken down with the
appropriate class structure.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D. Tejpal
Sent: Friday, June 18, 2010 1:02 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] BOM - Table Structure For Handling Common Parts
InVarious Assemblies/Sub-Assemblies.

    Typical fields for a table having hierarchical list of assemblies
and parts are observed to include:  ItemID, ParentID, Description, Qty

    With this arrangement, the complete hierarchical chain from each
topmost item (having no immediate parent) downward can be worked out
right upto the lowest part (having no child).

    In situations where a part with same ItemID is needed for more than
one Item chain (i.e. more than one top parent), the above table
structure might not prove adequate. 

    Any suggestions - while retaining its role as a self contained table
?

Best wishes,
A.D. Tejpal
------------
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity 
to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
Sensitive material. If you are not the intended recipient, please contact the sender 
immediately and destroy the material in its entirety, whether electronic or hard copy. 
You are notified that any review, retransmission, copying, disclosure, dissemination, 
or other use of, or taking of any action in reliance upon this information by persons 
or entities other than the intended recipient is prohibited.