Recursive category problem...

Hello,

Here's the problem...

I have two tables in the DB-:

1. Category Table
Fields: id,name,sub_id
(id and sub_id are releated recursivly)

Eg. of a populated 'category' table!
1,Home,0
2,A,1
3,B,1
4,C,1
5,Avalanche,2
6,Bat,3
7,Carpet,4

2. Links
Fields: name,cat_id,date
(cat_id and id from category are releated)

Eg. of a populated 'links' table!
Link Home,1,2004/12/10
Link Carpet,4,2004/11/10
Link Bat,3,2004/10/10


Now, when i display all the (mainly top level) categories i need a method which would return me the date when the category was last updated! With that i mean the date of the link which was last added in that category (that includes any category under it and so on...).

So when i ask that method for the last updated date for say the 'Home' (topmost level) category from the sample data i have provided it should return me 2004/12/10.


------------------------------------
I have coded this but the problem is that this code is extremely heavy and as the links increase in the database i guess this way will soon give way. Anyway, let me write you my approach...

method returnLastDate(catid)
For all links (order by date desc)

if ( isChild(link[id],catid) )

return link[date]

end method returnLastDate

method isChild(son,dad)

if ( if son is a link in ANY subcat of dad )

return true

else

return false

end method isChild

(isChild is a recursive method which will return you true if son is a link in ANY sub category of dad)


I basically traverse through all the links (in descending order) and for each link i check whether it is a part of any subcategory of the category i need to get the last updated date for!
------------------------------------

Please suggest me an alternative approach as this one is toooo heavy and will eventually break down (it already is as i am getting these timeout errors frequently).

Thanks
Neville