Received: from rakete.joerdens.de (rakete.joerdens.de [62.104.116.200]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBJDpG681754 for ; Tue, 19 Dec 2000 08:51:17 -0500 (EST) (envelope-from frank@joerdens.de) Received: from limedes.archi-me-des.de (pD900256D.dip.t-dialin.net [217.0.37.109]) by rakete.joerdens.de (8.9.3/8.9.3) with ESMTP id PAA17008; Tue, 19 Dec 2000 15:11:02 +0100 Received: from joerdens.de (tricky.archi-me-des.de [192.168.100.1]) by limedes.archi-me-des.de (8.9.3/8.9.3) with ESMTP id OAA00409; Tue, 19 Dec 2000 14:35:45 +0100 Message-ID: <3A3F67D4.B1D122C3@joerdens.de> Date: Tue, 19 Dec 2000 14:51:16 +0100 From: Frank Joerdens X-Mailer: Mozilla 4.72 [en] (WinNT; I) X-Accept-Language: en MIME-Version: 1.0 To: Michael Ansley , pgsql-sql@postgresql.org Subject: Re: Tree structure table normalization problem (do I need atrigger?) References: <7F124BC48D56D411812500D0B747251480F353@fileserver002.intecsystems.co.uk> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/213 X-Sequence-Number: 547 > Michael Ansley wrote: > > Hi, Frank, > > This is exactly why there are alternative solutions for trees. The mechanism that you > are using traded input speed for 'queryability', and this is where you start to run into > problems. Either you need to store redundant information (i.e.: the level) or you need > to have a recursive or iterative procedure which steps back up to the top. > > Just for illustration, if you were using a node type to indicate the ID of each node > (say, something like 1, 1.1, 1.2, 1.2.1, etc.) then input into the table would be an > expensive operation, but querying this would involve only getting out the ID of the node > that you were at, and splitting up the ID using the periods for separators. So if you > wanted to traverse up the tree from node 1.2.5.3, (you know that it's at level three > simply by counting the periods)then the order would be: > > 1.2.5.3 > 1.2.5 > 1.2 > 1 It almost looks my very first attempt to do a tree, except that I had the node number not in one column but a column for each number: Instead of 1.2.5.3 1.2.5 1.2 1 I had 1|2|5|3 1|2|5|0 1|2|0|0 1|0|0|0 > And you only need the original node ID to do that, which is a single query. > > If you are not going to be doing this query often, and/or there are not going to be many > levels, then you should probably consider the plpgsql solution, as it makes life simple > for the programmer, and the cost is not really an issue, and you don't have to maintain > redundant data. Denormalisation is a major pain when you start having to maintain it. What exactly is the problem with it (are you referring to the scheme I proposed or to the 1.2.3-type scheme)? Actually, I don't really know now why I left my original idea. I guess it looked to simple. I think I will stick with my recursive parentid PL/pgSQL-trigger type schema for now. I'll just have to get into that PL/pgSQL stuff which I haven't properly looked at so far. Would this be a denormalized table then? The level information would be redundant but linked with the other columns via the trigger, hence there's nothing much that could go wrong . . . or is there a hidden maintenance penalty? Cheers, Frank