idea Allow creation of relationship classes with the same source/target table in Data Management Ideas

Allow creation of relationship classes with the same source/target table

AJR — Fri, 26 Aug 2022 20:27:09 GMT

I'd like to be able to represent the equivilant of a self-join as a relationship class, but since the source and target table are the same this isn't possible currently. A classic example would be a table of empoloyees where there is an employee id and a supervisor id column. The relationship would be from the supervisor id column back to the employee id column so that the supervisor for the empolyee could be easily accessed via the relationship.

Re: Allow creation of relationship classes with the same source/target table

KevinMorris — Wed, 05 Nov 2014 16:56:24 GMT

I've promoted this request for support for a completely different process, but most likely related to the underpinnings of the software.

Recently, I have been tasked to pull data from a SDE tables that requires complex SQL queries. While these queries can be done in SQL Server 2012 (or coded in python), the data is external to ArcGIS using SQL Server. What I have done in this case is to apply a definition query onto the SDE feature class definition in ArcMap Properties for the feature class utilizing a where clause "where ObjectID in (Custom Query In SQL Server that pulls the ObjectId's out; this custom query may contain more than one table/view)". The underpinnins of this definitition query is most likely a form of self join and while I can utilize this mechanism to get the data to export to a feature class, oftentimes many tools, and ArcMap standard joins fail to work. In this manner, providing more robust joins and queries may be helpful.

The current application of this approach is for Hazard Trees where a spatial table (hazard trees) contains the tree location (and several other revelant mapping attributes) and this table is joined to two additional non-spatial tables, surveys and actions. Report include tree surveys for which there is not a recent action (e.g. most recent action date is < most recent survey date for tree), most recent tree actions for the year(annual and fiscal), etc.

Re: Allow creation of relationship classes with the same source/target table

BranislavBlagojevic — Wed, 07 Feb 2018 16:57:22 GMT

ESRI promised this (involution) before 4 or more years ago but has not implemented it until today.

Re: Allow creation of relationship classes with the same source/target table

Larsde_Vries1 — Wed, 29 Aug 2018 09:36:03 GMT

The use case I would like to apply this contains a decomposition, described as parents and childs within one table containing a multilevel hierachy.

Like e.g.

01 - building

01.1 - floor 1

01.1.1 - room 1-01

01.1.2 - room 1-02

01.2 - floor 2

01.2.1 - room 2-01

01.2.2 - room 2-02

Table would look like:

Parent	Child
01	01.1
01	01.2
01.1	01.1.1
01.1	01.1.2
01.2	01.2.1
01.2	01.2.2

Create relationship class with the same element (table or feature)

SpreaficoAlessandra — Wed, 24 Aug 2022 14:13:47 GMT

Improve the 'Create Relationship Class' tool letting the possibility to relate a table or feature to itself, so records of a table (or feature) can be associated with others of the same table (or feature) with a many-to-many relationship class.

Till now, it's not possible, the origin and destination table have to be two different elements.

What do you think?

Re: Allow creation of relationship classes with the same source/target table

SSWoodward — Wed, 24 Aug 2022 16:55:01 GMT

@SpreaficoAlessandra , Thank you for the idea! We've merged it with an existing idea in our Data Management Ideas board. Kindly add your kudos here to give the idea a boost!

Re: Allow creation of relationship classes with the same source/target table

TanuHoque — Thu, 25 Aug 2022 06:10:21 GMT

as a workaround, you should be able to use QueryLayer to achieve this

Re: Allow creation of relationship classes with the same source/target table - Status changed to: Under Consideration

SSWoodward — Fri, 02 Feb 2024 18:30:17 GMT

Thanks for everyone's ideas and participation in this thread! The geodatabase team is taking a closer look at this idea to see how feasible it is. We will update the status of this idea if any developments occur.

Re: Allow creation of relationship classes with the same source/target table

AJR — Thu, 08 Feb 2024 16:47:56 GMT

I'm glad to see that this idea is now under consideration. Slightly less glad that it is 10 YEARS after I posted it, but better late than never I suppose.