[thelist] Proper DB Table Design...

[Mattias Thorslund]

Travis wrote:

>This is one of these situations where I found myself 2nd
>guessing my methods after I already did all the work (grin.)
>  
>

I recognize that :-)

>I've looked at some of those other models already actually, but they all
>seem quite a bit more complicated than the adjacent list model.  Anyhow, I
>think they might be overkill for my purposes (the tree is never more than 4
>levels deep and will only contain a few hundred items), so while I will
>certainly try to get more comfortable with some of these other methods for
>the future, for the time being I think I'd prefer to stick with an adjacent
>list - it makes the most sense to me.
>
>thanks!
>
>-travis
>  
>

A few hundred items and four levels deep can take quite a while (well, 
seconds)  if you retrieve your entire hierarchy in one operation. If 
you're only retrieving the immediate children, then wait for the user to 
make the next choice, it's a different story.

With your sortorder column you're pretty close to the "Flat Table Model" 
which is also a great way to go if the hierarchy isn't updated too 
often.  Re-sequencing will take time but most of the time you're just 
issuing a simple SQL query.

What took me a while to grasp about the "Modified Preorder Tree 
Traversal" model is that inserts and other changes  are actually very 
simple to handle.  I'll probably use it next time I have to implement 
something like this.

What I ended up using the last time around (for a hierarchical 
navigation menu) was a simple XML file, handling categories, internal 
and external links. 

Good luck!

/Mattias