List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 24 2007 5:04pm
Subject:Re: expanding hierarchies
View as plain text  
Naz,

 >The ability to manipulate trees with single queries
 >was what made them so scalable in my mind. What is
 >the better way to handle large frequently updated trees?
 >This is the best method I know, I'd love to
 >learn of a better one.

For large frequently updated trees & DAGs I prefer edge lists, examples 
at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.

PB

-----

Naz Gassiep wrote:
> Really? The ability to manipulate trees with single queries was what
> made them so scalable in my mind. What is the better way to handle large
> frequently updated trees? This is the best method I know, I'd love to
> learn of a better one.
> - Naz.
>
> Peter Brawley wrote:
>   
>> Naz writes
>>
>>     
>>> The definitive answer to anything that
>>> requires trees in SQL is nested sets.
>>>       
>> They are not definitive when the tree is large and must be updated
>> frequently.
>>
>> PB
>>
>> -----
>>
>> Naz Gassiep wrote:
>>     
>>> The definitive answer to anything that requires trees in SQL is nested
>>> sets. I have written a tutorial on the subject, as this is about the
>>> most asked question in DB relational data modeling.
>>>
>>> http://www.mrnaz.com/static/articles/trees_in_sql_tutorial/
>>>
>>> Enjoy :)
>>>
>>> - Naz.
>>>
>>> Peter Brawley wrote:
>>>   
>>>       
>>>> tbt
>>>>
>>>>     
>>>>         
>>>>> the number of levels in this table is unknown and the query should
> work
>>>>>  for any number of levels
>>>>>  please provide a sample 'select' query in mysql
>>>>>       
>>>>>           
>>>> That's a graph, which is recursive, so you need an sproc. See edge
>>>> list sprocs at
>>>> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html.
>>>>
>>>> PB
>>>>
>>>> -----
>>>>
>>>> tbt wrote:
>>>>     
>>>>         
>>>>>> Hi
>>>>>>
>>>>>> i have a table like this
>>>>>>
>>>>>> id      name        parent_id
>>>>>> 1       europe
>>>>>> 2       spain       1
>>>>>> 3       england     1
>>>>>> 4       france      1
>>>>>> 5       london      3
>>>>>> 6       mayfair     5
>>>>>> 7       madrid      2
>>>>>>
>>>>>> in this table each destination is mapped to a parent destination
>>>>>> eg: mayfair is mapped to london, london is mapped to england
> etc.
>>>>>>
>>>>>> i like to write a query to find child destinations when an id is
>>>>>> given for
>>>>>> a parent destination
>>>>>> eg: if 3 is the id, then the result of query should be 3,5,6
>>>>>> if 2 is the id, then the result should be 2,7
>>>>>>
>>>>>> the number of levels in this table is unknown and the query
> should work
>>>>>> for any number of levels
>>>>>> please provide a sample 'select' query in mysql
>>>>>>     
>>>>>>         
>>>>>>             
>>>   
>>>       
>
>
>   

Thread
expanding hierarchiestbt24 May
  • Re: expanding hierarchiesPeter Brawley24 May
    • Re: expanding hierarchiesNaz Gassiep24 May
      • Re: expanding hierarchiesPeter Brawley24 May
        • Re: expanding hierarchiesNaz Gassiep24 May
Re: expanding hierarchiesPeter Brawley24 May