From oracle-l-bounce@freelists.org Mon Jan 10 11:46:55 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j0AHkri08388 for ; Mon, 10 Jan 2005 11:46:53 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id j0AHkqn08380 for ; Mon, 10 Jan 2005 11:46:53 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4291572C33F; Mon, 10 Jan 2005 12:53:28 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01370-70; Mon, 10 Jan 2005 12:53:28 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6DF7272C4C2; Mon, 10 Jan 2005 12:50:48 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=YnZpJhaWih4UtZnNKJ72eXhGqEj9fjJOuAszzrJ6OILQQQSLPC4jd3+YdXcbj0VJbZimTowdcrck7wf45luK9d8pbWlppE/L0QRE2xid5W6bJjkafhatJz94DY4j0M4qV4z4Qpsj7dNmn82WEX8iPDoTzCjN+A7cAKXp0+H5ClY= Message-ID: Date: Mon, 10 Jan 2005 09:46:24 -0800 From: Jared Still To: sanjay.khangarot@wipro.com Subject: Re: Hierarchical table design, child has multiple parent Cc: oracle-l@freelists.org In-Reply-To: Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: X-archive-position: 14550 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jkstill@gmail.com Precedence: normal Reply-To: jkstill@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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. (sub-assemblies) 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@wipro.com 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 -- http://www.freelists.org/webpage/oracle-l