On Thu, Jan 20, 2011 at 22:05, David Harkness <davi...@highgearmedia.com> wrote:
> Thanks for the link. That article proposes an interesting way to organize
> the categories. Have you implemented this in the wild? Clearly the design
> would work as it's pretty simple, and I like that it removes the need for
> recursive queries.

I am also interested in knowing if this approach is used in any production code.

> Dotan, the Venn diagrams are just used to explain the concept. If you use
> the code to determine the left and right values, you can ignore the diagrams
> entirely. As long as you're not adding/removing categories every minute,
> having to recalculate left and right values isn't that big of a deal.

I understood that. My concern is exactly with adding new nodes. There
is no incrementor (++i) in SQL, so knowingly coding a solution that
will require incrementing two fields in half the database rows seems

> Also, there's no reason you couldn't keep the parent_id field with the
> nested sets. It would come in handy for certain types of queries, though
> it's not necessary.

That is true. I could store both methods, and experiment to see which
is preferable. But what a mess this would be if the two methods go out
of sync! Isn't there a name for that in SQL, something along the lines
of not storing the same data in two places lest one should change and
not the other? The term escapes me.

>> I disagree. The method I proposed can be extended to any depth, and any
>> leaf or branch can be retrieved with a single query.
> The nested set method can be extended to any depth, and it pays off more the
> larger the hierarchy grows. While you can retrieve any branch (all
> ancestors) of a node with a single SQL query, the SQL engine itself actually
> must perform a recursive query meaning multiple hits on the parent_id index.

That pays off more? For the guy writing code or for the database
memory requirement?

>> I suppose for retrievals this structure has advantages, but unless
>> MySQL has a ++ operator (or better yet, one that adds or subtracts 2
>> from an int) then it looks to be a pain to add nodes.
> ++ or += wouldn't be any better here than x = x + 2. Once you're modifying
> indexed values, you'll pay a much higher price writing to disk than += could
> ever save you in CPU cycles. The beauty is that inserting a node requires
> only two update statements that will fix *all* categories that need to be
> adjusted.

Only two update statements, but they are affecting on average half the
database's rows!

> Adding categories to the hierarchical model is definitely faster
> so it comes down to your insert-to-select ratio. Moving a subtree is also
> much easier with the hierarchical model.

Which do you call the hierarchical model? That term is not used in the
linked article.

Dotan Cohen


PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to