Re: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Jan 20, 2011 at 7:00 AM, Richard Quadling <rquadling@xxxxxxxxx>wrote:

> I'd recommend using a nested set approach for the tags
> (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
> gives a good explanation on the issues and methodology of nested
> sets).
>

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.

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.

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.

On Thu, Jan 20, 2011 at 11:40 AM, Jerry Schwartz <jerry@xxxxxxxxx> wrote:

> 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.

On Thu, Jan 20, 2011 at 11:59 AM, Dotan Cohen <dotancohen@xxxxxxxxx> wrote:

> 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. 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.

David

[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux