Re: Tree structure table normalization problem (do I need a trigger?)

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