locked
HierarchyID Design RRS feed

  • Question

  • Good Night!

    Please forgive my English (I'm Spanish speaker)

    I'm trying to design a data base using hierarchyId datatype. My start design is something like this:

    Database Design

    All those tables upside are hierarchy Id (nodo means node in Spanish) and all primary key in those tables are hierarchyID datatype.

    I began with the table tbm_grupo_empr (That table will contain Corporate Groups) I designed it in hierarchyId because a Corporate Group may contain (Or may be father of others Corporate Groups).

    The other table is tbm_empresa (That table will contain Companies) I designed it in hierarchyId because a Company may contain (Or may be father of others companies). This table references the Corporate Groups because a Corporate Group may contain Companies and any company must belong to a Corporate Group.

    Other table is tbm_sucursal (That table will contain Subsidiaries) I designed it in hierachyID too because a Subsidiary may contain (Or may be father of other Subsidiaries). This table references the Companies table because a Company may contain Subsidiaries and any Subsidiary must belong to a Company. Now this table I designed separately because I didn't figure how to manage that a Subsidiary may have Offices (For example Accounting or Archive, etc. but one office may be keeping the accounts or archive to all Subsidiaries).

    The next table is tbm_area (That table will contain Offices) I designed it in hierarchyID because an Office may contain (Or may be father of other Offices). This table references the Subsidiaries table because a Subsidiary may contain Offices and any Office must belong to a Subsidiary.

    The next table is tbm_cargo (That table will contain Positions) Again I designed it in hierarchyID because a Positions may contain (Or may be father of other Positions). This table references the Offices table because an Office may contain Positions and any Position must belong to an Office.

    The last table is per_trabajador (That table will contain Employees) Obiously this table is hiearchyID because a Employee reports to other employee and may be boss of other employees. Some body may thing that if I have the Position I have the boss for example but if I have a Drivers Office with some Drivers Positions but each Driver reports to a Manager (His boss is the manager asigned to and not the Driver's Manager) One is the functional Boss and other is the direct Boss.

    Now this is my basic Design I think.

    Now I can't imagine how to "draw" in a select sentence the full Organizational Tree... Help me please! I thought something like CTE recursive select but I did'n figure out how can I manage it.

    Other alternative is design other hierarchyID table that contains all the tree with a type column (Type Corporate Group, Type Company, Type Subsidiary, etc.) but each tipe has specific attributesand I don't figure out how to reference those neither, because its atributes are in diferent tables with their own codes (I may have Company code 1 and Office code 1 too).

    I thought in that alternative when I read this article: Hieararchical Data, but in this case one country won't have other countries inside (One Office may have other offices on charge) and In that example I didn't see where store specific chararcterictics of one type for example a country may have a Capital but a neighborhood or a country doesn't.

    Another topic is that I have other tables for permissions. For example a user can't see a specific Office or Company information and his sons or a user can't create Subsidiaries of a specific subsidiary and his sons (full node) or can't create positions in a specific Office but when I exclude a node it exclude the entire node if I use left join and I don't want to use specific permissions for speciffic rows because if I want to revoke a permission on the full node with n children I use a single row with the father and no n rows each one for each child.

    I hope to be enough clear.

    Thanks a lot for your valuable help.


    Jamesit0


    • Edited by Kalman Toth Monday, September 29, 2014 12:33 AM Spelling
    Tuesday, September 16, 2014 3:10 AM

Answers

All replies

  • Now I can't imagine how to "draw" in a select sentence the full Organizational Tree... Help me please! I thought something like CTE recursive select but I did'n figure out how can I manage it.

    Organizational tree example with hierarchyid:

    http://www.sqlusa.com/bestpractices2008/orgchart/



    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, September 16, 2014 7:07 AM
  • Good Afternoon Kalman!

    Thanks a lot for your reply but the link is broken, I can't see the web page.

    Regards,

    James.


    Jamesit0

    Tuesday, September 16, 2014 7:57 PM
  • Good Afternoon Kalman!

    Thanks a lot for your reply but the link is broken, I can't see the web page.

    Regards,

    James.


    Jamesit0

    Worked for me
    Tuesday, September 16, 2014 8:01 PM
  • Good Night Kalman!

    The only way that I could see the web page was using a proxy web.

    Now I reviewed the article and it shows a CTE with a single table "HumanResources.Employee" from "AdventureWorks2008" table and then join it with the "Person.Person" table having the same foreing key for those two tables.

    I have several tables, each one with their own primary key with diferent data type (code = codigo) or diferent value (nodo = node) and each table has different levels. How can I merge all those tables? Now I designed a single table (my second option) like this:


    tbm_organigrama table is a HierarchyID table when I may put all objects by Type "tipo in Spanish" (Corporate Groups, Companies, Subsidiaries, etc. "tbm_tipo_organigrama" the table tbm_tipo_organigrama from the schema idi is just for translations. Now, the column object "objeto in Spanish" will store the object ID for each object according to each table, but the tbm_grupo_empr table (Corporate Groups Unique index is tinyint Type) and the per_trabajador table (Employees table unique index is integer Type) and in the Corporate Groups I may have the Group 0, 1, etc with the node /, /1/, etc. and in the Emplyees table I may have the employee 0, 1, etc. with the node /, /1/, too.

    Which is the best option (Or other option) to draw all objects in the Orgchar in one select if I have different tables and data types all of them hierarchyID designed?

    Thanks a lot for your valuable help.


    Jamesit0


    • Edited by Jamesit0 Wednesday, September 17, 2014 1:50 AM
    Wednesday, September 17, 2014 1:45 AM
    • Marked as answer by Jamesit0 Sunday, September 28, 2014 2:33 PM
    Thursday, September 18, 2014 2:38 PM
  • ¡Good morning!

    Please forgive my delay because I was studying the best way.

    At last I decided to use my second option.

    Regards

    James.


    Jamesit0

    Sunday, September 28, 2014 2:35 PM