2

In the nested set model we have LEFT and Right columns

the first time when the table is empty, what i need to insert to the RIGHT column, if i don't know how many children will i have

LEFT 1 - forever

RIGHT ? - what value goes here??

how to make it dynamic? not static.

ps: using php

Mister PHP
  • 307
  • 4
  • 17

1 Answers1

7

I'm assuming from your tags and title that you are looking for a solution that works with MySQL.

Yes, you are right that unless you know the number of elements in advance the value for right needs to be calculated dynamically. There are two approaches you can use:

  • You could start with the least value that works (2 in this case) and increase it later as needed.
  • You could just make a guess like 10000000 and hope that's enough, but you need to be prepared for the possibility that it wasn't enough and may need adjusting again later.

In both cases you need to implement that the left and right values for multiple rows may need to be adjusted when inserting new rows, but in the second case you only actually need to perform the updates if your guesses were wrong. So the second solution is more complex, but can give better performance.

Note that of the four common ways to store heirarchical data, the nested sets approach is the hardest to perform inserts and updates. See slide 69 of Bill Karwin's Models for Heirarchical Data.

nested sets update difficulty

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • 1
    xmmmm, but if i use the adjacency list i will have to make many queries like LEFT JOIN etc...is to static for the depth, so remains the 2 designs path enumeration and closure tables which is better at your discretion? – Mister PHP Jul 07 '12 at 07:36
  • and also every node will have many childrens that they will have other childrens, it seems like the nested model is not good for this...so what design is better to use when you don't know how many levels you will have? or records – Mister PHP Jul 07 '12 at 07:38
  • That overview is very MySQL centric, because querying a sub-tree classifies as "Easy" for all those DBMS that support recursive queries (which is pretty much a mainstream functionality nowadays). –  Jul 07 '12 at 08:48
  • @a_horse_with_no_name: I've updated my answer to make it more clear that this is an answer for MySQL only. – Mark Byers Jul 07 '12 at 09:49