Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    9

    Hierarchy design - how to support sub tree searches?

    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?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Categories and Subcategories may have what you want
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2008
    Posts
    9
    Thanks...

    I like the 'Modified Preorder Tree Traversal' idea - I'm going to use that.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    That method works will with large, fairly static datasets. Otherwise, use the adjacency model.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    OP: If you want more information and thoughts about your idea, it is known as Materialised Paths.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •