Is it allowed to have multiple empty parentid's?

user_19830188 0
Viewed 38

So first I have a typical table that looks like this:

SectionID SectionName DivisionID DivisionName IndustryID IndustryName
SectorID1 SecNam1 DivisionID11 DivName11 IndustryID111 InduNam111
SectorID1 SecNam1 DivisionID11 DivName11 IndustryID112 InduNam112
SectorID1 SecNam1 DivisionID12 DivName12 IndustryID121 InduNam121
SectorID2 SecNam2 DivisionID21 DivName21 IndustryID211 InduNam211
SectorID3 SecNam3 DivisionID31 DivName31 IndustryID311 InduNam311

now I want to transform it in a more dynamic hierarchy structure with parentIds which looks like this:

ID ParentID Level Type Name
1 NULL 1 SectorID1 SecNam1
2 NULL 1 SectorID2 SecNam2
3 NULL 2 SectorID3 SecNam3
4 1 2 DivisionID11 DivName11
5 1 2 DivisionID12 DivName12
5 1 2 DivisionID21 DivName21
6 2 2 DivisionID31 DivName31
7 2 3 IndustryID InduNam111
8 2 3 IndustryID InduNam112
9 2 3 IndustryID InduNam121
10 2 3 IndustryID InduNam211
11 2 3 IndustryID InduNam311

Is it okay to have multiple empty parentId's?
Is this transformation ok?

data-modeling parent-child sql
1 Answers

Having the parent for the root node as null is fine - though in some designs this is populated with the same value as the record itself rather than having to deal with nulls e.g.

ID ParentID Level Type Name
1 SecNam1 1 SectorID1 SecNam1

Also, having different versions of the same value in the TYPE column looks odd. I would have expected just "SectorID" not "SectorID1", "SectorID2", "SectorID3". The difference between different sectors is handled by the name of the sector e.g. like you have done with "Division".

Did you mean the level of "SectorID3" to be 2 rather than 1 - or is that just a typo?

user_136583990
Related