HierarchyID Across Multiple Tables RRS feed

  • Question

  • Hi, I am looking for some advice on using HierarchyID across multiple tables.

    The scenario is; I have three tables, one with equipment numbers (multiple different levels), one with maintenance to be done to the equipment and one with spare part information for the equipment. I want to represent this information in a single hierarchy tree. Previously I have used a parent child relationship across all three tables and using a union query, recursively added them to the tree.

    Can the HierarchyID be used across multiple tables?

    I have thought of using a forth table containing the HierarchyID, the foreign keys from each table in separate columns and a field to identify which table the foreign key belongs to.

    What is the best method to use?



    Friday, July 9, 2010 2:54 PM


  • You can use a fourth table as you mentioned, which would make it a dimensional model (star schema).

    Your hierarchyID would become the surrogate key for the unique combination of the 3 keys from other tables.

    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, July 13, 2010 9:55 AM