sdetable -o create_view vs. query tables for 1:M relationships

Question

ArcSDE 10.0/Oracle Spatial 11g/SDO_GEOMETRY/Arc Desktop 10.1/Windows 7, 64 bit

I have to find the most simple and still functional way to use a number of related tables.  The spatial table has 1:M relationships with many standalone tables (relationship classes have been created).  In addition, the standalone tables also are related (relationship classes have not been created, but can be if needed).  IMPORTANT - These tables will be used by non-GIS users, so

the reason it must be fairly simple to use.  Problems: 1:M relationships "not supported" in joins, but using a relationship class or creating a relate in the .mxd does not give a straight forward way of querying from both tables (can't access related table attributes in 'definition query').  Yes you can select by attributes from 1 table, open related table and "select from current selection".  But when you have to do this to 3 or 4 tables in a row to get 1 query these non-GIS users will get confused.

What is the best way to handle this?  Am I missing something about relates/relationship classes about querying from the related table(s)?

I'm exploring sdetable -o create_view command making the view as a M:1 and including the shape field so there are geometries for all records.  This works well except views are not editable...is this correct?  Is there a way around this?

I'm exploring creating query tables but have some concerns: the query tables are temporary "layers" stored in the GDB correct?  Or are they stored in memory on the creater's local machine?  Basically I'm concerned about permissions to create these temporary query tables in the GDB, especially since it would be easy for someone to accidentally create a multi-million record query table (not setting the unique IDs to match).

Any ideas would be helpful as I've searched the forums and haven't found the answers I'm looking for to handle this.

Thanks in advance,
Wes

WesKing · Answer

Thanks Marco...that's all great information!

Although I am interested in editing capabilities, the majority of users will not be editing.  The editors will be GIS analysts so if there's not an easy way to query multiple tables in the scenario I described, the editors (including myself) will have to use old fashion ingenuity and just tough it out for now 🙂

Thanks again Marco,

Wes

MarcoBoeringa · Answer

I'm exploring sdetable -o create_view command making the view as a M:1 and including the shape field so there are geometries for all records.  This works well except views are not editable...is this correct?  Is there a way around this?

One additional remark concerning the above statement in your first post: Creating a view should allow you to view the fields of both the base table and the related table in one "view/query" in a 1:M relationship. However, in a sense it is logical you can't "edit" such a view directly, as the view is de-normalized, and the records of the base table have been "virtually" duplicated for each related record in the base table.

This older ArcGIS 9.3 Help page also states:

"Spatial views contain a single spatial column. You can create spatial views in the same way you create other views; just be sure to include the geometry column in the view definition. The table containing the spatial column must be the same table that provides the row ID (also called the ObjectID) column for the view. Spatial views cannot be edited through an ArcSDE client."

And this 10.0 Help page talks about multi-versioned views that can be edited, but only in certain specific situations, and not from within ArcGIS:

"Multiversioned views incorporate database views, stored procedures, triggers, and functions to access or edit a specified version of a table or feature class in a geodatabase using SQL."

This requires the Table or Feature Class to be versioned, just like the versioned editing of any enterprise geodatabase. And editing from within some custom webapplication or non-ArcGIS (e.g. AutoCAD) application. As that last Help page also states:

"The primary use of multiversioned views is to edit versioned data using SQL. You cannot modify the underlying table or feature class through a multiversioned view using an ArcGIS client application."

Also, multi-versioned views do not support relationships or joins, they can only be defined for a single Table or Feature Class, see below:

"Multiversioned views only work with an individual versioned table or feature class. You cannot use a where clause to join multiple tables together or restrict which rows or columns are included in a multiversioned view."

WesKing · Answer

Thanks for your reply Marco!

Yes, I was hoping for an answer that wouldn't require an additional purchase/license/etc..  Funds are limited right now (as I'm sure they are for most) so I'm not optimistic that request would go over well.

I was hoping for something that could be accomplished through ArcGIS Desktop or ArcSDE (so anyone with other ideas please chime in).

However, I will do some research and bring it to the people with the wallets.

Thanks again for your reply,
Wes