NYCPHP Meetup

[Michael James]

Jim,

I had to build a tree for a project, and I found that de-normalization worked rather well.  In my case i had a tree of "subjects", therefore i created a "subject path" table with the following fields:

id                      
parent_id              
subject_id_0            
subject_id_1            
subject_id_2            
subject_id_3            
subject_id_4            
subject_id_5            
subject_id_6            
subject_name_0         
subject_name_1         
subject_name_2         
subject_name_3         
subject_name_4         
subject_name_5         
subject_name_6         
narrowest_subject_id   
narrowest_subject_name 
full_name             

also, i had a subject table:
id
name 


I knew that I would never go over 7 levels in my tree, but i could always add more fields to the subject path table if necessary.  Anyway, from the subject table and from the id and parent id fields in the subject path table, i can derive all of the other fields for each row in the subject path table.  (this would be something you can do as a pre-processed script, because you wouldn't want to figure it out during run-time since it would slow your site down.)   And I used these de-normalized fields to create the tree on the website using very simple queries (ex. SELECT narrowest_subject_name from subject_path where parent_id = 5)

Mike

>>> jim at bizcomputinginc.com 10/16/02 01:12PM >>>
I know this is a SQL question, but since most apps deal with SQL, I 
figured I'ld ask here since the app is using PHP and MySQL.

I have a table that establishes an entity that may have a parent from 
the same table.  I would like to display the data in a tree format where 
all items with a ParentID of 0 ( no parent ) are listed with children 
indented under the parent.  The nesting can be infinite, but 
realistically will only be 2 or 3 levels deep.  I can see how to do this 
through a whole series of queries, but can see how the performance of 
such a design could be very poor if there is a lot of items.  Looking 
for any ideas, even if they would require a table design mod to make the 
queries simpler.  The current design is a single table with ID, and 
ParentID and whatever other info needed for the item.

Thanks, Jim






--- Unsubscribe at http://nyphp.org/list ---