Depending on how you plan to use the data, you may be interested in the "Nested Set Model". There are articles listed at searchdatabase.techtarget.com if you search for that string, but you may need to register there (can't supply links as they contain user ID...). Or if you have Joe Celko's SQL for Smarties, a whole chapter is dedicated to this model.
If you are presenting one level at a time, the adjacency model (which is the one you are using) may be a good choice. If you want to perform cross-section operations or operate on entire subtrees, the nested set model would be better suited. HTH, Tore. ----- Original Message ----- From: "Adam de Zoete" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, February 24, 2003 12:46 PM Subject: Directory structure > Hi, > > I am trying to figure out the best method for creating a directory > with mySQL (Lasso). Let me first say that I am new-ish to SQL, so > might be jumping into the deep-end. > > I am building this for a site and am looking for a fast method of > displaying where users are in the directory, or where an entry exists > within it's structure. > > At the moment I have this table (just for the areas of the directory) > and another table for all the entries that live in the directory: > > ID BIGINT > Category VARCHAR(35) > Parent_ID BIGINT > Branch VARCHAR(255) > > +----+-----------+-----------+--------------------------------------+ > | ID | Category | Parent_ID | Branch | > +----+-----------+-----------+--------------------------------------+ > | 1 | Home | NULL | Home | > | 6 | Software | 1 | Home/Software | > | 13 | Internet | 6 | Home/Software/Internet | > | 34 | Servers | 13 | Home/Software/Internet/Servers | > +----+-----------+-----------+--------------------------------------+ > > I am not sure whether I have got any of this right, but I have > developed it this way in order to minimize the amount of searches > that need to be performed in order to show the Branch of multiple > entries within my search results. > i.e. as far as I am aware if I don't store the Branch alongside then > I have to loop through IDs and ParentIDs with multiple searches to > build the correct Branch for each entry that I am displaying in my > search results. This amounts to a lot of searches. > > My questions are: > > Should I be using separate tables for each category deep? If, so how > could I make sure it could grow deeper? > > Can I generate the Branch dynamically within my table(s)? > > Can anyone suggest a proven method for creating a flexible directory > structure that can grow and have categories that can have multiple > parents? > > Any suggestions would be greatly appreciated, > > Thanks in advance, > > Adam > > -- > // Adam de Zoete > \\ [EMAIL PROTECTED] > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
