Editing a table linked to a feature class in different databases

[HaniDraidi]

I have the following scenario:

I have an enterprise database NOT Geodatabase (Oracle Database) that contains hundreds of tables that I can connect to via ArcMap and explore its tables hosted on a standalone server.

I also have another enterprise Geodatabase hosted on another separate server that contains layers that have common fields with the tables in the first enterprise database.

I can link the data between the two databases by joining/relating the tables from the two databases using key fields that the two databases share.

After linking the two databases, I want to be able to edit the enterprise database’s tabular data linked to the feature classes attribute tables in the Geodatabase. Usually, when I have a feature class and a stand alone table (which share a key field that contains unique identifiers for each feature/record) within the same Geodatabase, I create a relationship class that defines the relation between them (using composite relationship, and forward messaging), and then I can edit the table from the feature class. However, in this case the table lives in a separate enterprise database, and the feature class lives in another enterprise Geodatabase, and so the relationship class cannot see the table and the feature class simultaneously. My question is: How can I edit the tabular data from the feature class.

The example below can better elaborate my case:

1-      I have a feature class for buildings in an enterprise Geodatabase (SQL Server Geo-Enabled database) that contains a field “Building_Code” that identify each building

2-      I also have a stand alone table for the same buildings in an Oracle enterprise database (Not Geo-enabled), which also contain the “Building_Code” field

3-      I want to establish a relationship (as I used to do with relationship classes) between the feature class (as origin) and the stand alone table (as a destination) based on the “Building_Code” field.

Is this possible? If yes, please advise how can I do this.

Any help is appreciated

Hani

[WesMiller]

If this is a one to one relationship you could join the tables and edit the source table(the table being joined to). if this is other than one to one you could link the data and use selection set to edit data.

[HaniDraidi]

Many thanks Wes for the prompt input

Its a One-Many relationship,

May you please elaborate more about how can I do this (with the 1-Many relationship)?

Best,

[RichardFairhurst · Accepted solution]

The only kind of relationship available is map based joins/relates, which are set up for a specific layer in a specific map.  There is no messaging option that will trigger automated updates between the two datasets and with this set up editing is limited to either the layers alone or the tables alone, since an edit session can only be open on one dataset at a time.

The selection sets mentioned by Wes are created by first creating a map based relate (right click the layer or table in the table of contents and choose to create a relate), then selecting a feature or record and executing the relate through the table view.  This will transfer the selection from one source to the matching features/records in the other source.

The biggest challenge is having to start and stop edit sessions to coordinate the edits or keep track of a set of edits in one data source and remembering to do the updates to the other data source, since you cannot simultaneously update them.  If the feature classes or tables are not versioned you can edit them with the field calculator outside of an edit session, but the process of selecting records and doing the related calculations is all manual.

The bottom line is that with the two sources in two different datasets, there is no way to get the same full functionality available as you have with two sources in the same dataset that have a relationship class created for them,

[HaniDraidi]

Thanks a lot Richard for the helpful and valuable contribution,

Based on what you've mentioned above, I can say that there is NO way to submit edits from a table/feature class in a certain (Geo)Database to another table/feature class in another (Geo)Database.

I can conclude that the only way to do such scenario is to geo-enable the Oracle database so it becomes an enterprise geodatabase where feature classes can live, and then I can create a relationship class that links them and propagates edits between them. Or alternatively, creating an SQL Server enterprise geodatabas, and importing all the oracle data into it.

Is this correct? What do you think?

Best,

Hani

[RichardFairhurst]

The two options you mention that would consolidate your GIS feature classes and tables within a single SDE Geodatabase are the only alternatives that would let you edit all of the data in a single edit session and create relationship classes.  Determining which option is best depends on your organization's preferred database platform and application requirements.

[HaniDraidi]

Thank you very much Richard!