Multilayer Hierarchy

Question

Multilayer Hierarchy

Paul Giles-Randall

SSC-Addicted

Points: 482
More actions
February 8, 2019 at 11:33 am

#370537

I have a need to store hierarchy data where the nodes are punctuated with fixed data. As an example I have supplied a diagram.
   [CompanyId](Display: CompanyName)
|
        |---- Offices
        | |
        | |----- [SiteId](Display: SiteName)
| |            |
        | |            |---- Contacts
        | | |
        | |            |---- Plans
        | |            |        |
        | | |---- [PlanId](Display: PlanName)
        | | |---- [PlanId](Display: PlanName)
Nodes enclosed in [ ] comes from the database, I have used the primarykey within the [ ], but the display name is listed by the side, so in the case of [CompanyID],this would be used as the node value in the tree, whilst [CompanyName] would be displayed.
As it stands Company Data is stored in the Company table, Site data in the Site table, etc...
This means we cannot use a standard parent child relationship due to the data existing in multiple tables.
Is there an elegant solution to the this problem?
Dictionary ee

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Paul Giles-Randall - Friday, February 8, 2019 11:33 AM
I have a need to store hierarchy data where the nodes are punctuated with fixed data. As an example I have supplied a diagram.
   [CompanyId](Display: CompanyName)
|
        |---- Offices
        | |
        | |----- [SiteId](Display: SiteName)
| |            |
        | |            |---- Contacts
        | | |
        | |            |---- Plans
        | |            |        |
        | | |---- [PlanId](Display: PlanName)
        | | |---- [PlanId](Display: PlanName)
Nodes enclosed in [ ] comes from the database, I have used the primarykey within the [ ], but the display name is listed by the side, so in the case of [CompanyID],this would be used as the node value in the tree, whilst [CompanyName] would be displayed.
As it stands Company Data is stored in the Company table, Site data in the Site table, etc...
This means we cannot use a standard parent child relationship due to the data existing in multiple tables.
Is there an elegant solution to the this problem?
Dictionary ee

You have PlanID's for Plans. Do the rows in the Plan table have a column that contains which ContactID the plan is associated with?

--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)

Paul Giles-Randall SSC-Addicted Points: 482 More actions · Answer 2

I think the drawing lost some depth.
So I have tried to add it again.

In effect this tree is created by combining data from four tables into one. The company, site, contacts and plan tables. Each company can have many sites, with each site contain one or more of plans or contacts.

drew.allen SSC Guru Points: 76944 More actions · Answer 3

Paul Giles-Randall - Friday, February 8, 2019 7:15 PM
I think the drawing lost some depth.
So I have tried to add it again.
[CompanyId](Display: CompanyName)
|
|---- Offices
|           |
|           |----- [SiteId](Display: SiteName)
|           |            |
|           |            |---- Contacts
|           |            |
|           |            |---- Plans
|           |            |          |
|           |                       |---- [PlanId](Display: PlanName)
|           |                       |---- [PlanId](Display: PlanName)
In effect this tree is created by combining data from four tables into one. The company, site, contacts and plan tables. Each company can have many sites, with each site contain one or more of plans or contacts.

By default, HTML collapses adjacent white space. If the white space is significant, you need to tell the engine that your text is pre-formatted. If you look at the IF Codes available in the editor you will see an option for "Plain (non-highlighted code". You should probably be using that.
[CompanyId](Display: CompanyName) | |---- Offices | | | |----- [SiteId](Display: SiteName) | | | | | |---- Contacts | | | | | |---- Plans | | | | | | |---- [PlanId](Display: PlanName) | | |---- [PlanId](Display: PlanName)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 4

Paul Giles-Randall - Friday, February 8, 2019 11:33 AM
Is there an elegant solution to this problem?
Dictionary ee

Why did you fail to post any DDL? I really hate trying to code from diagrams or pictures. In fact, I just don't do it anymore after 30+ years of being on SQL forums. When you say there is a standard way of representing a tree in SQL, what did you mean? Is that the adjacency list model or the nested set model?

At the risk of being accused of trying to pay my mortgage, you might want to get a copy of my book on trees and hierarchies in SQL. The important principle is that the hierarchical structure is a relationship, so it gets its own table. The nodes are entities so they go in one or more other tables. It's the difference between an organizational chart and a company and the personnel that occupies the job positions.

Please post DDL and follow ANSI/ISO standards when asking for help.

sgmunson SSC Guru Points: 110639 More actions · Answer 5

Paul Giles-Randall - Friday, February 8, 2019 11:33 AM
I have a need to store hierarchy data where the nodes are punctuated with fixed data. As an example I have supplied a diagram.
   [CompanyId](Display: CompanyName)
|
        |---- Offices
        | |
        | |----- [SiteId](Display: SiteName)
| |            |
        | |            |---- Contacts
        | | |
        | |            |---- Plans
        | |            |        |
        | | |---- [PlanId](Display: PlanName)
        | | |---- [PlanId](Display: PlanName)
Nodes enclosed in [ ] comes from the database, I have used the primarykey within the [ ], but the display name is listed by the side, so in the case of [CompanyID],this would be used as the node value in the tree, whilst [CompanyName] would be displayed.
As it stands Company Data is stored in the Company table, Site data in the Site table, etc...
This means we cannot use a standard parent child relationship due to the data existing in multiple tables.
Is there an elegant solution to the this problem?
Dictionary ee

I'm not at all sure what problem it is you are looking to solve. Are you designing the tables for this database or are you in need of help formulating the query to bring back information across these tables? I also wonder why you think that a typical parent/child relationship is something that can't be used? If I had designed this database, the CompanyID would be a foreign key reference in the Offices table, and the OfficeID would; similarly; be a foreign key reference in the Sites table, and so on down the line (the SiteID would be a foreign key reference in the Contacts as well as Plans tables, as those appear to be at the same level of the hierarchy. If the problem is that you need a query, standard joins will do, and configured based on what kind and level of data you need to return.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

x SSC-Insane Points: 23660 More actions · Answer 6

Why did you fail to post any DDL?

In the OP's defense it sounds like he's trying to produce some DDL and getting help doing it.