I'm working on building a tree structure in MySQL and have been experimenting with different ways of representing the data. However, no matter how I slice it, there are shortcomings.

The nested sets model allows me to easily select entire branches of the tree - which I need to do. However it is not so easy to select immediate children of a node.

The adjacency list model is great for getting immediate children, but not so good for returning entire branches of the tree.

I'm wondering, is there anything particularly bad about building a tree structure like this:

TABLE: Tree
    ID
    name
    lft
    rgt
    parentID

So what I have is the adjacency list model and the nested sets model all in the same table. That way I c开发者_运维技巧an use either / or, depending on the circumstances of what I am selecting for.

What are peoples' thoughts? Is this allowed? (Peanut butter and chocolate together at last?) Or is this considered bad design?

Thanks in advance,

I highly recommend it myself. This is the style which CakePHP uses for its tree structures, and as you pointed out, it gives you great flexibility. You just need to do a bit more work to ensure the integrity of your tree, but it's quite worth it.

No, its not bad design at all. At least in my opinion.

I would however only implement one. For example, I used Adjacency List Model as my prime method of adding new elements and retreiving.. (obviously retreiving since its so easy) however, I also added parentIds for Nested Set.

The benefit of this is that I can rebuild my ALM at any time and can easily move things quickly providing I keep the parentIds.

I would not use both in your code however, just have it there in the background...

Edited Comments

You mention that it is not so easy to select immediate children. I think you might be doing something wrong. It is extremely easy even in Adjacency List to select the immediate children.

If you create a new question I will answer how to do it in that.