>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).
It's doable in VFP, but it'd be much easier in a language that supports linked lists; C++, LISP, Pascal, and Modula alldeliver very strong list processing structures. Delphi, which is built on Object Pascal, might be a good choice for this application. While it'spossible to implement this in VFP, if the hierarchical self-referential structure is central to the app, another language probably would be a preferable tool.