I don't understand.. Can't you just use Hierarchy combined with integer as the primary key?

For example, I'm using ChecklistID and ChecklistItemID in this example in order to store multiple parent child hierarchies.. in the same table.

CREATE TABLE [dbo].[ChecklistItems](

[ChecklistID] [int] NOT NULL,

[ChecklistItemID] [int] IDENTITY(1001,1) NOT NULL,

[ChecklistItemName] [varchar](50) NOT NULL,

[ChecklistItemParent] [int] NOT NULL,

[OrderBy] [smallint] NOT NULL,

[ChildLevel] [tinyint] NOT NULL,

[ChildLevelDesc] [tinyint] NOT NULL,

[ChecklistHierarchy] [hierarchyid] NULL,

[HtmlPrefix] [text] NULL,

[HtmlSuffix] [text] NULL,

CONSTRAINT [PK_ChecklistParentChild] PRIMARY KEY CLUSTERED

(

[ChecklistID] ASC,

[ChecklistItemID] ASC

)

)

Now I can easily generate a checklist hierarchy when I pass in a single checklistID into this sproc spCreateChecklist

CREATE procedure [dbo].[spCreateChecklist]

(

@checklistID int

)

as

with H(ChecklistID, ChecklistItemID, Level, ChecklistItemName, ChecklistItemParent, FQName)

As

(

Select ChecklistID, ChecklistItemID, 0, ChecklistItemName, ChecklistItemParent, Convert(varchar(max), ChecklistItemName)

From checklistparentchild

Where checklistItemID = checklistItemParent

and ChecklistID = @checklistID

UNION ALL

Select C.ChecklistID, C.ChecklistItemID, H.Level + 1, C.ChecklistItemName, C.ChecklistItemParent, Convert(varchar(max), H.FQName) + '.' + Convert(varchar(max), C.ChecklistItemName)

From checklistparentchild C

INNER JOIN H on H.checklistItemID = C.checklistItemParent

Where C.checklistItemID != C.checklistItemParent

and C.ChecklistID = @checklistID

)

Select *, SPACE(Level*3) + ChecklistItemName as IndentedName

From H

Order by FQName

Using Views, I think that this will be quite straight forward to navigate / flatten... of course, I've been using views to flatten parent-childs for a decade.

I just don't understand why you haven't considered this as an option

I'm at this post because I'm trying to change ChecklistItemID to ChecklistHierarchy in this example, I haven't been able to get my head around what you're having difficulty with, sorry.

-Aaron

MCITP: DBA SQL Server