Re: Using Ltree For Hierarchical Structures

2013/2/26 Thomas Kellerer <spam_eater(at)gmx(dot)net>

> Don Parris wrote on 24.02.2013 23:20:
>
> With many thanks to Misa and others who helped out with my question
>> about working with hierarchical data, I have now written a blog post
>> on how I implemented the ltree module to solve my problem.
>>
>> http://dcparris.net/2013/02/**24/using-ltree-hierarchical-**postgresql/<http://dcparris.net/2013/02/24/using-ltree-hierarchical-postgresql/>
>>
>> Frankly, if you work with hierarchical data, I'm not sure I could
>> recommend it strongly enough. I should think that even experienced,
>> advanced SQL gurus would appreciate the simplicity ltree offers, when
>> compared to the ugly table designs and recursive queries in order to
>> work with hierarchical structures.
>>
>> I really hope this blog post will help others in the same boat.
>>
>>
> How do you ensure referential integrity with this approach?
> (i.e. make sure that all elements from the path column actually point to
> an existing category)
>
> Thomas

Hi Thomas,

Yes we met that problem and it further makes deeper problems... i.e. what
if some category in up level - change his parent (updated path field) -
path must be changed for all childs...

Of several solutions - we have picked to use the best from both worlds...

So we still use - parent_id column... and ltree is used just as
materialized path - to improve performance... I think Materialized Views
what comming in 9.3 - (I still havent seen how it works) - will help in
that way - we will see...