I think a self-reference table with "m to n" relashionship should be a good
choice, something like this:
just pseudo-code, not correct SQL...


Table Product
ProductId integer PK,
Description varchar(80),
etc,
etc);

Table Components(
ComponentID integer FK to ProductID,
ProductID integer FK to ProductID,
Quantity numeric(12,4),
etc,
etc)

Lets talk about a product with ProductID = 1:

You can select all first level components with the following SQL:
select * from Product P join Components C on (C.ProductID = P.ProductID)
join Product PC on (PC.ProductId = C.ComponentID)

I think code a recursive procedure on the client is a good choice to make
Hierarchical Trees.

You could code Triggers to maintain a reference table that holds all
relations between Products and all components in any level, it could help
to do some tricks...


see you !

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br


At 11:56 02/12/2002 +0000, you wrote:

>Hi all...
>
>I just need a second opinion on something
>
>As part of a new app I have to implement a multi-level Bill-of Materials
>and am pondering the table structure to use
>
>ie - top level is level 1, parts that make it up are level2, if any of these
>are sub-assemblies - level 3 ?? etc
>
>I am sure this has been done loads of times - it is how to cope with
>multi-level
>sub assemblies I need input from
>
>
>Regards
>
>
>Roger P
>
>
>[Non-text portions of this message have been removed]
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/