Best method to search hierarchical data
- by WDuffy
I'm looking at building a facility which allows querying for data with hierarchical filtering. I have a few ideas how I'm going to go about it but was wondering if there are any recommendations or suggestions that might be more efficient.
As an example imagine that a user is searching for a job. The job areas would be as follows.
1: Scotland
2: --- West Central
3: ------ Glasgow
4: ------ Etc
5: --- North East
6: ------ Ayrshire
7: ------ Etc
A user can search specific (i.e. Glasgow) or in a larger area (i.e. Scotland).
The two approaches I am considering are:
keep a note of children in the database for each record (i.e. cat 1 would have 2, 3, 4 in its children field) and query against that record with a SELECT * FROM Jobs WHERE Category IN Areas.childrenField.
Use a recursive function to find all results who have a relation to the selected area.
The problems I see from both are:
Holding this data in the db will mean having to keep track of all changes to structure.
Recursion is slow and inefficent.
Any ideas, suggestion or recommendations on the best approach? I'm using C# ASP.NET with MSSQL 2005 DB.
