[thelist] [DB Design] Recursive Directory Structure

Mike Migurski mike at saturn5.com
Sat Feb 1 20:04:00 CST 2003


>> You can do this quite easliy if you approach it as a recursive data
>> structure. You need to have a way to point one row at another in the
>> same table (child to parent) and to determine when you've reached
>> home.
>
>Yeah, but the performace hit scares me -- this table would be pretty big,
>and the server isn't the most robust piece of hardware I own. :)
>
>Is there a way to do this with JOINing the table on itself?

Rudy's example shows how to do it, if you are willing to only go down a
specific number of levels.

Personally, I find the recursive method to be much more elegant, so I just
cache the results. It's done once, the performance sucks, for superhuge
tables, but every sybsequent hit uses the cached version. If you are using
PHP, look into the function serialize and unserialize - they are inverses
of each other, and transform arbitrary data constructs to and from regular
strings.

Caching makes sense if your structure changes rarely. It's difficult to
code correctly, but if you do it well, you can generalize it to a a
thousand other purposes.


---------------------------------------------------------------------
michal migurski- contact info and pgp key:
                 http://www.saturn5.com/mike/contact.html

                "Freedom! Horrible, horrible freedom!"






More information about the thelist mailing list