Hierarchy design - how to support sub tree searches?

davoutuk · #1 (**permalink**) 07-07-09, 15:17

I have a design problem that requires that a hierarchy support sub tree searching.

For example, take the following hierarchy....

Code:

World
   |--- India
   |--- USA
   |--- Europe
             |--- UK
             |      |--- London
             |      |--- Manchester
             |      |--- Edinburgh
             |--- France
             |--- Germany

There is a search requirement where if a parent node is selected then the search results should return all child nodes under that parent node, across all underlying subordinate levels. For example, selecting 'Europe' as the search parameter would return 'UK', 'London',' Manchester', 'Edinburgh', 'France' and 'Germany' as the results.

My table design is...

Code:

CREATE TABLE `geographic_location` (
  `GeoLocationIntID` int(11) NOT NULL auto_increment,
  `GeoLocationTitle` varchar(50) NOT NULL,
  `GeoLocationAbbrev` varchar(20) NOT NULL,
  `GeoLocationParentIntID` int(11) default '-1',
  `GeoLocationDisplayOrder` int(11) default '0',
  PRIMARY KEY  (`GeoLocationIntID`),
  UNIQUE KEY `GeoLocationTitle` (`GeoLocationTitle`)
)

I'm wondering how best to cater for this sub tree search requirement. One idea involves adding two new fields...

'alias' - a unique short alpha code for a location
'aliasPath' - a concatentation of all the alias values for an entry and all its parents

For instance, with...

Code:

World (alias: 'w')
   |--- Europe (alias: 'e')
             |--- UK (alias: 'uk')
             |      |--- London (alias: 'ldn')

.. then the aliasPath for London would 'w/e/uk/ldn'

Using this aliasPath would allow me to use LIKE style searches to find all subordinate nodes .

Thoughts?