Yesterday ltree was mentioned to be a good system for tree structured
table data. I and a colleague of mine have been playing around with the
examples and the (rather sparse) documentation, but we're stuck on a few
questions...
How does one guarantee referential integrity using ltrees? It doesn't
seem to do so by itself, but can it refer a parent node directly?
We assume you can do this:
CREATE TABLE my_tree (
path ltree PRIMARY KEY,
parent ltree REFERENCES my_tree(path)
);
In this case a tree would look something like:
parent | path
--------------------------
(NULL) | A
A | A.B
A.B | A.B.D
A | A.C
That's the "classical" way, which is also used in our current
implementation with integers instead of ltrees, but it's not very easy
to query efficiently (at least ordering seems to remain a problem).
Maybe something along the lines of the following is possible?:
CREATE TABLE my_tree (
path ltree PRIMARY KEY REFERENCES my_tree(path)
);
Data would look like:
path
-----------------------
A
A.B
A.B.D
A.C
With A.B and A.C referencing A in their parent record and A.B.D
referencing A.B
What I like about this solution is that only one ltree path per node is
required, and that the root node doesn't need a parent reference. The
question is whether this is/can-be-made possible...
Do ltrees know that a node with path 'A.B.D' references it's parent
'A.B'? I mean, can ltree 'A.B' equal ltree 'A.B.D' somehow while the
strings are unequal?
Can it be made to know that somehow (functional foreign keys or
something - maybe using "ltree_isparent(ltree, ltree)")?
I can determine things like this with a few experiments, but I want to
know "the right way" to work with ltrees and referential integrity. How
do people use this?
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
