>I have a table (project) wich has a relationship with itself, every record has a unique id and one field points to a parent record in the same table. This creates a hierarchy (without loops). Further I have an other table (activity) wich also has a relation with the first table (project one-to-many activity). Now my question is: how can I optimise these tables so that when I select a record in the [project] table the [activity] table will show all the associated records, this means when I select the 'root' record in project I will get the entire [activity] table, and when I select a 'leaf' record I will get only a few records from the activity table.
>
>I could create several indexes in the activity table wich will hold the 'parent' id, the 'parents' 'parent' id, the 'parents' 'parents' 'parent' id and so on. But this way the hierarchy can't be infinitely deep! Is there an other way to do this? (You must assume the hierarchy is infinitely deep and infinitely wide and every project has an infinite number of activity's).

Woter,
How and where you would show, will be editable or not etc would have different strategies. ie:
-Could be a treeview structure
-Could use ADO wt MS hierarchial grid
-Prepare and show in grid via a routine

All hierarchic levels could be in a field like :
1_3_567_78_
To squeeze field you could use bintoc() instead. For small sets this would work but for larger sets just holding :
NodeKey, ParentKey as in any traditional treeview structure sounds more feasible. Behind the scenes you could hide a treeview with node text holding say PK as char. inlist(myKey,chrtran(Node.Fullpath,'\',',')) would make an exression to select records for that level. You could have node.key as PK+'_' and simply search an elements position in treeview nodes(PK+'_') to get node.fullpath. Of course this again is only feasible for small sets for treeview loading is extra slow. ADO hierarchical set sounds more feasible. In ADO sets you could create an hierarchy in same table as you define (ie: employee, reportsto and s/he reportsto ...)
Cetin