Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBJGhe697053 for ; Tue, 19 Dec 2000 11:43:40 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.3) with HTTP id 1167250 for ; Tue, 19 Dec 2000 08:43:41 -0800 From: "Josh Berkus" Subject: Re: Tree structure table normalization problem (do I need a trigger?) To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.3 Date: Tue, 19 Dec 2000 08:43:41 -0800 Message-ID: In-Reply-To: <3A3EA494.3682000B@joerdens.de> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200012/218 X-Sequence-Number: 552 Frank, > However, I have > a problem now > which seems non-trivial: I am at some point in the tree, > say 3 nodes > down from the root, but I don't know where I am exactly > (across which > nodes would I travel along the shortest path to the top?) > and would like > to find out. This is, again, not really difficult if I > know how deep > into the tree I am, in which case I can simply do (I know > that I am 3 > nodes from the root and that my current node number is > x): This is exactly why my model includes a "Level" column. It was more important to me to have the easy queriability of the "redundant" level info than to have the fluid flexibility of a tree without it. The choice sorta depends on what you're storing in the tree. > (This is probably very expensive if the tree gets really > deep, but I > don't expect that to happen in my database anytime soon.) Not really. You're querying (hopefully) two indexed fields within the same table, refrenced to itself. Once you've run it a few times, even the elaborate UNION query I posted will run very quickly - on my table (~300 items) it runs <2 seconds. > This means > you need a loop control structure which means you have to > write a > PL/pgSQL procedure (or some other procedure) that is run > by a trigger to > update the level column on insert or update, as in > This seems to feasible but not really as straightforward > as one might > hope. Is there an easier way? Hmmm. I don't know, Frank. That strikes me as a really good, straightforward workaround to your problem. I'm not sure what you could do that would be simpler. This is practically a textbook example of why triggers are necessary to retain relational integrity. -Josh Berkus