Menu Model

[eluser]Think Floyd[/eluser]
Thanks for the advice, my problem has exposed difficulties in my schema it seems. I thought my schema was relational and OK, but it seems efficiency is the order of the day.

I've just found an article on MySQl Hierarchical Data http://dev.mysql.com/tech-resources/arti...-data.html

This is the sort of schema we are talking about? Lets hope restructuring my DB will result in simpler queries.

[eluser]jedd[/eluser]

I thought my schema was relational and OK ...

It was relational, but not good - like a drunk uncle at Christmas.

The nested set article is interesting - I've not seen it explained that well before. As Fuzzy pointed out there's at least one module you can easily plug in to your CI app.

Though for what you're doing, I'd suggest you don't need anything that complicated. The standard 3-column table will do you quite nicely. You're talking about less than a KB of data .. so you'll probably get worse performance if you wrap too much stuff around it anyway. You would probably get better performance by pulling the whole table in with one SELECT query and massaging it in within PHP into a multi-dimensional array.

It might cheer you up to know that I'm trying to work out how to deal with a 23-level hierarchy, with duplicate names in the lower levels, lots of gaps all the way up the hierarchy, and requiring some way of recording child nodes that have (historically) multiple parents. Yes. I thought it would.

[eluser]Think Floyd[/eluser]
Christ, I think I prefer a drunk uncle at Christmas.....

Although, I'm very much the wiser from this thread. Ta all... and yes; I was planning on prodding 7 tables for the menu query. Hehe.