Inspections Data Management - looking for suggestions

[Tiff]

Hello all! I'm hoping to gather input and suggestions about best practices and data management when it comes to mapping inspections - such as outfall inspections and evaluations, stormwater inspections, etc.

Here are two options in mind, with examples:

1. A feature layer for storm drains/outfalls/etc. AND an unrelated feature layer to map outfall inspections (e.g., one point for each inspection) with a common ID.

2. A feature layer for storm drains/outfalls/etc. AND a related table containing all outfall inspections.

I know best practices for data management will often point towards #2; however, there are such limitations to visualizing data and trends with related tables using ArcGIS Online as well as other potential data collection issues (bugs associated with AGOL arcade calculations with related tables), I'm wondering if anyone has more to share regarding either option, what their organization does, and any advantages/pitfalls that they've experienced.

Thank you!

[MobiusSnake]

I've used both approaches.  Since the second approach is probably more common, I can tell you about my experience with the first, particularly why we went with it.

On a job a few years back, the group I was working with had an asset type that would require various types of inspections and jobs in the future, but the specific types of inspections and jobs weren't fully defined, with a high likelihood of new job types being added in the future.  To keep it flexible (and avoid having to add new related tables to an existing service, since that's a pain), we went with an "asset service" that had one feature layer, along with one service with a table for each type of inspection/job that we needed.

The asset layer had Global IDs enabled, while each inspection and job table had a GUID field named "parent_id", a lot like how you'd set up the child dataset in a relationship class.  We used Field Maps to access assets, with a pop-up that had links to the various types of S123 inspections and jobs.  The Global ID was passed in as a parameter to a hidden field in the S123 forms, and saved in the parent_id field.  We also passed a human-readable ID through, to make queries against the "related" tables easier.

Visualization was primarily through dashboards, and it was easy enough to link the assets with the various types of inspections and jobs using the Global ID - GUID link.

One theoretical downside would be the loss of cascading deletes that relationships provide, but there was never a case where we wanted to delete our assets, so that had zero impact.  It could also be a bit of a hassle if you were working with the data in Pro, it's nice having those relationships already in place to traverse from parent to child in the Attributes panel (but a relate within a map would do the same thing).

Since the inspections/jobs were linked to the asset, there was no reason for us to make the "related" tables spatial, non-spatial tables were fine.  If you want to map them independent from the assets themselves, there might be an argument to copy the original geometries, or capture a new one.  (Especially if your assets are lines or polygons, you could capture the specific inspection point along the line or within the site.)

[Tiff]

Hi @MobiusSnake, thank you for your detailed response! I appreciate your input. So if I'm understanding correctly, the biggest reason for your separate/unrelated tables (but keeping the GlobalID - GUID link) is because there would be variations in how many more inspection tables you would need to add to the overarching layer/data schema (requiring routine republishing, etc.) 

The same would actually apply for our situation, but to a lesser extent - there may be two related tables (possibly not more) but they won't be completed at the same time. 

Were there any other reasons you opted to go this route? I actually feel that with this approach, you may still met with some of the limitations I've run into, such as building an ArcGIS dashboard and not being able to filter on attributes unless it's the GlobalID/GUID attribute; for example, if you were to filter by "park name" for the parent layer, the filter wouldn't work for the standalone table unless the table itself also had the "park name" attribute. Did you run into this consideration, and/or bypass some of these issues by hardcoding the attributes needed for filtering in the tables? I do also feel that non-spatial tables likely have their limitations in ArcGIS platforms too - though I can't speak to any specifics off the top of my head.

Similarly, pop-up functionality - did you find creative ways using Arcade to display all the related records? That would also be another workaround needed for something that related record functionality would support out-of-the-box (despite its limitations).

And another broader question about the data collection - so you were able to link Field Maps to various Survey123s, and so each Survey123 itself pertained ONLY to the individual table, correct? The only reference to the feature layer was its GlobalID value?

Thank you again so much!