A very good solution and something that we've implemented in one of our solutions, the only difference is that we also have a RelationshipType table hung off the HeirarchyRelationship table.

The code snippet below, is an example of how we set this up to cater for differnt Geographic needs with an organisation.

CREATE TABLE [Common].[Geography] (

[GeographyId] INT IDENTITY (1, 1) NOT NULL,

[GeographyCode] NVARCHAR (50) NOT NULL,

[GeographyName] NVARCHAR (150) NOT NULL,

[Level] NVARCHAR (50) NULL

);

GO

CREATE TABLE [Common].[GeographyRelationship] (

[GeographyRelationshipId] INT IDENTITY (1, 1) NOT NULL,

[GeographyId] INT NOT NULL,

[ParentGeographyId] INT NOT NULL,

[RelationshipTypeId] SMALLINT NOT NULL

);

GO

CREATE TABLE [Common].[RelationshipType] (

[RelationshipTypeId] SMALLINT IDENTITY (1, 1) NOT NULL,

[RelationshipTypeName] NVARCHAR (20) NOT NULL,

[RelationshipTypeDescription] NVARCHAR (50) NOT NULL

);

GO

This allows us to create many different variations from a single list of nodes and at a variety of grains, some down only as far as sub-regions (State/County), others as deep as City Street.

Using a CTE the SQL is very simular to the Article, with only the addition of a join to the RelationshipType table and filtered on the required Type of Hierarchy.