Join versus Relate

Question

Good evening.  I am trying to decide between using a join versus a relate in ArcMap.  I understand that a join supports a one-to-one relationship, but it also says it supports a many-to-one relationship, which may be what I need.  Keep reading to help me decide.  I also understand that a relate supports/implies a one-to-many relationship.  That's the theory.  Can you help me apply it?  Here's my situation...

I have a shapefile that I imported into a SQL Server database as a feature class that contains spatial data.

I have another table in the same SQL Server database, which is not a geodatabase, that contains non-spatial data for the locations in the feature class.  This table of non-spatial data has many rows for the same location.  Additionally, it will be updated frequently/outside of my knowledge by some automated process.  Currently, I am joining the non-spatial data to the spatial data in ArcMap.  I realize that I will only get the first match between the two tables, but my front-end, which uses the ArcGIS JS API, sends a rather restrictive query to the map service created from the document in ArcMap, which should only yield one row for each location.  This implementation is working fine for me, but I am just wondering about best practices.  Should i really be using a relate or should I just reverse the order of the join -- join the spatial data to the non-spatial data?  And, in so doing, will I see any improvements in the performance of my map service?  It is not horrible, but I am just wondering, generally/best practices-wise speaking.

Any thoughts/suggestions/help would be appreciated.

Thanks...Chris

ChristopherJohnson1 · Answer

More thoughts on your suggestion, Michael.  Using the query table, since the data in my non-spatial table could change at anytime, that would mean that I would have to automate the building of the query table.

Thanks...Chris

ChristopherJohnson1 · Answer

Thanks for the reply, Michael.

Yes, I am wondering how different my workflow, in my front-end code, would be...testing it out now.

Thanks...Chris

MichaelKowalczyk · Answer

You might try a Query Table.  I have been recently using them in lieu of one-to-many joins.

You add both tables, and then you input a SQL expression that contains the relationship.  It will then "duplicate" records in the spatial table to match the entries in the non-spatial table, and visa versa.

A relate would be significantly different than your current set up.  You would select the record in your spatial table, and the matching records would be selected in the non-spatial table, but they would still appear as two separate tables, unlike a join.  This may impact your workflows.  A query table, however, would function much like the original table, (although it would not support editing).  The query table would even show the features if you include the SHAPE@ field!