Best method to search hierarchical data

Posted by WDuffy on Stack Overflow See other posts from Stack Overflow or by WDuffy
Published on 2010-03-15T14:27:25Z Indexed on 2010/03/15 15:29 UTC
Read the original article Hit count: 237

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:

  1. 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.
  2. Use a recursive function to find all results who have a relation to the selected area.

The problems I see from both are:

  1. Holding this data in the db will mean having to keep track of all changes to structure.
  2. Recursion is slow and inefficent.

Any ideas, suggestion or recommendations on the best approach? I'm using C# ASP.NET with MSSQL 2005 DB.

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about c#