Re: Hierarchical table design, child has multiple parent

  • From: Jared Still <jkstill@xxxxxxxxx>
  • To: sanjay.khangarot@xxxxxxxxx
  • Date: Mon, 10 Jan 2005 09:46:24 -0800

Do some research on BOM, also known as Bill Of Materials.

This is quite a common design and programming problem, that is
not always well implemented.

If the depth of the structure is known, and it will not change, you
may wish to implement with separate table for each level. That does
not work well though if one of the parents can also be a child.

If the depth is not known, you will likely want to use 1+N tables
(where N may be 0) and heirarchical queries.  In this case you 
would do well to implement on 10g as it has new features for
heirarchical queries.

On Mon, 10 Jan 2005 22:55:20 +0530, sanjay.khangarot@xxxxxxxxx
<sanjay.khangarot@xxxxxxxxx> wrote:
> Hello All,
> I wanted to create a schema in which Parent child relationship needs to be 
> maintained where a child can have multiple parents. Like addition of 
> ingredients to create a final product, where the ingredients are the parents 
> and resultant product is the child.
> How should I go to store this kind of data? My requirment also includes to 
> explode this structure and should be able to reach up to the top most parent 
> in the heirarchy?
> If anyone has any reference, from where I can pick this information please 
> advise.
> Thanks in advance.
> Regards
> sanjay

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: