Discussions

Expand all | Collapse all

Recommendation on best way to create hierarchy between client contacts

  • 1.  Recommendation on best way to create hierarchy between client contacts

    Posted 30 days ago
    I had a user request a good feature for my app.  They want to be able to pull a report on a contact showing all of the projects they are associated with.  This way if they are meeting with a client they can show them an activity sheet of all active projects.

    However, they also want this to be work up the hierarchy.  For example lets say we have a team of (3) people.  Mark is in charge of the entire east coast and Jeff, Ashley, and Jim all report to Mark.  

    If I pull a report on Jeff's projects I only want to see his.  If I pull a report on Mark I want to see any direct projects Mark is working on in addition to Jeff, Ashley, and Jim since they all report to Mark.

    So I am thinking I need to take my contacts table and make a relationship to itself with a field.  In this case on Mark as the contact I could add his direct reports in the one to many relationship and pull the data.  Or should I be creating a field somehow on the contact to identify the supervisor so on Jeff's contact page I would select Mark as his boss.

    Once I get the relationship right I am assuming it is a straight forward report but wasnt 100% sure I am going about it the right way.

    ------------------------------
    Ivan Weiss
    ------------------------------


  • 2.  RE: Recommendation on best way to create hierarchy between client contacts

    Posted 28 days ago
    Edited by Evan Westbrook 28 days ago
    Ivan,

    If I understand correctly, it sounds like you have a couple of many to many table situations.

    1. Many Contacts can have many Supervisors, and many Supervisors can have many Direct Reports
      1. Mark could report to both Larry, VP of Sales, and Cindy, VP of Operations
    2. Many Direct Reports can have many Projects, and many Projects can have many Direct Reports working on them
      1. Trucking Accounts can be worked on by Mark and Ashley, and Ashley can be working on Trucking Accounts and Baseball Accounts

    With many to many relationships, a "join" table might be more appropriate than relating a table to itself. For example, maybe you would have a "Supervisor Direct Reports" table to track hierarchies, and a "Employee Projects" table to track which employees are on which projects.

    ------------------------------
    Evan Westbrook
    PRIME Developer
    Harder Mechanical Contractors Inc.
    Portland OR
    ------------------------------



  • 3.  RE: Recommendation on best way to create hierarchy between client contacts

    Posted 28 days ago
    Evan, thanks for the response!  not quite right though.  I am not referring to an internal hierarchy.  I am referring to a client hierarchy.  For example our largest client has the following Hierarchy:

    • President of the Division
    • Multiple Regional Vice Presidents reporting to the President
    • Multiple District Managers reporting to each Regional Vice President
    • Multiple General Managers reporting to each District Manager


    So in essence a pyramid type of setup with (4) layers.  If I pick any contact towards the top of the list I should be able to see all of their opportunities underneath them.

    So if I view the President, I see the entire pyramid.  If I pick a RVP I get the DM and GM opportunities reporting up to that RVP, etc.

    ------------------------------
    Ivan Weiss
    ------------------------------



  • 4.  RE: Recommendation on best way to create hierarchy between client contacts

    Posted 26 days ago
    Ivan,

    Thanks for the clarification. I'm going to assume your client list table is called "Clients."

    I think an additional table to track hierarchies is still the direction you want to go (maybe a separate table for each hierarchy step if it's going to remain fairly static.) If you relate Clients to Clients, you'll have to add a new table relationship for each additional field (not fun.) Having Parent records automatically know their Children or Grandchildren could get tricky, but once you figure that out, it will be simpler to manage.

    From there, you can set up report links to display the information your form. Lookup fields counting the total # of Child records for each hierarchy step can then feed form rules to display different aspects of the Client form.


    ------------------------------
    Evan Westbrook
    PRIME Developer
    Harder Mechanical Contractors Inc.
    Portland OR
    ------------------------------



  • 5.  RE: Recommendation on best way to create hierarchy between client contacts

    Posted 24 days ago
    Evan, so I did not quite follow you.  The table is called Contacts.

    I was assuming I could self relate it and just do it that way.
    But you are saying do a separate table and name it lets say Contact Hierarchy.  I am thinking it should relate to the Contacts table twice
    The first one being the Employee and the second one being the Manager?  Is that correct?  And use that to report out the work since it is linking people?  I guess what I was somewhat confused by is each Manager can have several Employees reporting to them.

    ------------------------------
    Ivan Weiss
    ------------------------------



  • 6.  RE: Recommendation on best way to create hierarchy between client contacts

    Posted 24 days ago
    Edited by Evan Westbrook 24 days ago
    Ivan,

    I think we're on the same page with how Contact Hierarchy would work. I did want to touch on some items behind the recommendation for a second table, as I think this one could go either way.

    Item 1: Data Structure Best Practices
    A good goal for many databases is 3rd Normal Form. Essentially, this just means that the tables in a database are set up such that a piece of data only exists in one place.

    I should note that this is a "Recommended Best" practice and not "The Only" practice. Design goals should be balanced with business needs for the database. Examples where a database might not be fully "normalized" are typically related to performance (reporting databases, data warehouses, query response times, end user workflows, etc.) 

    QuickBase University has some great notes on this if you'd like more information.

    Item 2: This use case could go either way
    A second table vs. relating a table to itself is sometimes a "to-may-to" "to-mah-to" situation. In the below image, Options 1 and 2 can more or less do the same thing.

    We even have some self-related tables like Option 2 in some of our production apps.

    Item 3:  Option 1 vs. Option 2
    When designing databases and writing code, efficiency, performance, and simplicity are important. Readability, maintainability, and debugability are also important.  In my experience, Option 2 relationships in our apps have been difficult to debug and difficult to explain when on boarding new team members. It's less clear what the table relationship does at a quick glace.

    Option 1 is also closer to 3rd Normal Form, and it gives us a lot of potential benefits: The hierarchy relationships between Contacts exist in a single location. It's more clear to any app users how Contacts are related to each other. It sets the foundation for future business needs (i.e. allowing other tables to use this contact hierarchy.) Updating future Contact Hierarchies (i.e. Bob gets promoted from DM to RVP) is easier.

    Here's an example of how you might set it up:


    ------------------------------
    Evan Westbrook
    PRIME Developer
    Harder Mechanical Contractors Inc.
    Portland OR
    ------------------------------



  • 7.  RE: Recommendation on best way to create hierarchy between client contacts

    Posted 18 days ago
    Evan, thanks for the SUPER DETAILED response again!  So here is my challenge....  I have several different clients I need to maintain the relationship for.  Not all of them have the same structure level.

    So my concern with the separate table is one of my client uses RVP and DM as the levels.  Another one of my clients use SVP, Vice President of Operations, and DM as the three levels (they have an extra one).

    So in a separate table approach could it intelligently understand this or does that push me to the self related table approach?

    If I go with the self table relationship....  I think I would relate "down" stream correct?  So on the contact I would have a one to many relationship to contacts and that relationship would be direct reports and that would be the level down (DM's in the first example above)

    ------------------------------
    Ivan Weiss
    ------------------------------



  • 8.  RE: Recommendation on best way to create hierarchy between client contacts

    Posted 17 days ago
    Edited by Evan Westbrook 17 days ago
    Ivan,

    I'm glad you appreciated the details. I get a little excited about data stuff sometimes.

    To answer your question: yes, the separate table approach would handle varying structure levels for different companies.

    If we think about this on a really high abstraction level, this use case is just describing how entities are related to other entities (sounds like a database!) With corporate management structures, we're tracking how employees are related to other employees. Specifically, who reports to whom. Since this doesn't require an employee's title (at least in the eyes of the database,) we can handle the corporate structures of different organizations in the same table. Said another way, the corporate hierarchy of Company A and B are implicit in the "Contact Hierarchy" table.

    Here's a quick example:
    • Company A:
      • Structure:
        • RVPs report to VPs
      • Contacts and Title:
        • Diane (VP)
        • Todd (RVP)
    • Company B
      • Structure:
        • DMs report to VPs
      • Contacts and Title:
        • Caroline (VP)
        • Bo (DM)
    In the "Contacts Hierarchy" table, we would enter the following (and maybe even pull [Title] and [Company] over as a Lookup Fields for reporting purposes.)
    • Record 1
      • Supervisor: Diane
      • Direct Report: Todd
    • Record 2
      • Supervisor: Caroline
      • Direct Report: Bo
    Without explicitly defining it for Company A or B in the database, we still know the corporate management hierarchies of each company. If Todd reports to Diane, Todd is a RVP,  Diane is a VP, and both of them work for Company A, we can assume that the corporate management structure at Company A is "RVP reports to VP."  This is also handy if you encounter a company with different management structures by department. For example, your Sales department at Company A might be Associate to DM to RVP to VP, but the Finance department might be Analyst to VP.

    ------------------------------
    Evan Westbrook
    PRIME Developer
    Harder Mechanical Contractors Inc.
    Portland OR
    ------------------------------