Schema for hierarchical data-CodePudding

Schema of the tables that maintain hierarchical data:

Category table:

|   ID  |   Label |

Mapping table:

|   ID  |  ItemID   | ParentID  |

One of the requirements is to be able to map and construct a category tree similar to below:

Root
|
|---Wood (0)
|   |---Foundations (2)
|       |---Root Systems (4)
|           |---Closed Systems (5)
|
|---ETC (1)
|   |---Foundations (2)
|       |---Technology
|           |---Support
|       |---Root Systems (4)
|           |---Open Systems (6)

As you can see Foundations (2) can be a child of Wood (0) and ETC (1). I store this structure as below in the mapping table

|   ID  |  ItemID   | ParentID  |
|    1  |  2        | 0         |
|    2  |  2        | 1         |

I am in a conundrum. When constructing tree, how can I properly categorize Foundations as two different nodes one under Wood and one under ETC. Also, Foundations can have different or same children depending on the data. Note that Root Systems has Open Systems (6) as the child but Root Systems under Foundations has Closed Systems (5) as the child. Based on the current schema if I construct tree, it would look like this which is wrong:

Root
    |
    |---Wood (0)
    |   |---Foundations (2)
    |       |---Root Systems (4)
    |           |---Closed Systems (5)
    |           |---Open Systems (6)

    |
    |---ETC (1)
    |   |---Foundations (2)
    |       |---Technology
    |           |---Support

What changes should I make to the schema to accommodate my requirements? Is it really a hierarchical data?

=====================================

Rephrasing my question:

I need to be able to create schema for a tree which is represented as follows:

Root1           
|---    Foundations     
|---    --- Root Foundations    
|---    --- --- Caps
|---    --- Primary Foundations 
|---    --- --- Beams
|---    --- Retaining Walls 
|---    Down Systems        
|---            
|---            
Root2           
|---    Foundations     
|---    --- Root Foundations    
|---    --- Retaining Walls 
|---    --- --- Piles

I came up with below schema to store above hierarchical data:

CATEGORY Table  
id  label
1   Root1
2   Root2
3   Foundations
4   Down Systems
5   Root Foundations
6   Primary Foundations
7   Retaining Walls
8   Caps
9   Beams
10  Piles


MAPPING Table   
id  itemid  parentid
1   1   null
2   2   1
3   4   1
4   3   2
5   4   2
6   5   3
7   6   3
8   7   3
9   5   3
10  7   3
11  8   5
12  9   6
13  10  7

But I don't know how to write a query using common table expression or any other constructs to return the data in such a way that I can construct the tree. How can I modify my schema to be able to generate a tree in the above format?

CodePudding user response：

The following example demonstrates how to use a single table to represent multiple trees of categories. Note that while the hierarchies are accurately represented in the output that the order of categories in the second tree differs from the OP's example due to a rearrangement of the table rows of sample data. If the order is important then it will have to be specified explicitly, i.e. either based on the existing data (e.g. alphabetical by Label) or by the addition of another column to the Categories table to specify the order within a level of a tree.

To see the example in action: dbfiddle.

-- Sample data.
declare @Categories as Table
  ( CategoryId Int Identity, Label NVarChar(50), ParentCategoryId Int NULL );

insert into @Categories ( Label, ParentCategoryId ) values
  -- First tree.
  --   Rows in order of the categories within the tree.
  ( 'Root1', NULL ),
  ( 'Foundations', 1 ),
  ( 'Root Foundations', 2 ),
  ( 'Caps', 3 ),
  ( 'Primary Foundations', 2 ),
  ( 'Beams', 5 ),
  ( 'Retaining Walls', 2 ),
  ( 'Down Systems', 1 ),
  -- Second tree.
  --   Rows out-of-order to show that the hierarchy is not dependent on   CategoryId   order.
  ( 'Piles', 10 ),
  ( 'Retaining Walls', 13 ),
  ( 'Root Foundations', 13 ),
  ( 'Root2', NULL ),
  ( 'Foundations', 12 );

select * from @Categories;

And the good part:

-- Walk through the hierarchy.
with Trees as (
  -- Start from the roots, i.e. categories with no parents.
  select CategoryId, Label, ParentCategoryId,
    -- Assemble the path of categories as we go so that we can order the results rationally.
    Cast( '/'   Cast( CategoryId as VarChar(10) )   '/' as HierarchyId ) as Path
    from @Categories
      where ParentCategoryId is NULL
  union all
  -- Add each level of children.
  select Child.CategoryId, Child.Label, Child.ParentCategoryId,
    Cast( Parent.Path.ToString()   Cast( Child.Categoryid as VarChar(10) )   '/' as HierarchyId )
    from Trees as Parent inner join
      @Categories as Child on Child.ParentCategoryId = Parent.CategoryId
  )
  -- Display the result.
  select CategoryId, Replicate( '|---', Path.GetLevel() - 1 )   Label as Label, ParentCategoryId,
    Path.GetLevel() as Depth, Path
    from Trees
    order by Path;

Aside: Notice that well chosen column and table alias names make it much easier to understand code like ... from Trees as Parent inner join @Categories as Child on Child.ParentCategoryId = Parent.CategoryId.