Hierarchy Problem - recursivity between two tables
Hello people,
I have a structure about Procuct Composition, with the following tables:
Product:
ID_Product, Info_Product, Type, Level
Raw_Material_Composition:
ID_Product, ID_Raw_Material, Amount
SubProduct_Composition:
ID_Product, ID_SubProduct, Amount
The relationships between Product and the other tables are 1 x N.
The problem is, the ID_SubProduct should reference the ID_Product. A Product can be composed of a lot of raw materials and a lot of other products, and it can compose another products as well.
I cannot rename this field to ID_Product, because this will cause a cyclic reference.
How can I make this relationship ? Can Hierarchy resolve my problem ? How can I do that ?
I think Hierarchy could solve your problem - if you rename the ID_SubProduct as ID_Product and the ID_Product as something like 'Parent Product ID' in the Hierarchy statement ...see attached example with dummy data added as a possible solution.
Some kind of lookup to add the Sub Product Name to the Sub_Product_Composition might also make things simpler in the Hierarchy syntax (to match the example in the reference manual)
