Parent-Child Single Table Reference

Question

I can't find any information on tables referencing themselves in a parent-child relationship. Is it bad practice to have this? Should this be split in two tables for the parent-child relationship? There's a lot more columns in the real table. We are working on cleaning up the old records from this table and it can take a long time to delete a parent with numerous children. This table is used for numerous things throughout our application so a long running delete causes the table to be locked and our application hangs. I don't know that splitting this table will provide any sort of benefits but I am not strong in SQL.

|     HistoryItemID   |ParentHistoryItemID|        Data       |
|---------------------|-------------------|-------------------|
|          15         |        NULL       |  Starting Rule 1  |
|          35         |         15        | Subject Processed |

Answer 1

Here is a little working example of using a Recursive CTE to generate a hierarchy via hierarchyID datatype

Example

Declare @YourTable table (HistoryItemID int, ParentHistoryItemID int, Data varchar(50));
Insert Into @YourTable values 
( 15, NULL, 'Starting Rule 1'),
( 35, 15, 'Subject Processed'),
( 38, 35, 'Subject 1'),
( 42, 35, 'Subject 2'),
( 17, NULL, 'Starting Rule 2'),
( 55, 17, 'Rule 2 - Subject 1'),
( 56, 17, 'Rule 2 - Subject 2')
;

Declare @Top   int         = null      --<<  Sets top of Hier Try 35

;with cteP as (
      Select HistoryItemID
            ,ParentHistoryItemID 
            ,Data 
            ,HierID = convert(hierarchyid,concat('/',HistoryItemID,'/'))
      From   @YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(ParentHistoryItemID ,-1) else HistoryItemID end
      Union  All
      Select ID  = r.HistoryItemID
            ,Pt  = r.ParentHistoryItemID 
            ,Data   = r.Data
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.HistoryItemID,'/'))
      From   @YourTable r
      Join   cteP p on r.ParentHistoryItemID  = p.HistoryItemID)
Select Lvl   = HierID.GetLevel()
      ,HistoryItemID
      ,ParentHistoryItemID
      ,Data  = replicate('|----',HierID.GetLevel()-1) + Data  -- Nesting Optional ... For Presentation
      ,HierID_String = HierID.ToString()
 From cteP A
 Order By A.HierID

Results

If @Top was set to 35