Skip site navigation (1) Skip section navigation (2)

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

From: Frank Joerdens <frank(at)joerdens(dot)de>
To: Michael Ansley <Michael(dot)Ansley(at)intec-telecom-systems(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Tree structure table normalization problem (do I need atrigger?)
Date: 2000-12-19 13:51:16
Message-ID: 3A3F67D4.B1D122C3@joerdens.de (view raw or flat)
Thread:
Lists: pgsql-sql
> 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

pgsql-sql by date

Next:From: Neil ThompsonDate: 2000-12-19 13:55:45
Subject: Re: Tree structure table normalization problem (do I need atrigger?)
Previous:From: Frank JoerdensDate: 2000-12-19 13:17:58
Subject: Re: Tree structure table normalization problem (do I need atrigger?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group