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
