Post reply

Building a self-referencing hierarchical table.

  • May 21, 2014 at 3:26 pm

    #289144

    Greetings all 😀

    So I have been doing some coding, and mostly scouring the internet for this today to no avail. I have a rather over-coded shotgun approach to this, but I was wondering if there were good solutions to this problem. An example of what I am talking about is the employee table in the Adventureworks database. This has employeeID and then ManagerID, ManagerID just being the EmployeeID of the person whom the original reports to.

    I know the queries for querying this type of data and even making recursive common table expressions. What I cannot seem to find is how one goes about BUILDING said table. I see all sorts of examples where people are just doing INSERT table VALUES () manually to load the table. The problem is, I need to create a table that has potentially thousands of records.

    It will essentially be a dimensional map. Don't even get me started as to they why, I will just suffice to say that is what the client and project want :-D. I have a process that will do this now, but it is not very dynamic and very hard coded. To me, there seems like there should be some sort of standardized methodology for handling this.

    Does anyone have any good info or resources on this? Any help is greatly appreciated!

    Link to my blog http://notyelf.com/

  • May 21, 2014 at 3:47 pm

    #1715515

    These articles might help you:

    Hierarchies on Steroids #1[/url]

    Hierarchies on Steroids #2[/url]

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 21, 2014 at 5:26 pm

    #1715529

    Do you want just a voluminous test table of randomized nodes? If so, the articles that Luis poinnted you to have just such a generator.

    If you want something where the structure is known and, perhaps, even controlled, we need some more information as to what the rules are for the structure.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 21, 2014 at 6:01 pm

    #1715534

    Thank you for the articles Luis, most informative! I also now know what I am looking for and referencing 🙂

    Jeff, after reading your articles, I understand now what I am trying to build which is the actual Adjacency List, not the actual Nested Set (though I may use that down the line depending on the needs).

    I know the data, and have the data in separate dimensions. What I am trying to do is combine hierarchical data from separate dimensions into one Adjacency list. So in the context of these articles, lets say each level within the employee table is it's own table, and does not have an ID. The levels and names are known, but the ID's created would be surrogate keys, and ParentIDs of the lower levels would need to map to the surrogate keys of the parents above them.

    Hopefully that made sense 🙂

    Link to my blog http://notyelf.com/

  • May 21, 2014 at 6:41 pm

    #1715538

    shannonjk (5/21/2014)

    Thank you for the articles Luis, most informative! I also now know what I am looking for and referencing 🙂

    Jeff, after reading your articles, I understand now what I am trying to build which is the actual Adjacency List, not the actual Nested Set (though I may use that down the line depending on the needs).

    I know the data, and have the data in separate dimensions. What I am trying to do is combine hierarchical data from separate dimensions into one Adjacency list. So in the context of these articles, lets say each level within the employee table is it's own table, and does not have an ID. The levels and names are known, but the ID's created would be surrogate keys, and ParentIDs of the lower levels would need to map to the surrogate keys of the parents above them.

    Hopefully that made sense 🙂

    It actually does make sense. The only problem would be that there needs to be something in, say, Level 3 that would identify (by hook or by crook) the related "parent" node in Level 2 in order to convert this to an Adjacency List. Is there anything in your separate dimensions that can do such a thing? If not, this is going to be a row to hoe manually.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 21, 2014 at 6:44 pm

    #1715539

    Ah yes there are in fact 1 to many relationships between the tables so there are Ids that link them together, I just don't want/need the resultant Ids on the resultant recursive table as they are only really useful for referencing the tables themselves. So level 1 does have ids to link to multiple records on level 2 and level 2 as well to level 3.

    Link to my blog http://notyelf.com/

  • May 21, 2014 at 6:57 pm

    #1715543

    Not a problem there. Those columns would only exist long enough to build the Adjacency List which would use surrogate IDs to map the "positions" in the Adjacency List. That way, you can have individuals in multiple positions without creating "cycles". If you're sure that individuals only occur once across all the dimensions, then we'd could do the same thing using the employee IDs.

    How many dimension levels do you have?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 21, 2014 at 7:41 pm

    #1715546

    That is what I figured but wanted to be clear on that just in case. As for the levels right now there will be a total of 5.

    Link to my blog http://notyelf.com/

  • May 21, 2014 at 11:30 pm

    #1715563

    5 Levels is easy. Since you have 5 different tables, you'd need 5 queries and that will allow you to avoid dynamic SQL. The first query would be simple. Just copy the data you need from level 1 (the root of the "tree" and supposedly the smallest level). Then join Level 2 to that and copy the info that you need from Level 1 and 2 to make your adjacency list. Do the same for the other 3 levels and I believe you'll be done.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 23, 2014 at 3:43 pm

    #1716272

    Thank you Jeff 😀

    that was pretty much the solution I had devised already but it felt rather hard-coded and not very resilient to change so I thought there may have been a better solution out there somewhere 🙂

    Thank you again for the help. I did manage to get this solved!

    Link to my blog http://notyelf.com/

  • May 23, 2014 at 5:51 pm

    #1716288

    shannonjk (5/23/2014)

    Thank you Jeff 😀

    that was pretty much the solution I had devised already but it felt rather hard-coded and not very resilient to change so I thought there may have been a better solution out there somewhere 🙂

    Thank you again for the help. I did manage to get this solved!

    That's available... it' just going to take some dynamic SQL and, possibly, a control table if you have each level in separate tables.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply