Re: Tree structure table normalization problem (do I need a trigger?) - Mailing list pgsql-sql

From Josh Berkus
Subject Re: Tree structure table normalization problem (do I need a trigger?)
Date
Msg-id web-1167250@davinci.ethosmedia.com
Whole thread Raw
In response to Tree structure table normalization problem (do I need a trigger?)  (Frank Joerdens <frank@joerdens.de>)
List pgsql-sql
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



pgsql-sql by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: SQL query not working when GROUP BY / HAVING is used
Next
From: Tulassay Zsolt
Date:
Subject: Re: Tree structure table normalization problem (do I need atrigger?)