Considerations for using attribute rule to generate sequential IDs in existing ASSET_ID field

[Bud]

ArcGIS Pro 3.4.3; Oracle 19c 11.3 EGDB; Traditional versioning moving edits to base.

My organization has recently upgraded from Pro 2.9.5 (had attribute rule limitations) to 3.4.3. And ArcMap has been phased out. So now, we are taking the plunge into calculation attribute rules.

We're planning to use attribute rules to automatically generate IDs in ASSET_ID fields in various existing FCs.

Question:

What do we need to consider when using an attribute rule to generate unique IDs for new rows in an existing FC? We may want to use use the attribute rule to enforce data integrity in general as well.

1. How to start/continue the sequence at the largest existing number in the field?
2. How to pad the numbers with zeros?
3. Should the field allow nulls?
4. Should the field be manually editable? Or should we disallow edits? Consider data dependencies/syncs to external systems like a work order management system.
5. We could check to make sure there is a unique database index that enforces unique IDs. Or alternatively, switch from a database index to an attribute rule for this, since the database index causes issues when splitting linear assets.
6. Consider relationship classes to other FCs or tables. I don't think we have any for our asset FCs, but it's still good to think about this.
7. Should we prevent row deletions, for the purpose of integrations to external systems?

Is there anything else we should consider?

[DavidSolari]

Answering off the top of my head:

1. You can pick the starting number when you create the sequence. If you're using a sequence then assume each sequence pull will generate a new largest number, if you have to check the table every time to find the previous largest number then you'll run into performance issues, and at that point you don't need a sequence to bump a number up by 1 anyways.
2. The Arcade Text function has a second parameter for number formatting that includes 0 padding.
3. Non-null fields are always preferable for record IDs, just make sure it has a sensible default as fields with calculation rules are inserted in their initial state before the rule updates things
4. Disallowing edits through the calculation rule is the safest move, otherwise it's trivial to break the identity. Worst case you can hop into the database and tweak an ID without ArcGIS.
5. You should be able to pop a unique index on the field straight through Pro's Manage dialog. Worst case you add a non-unique index which will still be a big performance boost if you need to query by ID.
6. No issues with relationship classes, I have a 1-m relationship where the parent uses unique IDs and there's been no issues on that front.
7. There's no special consideration with sequences and deleting rows, just keep in mind it'll lead to fragmented IDs over time.

One last tip: try adding a short prefix to the IDs like "DB" or "GIS" or something, that way if you need to work with IDs that come from other sources you don't have to worry about numeric collisions.

[MErikReedAugusta]

David's points largely cover the territory I would've, and his points are all correct.

Still, adding my 2¢ for a few things we've encountered with Asset IDs:

3. NULL-enforceability on IDs

This is something we've slightly struggled with when that ID field drives some external source.  We use Cityworks as a work order management system, and all of our Assets must have a wholly-unique ID in that system.

This potentially poses a problem for Assets that somehow sneak in and haven't been assigned an ID, because whether I use a "true" <Null>, a "fake" null (i.e., an empty string), or a sentinel value (i.e., "Unassigned"), there's an increased chance for duplicate IDs sneaking in.

Obviously, the only solution here is to do whatever you can to make sure you don't allow that system to see any Assets that haven't had IDs assigned, yet.  In our case, that can be done with simple queries on the data that Cityworks is consuming, so it's in practice a non-issue.  But it's always something to keep in mind.

5. In my limited testing*, NextSequenceValue seems to have odd behavior with line-splitting, in some cases.

*For the moment, we're still manually and/or batch-calculating our IDs, which is suboptimal, but we haven't been given the greenlight to deploy ARs in our SDE, yet.  Soon, hopefully.  I have done some preliminary testing in siloed GDBs, though.

See my post on the preliminary testing I did, where NextSequenceValue seems to skip a value when it calculates as a result of a linear feature being split.  Obviously, it's been more than a year since then, so my memory is a bit foggy.  As I recall, though, I could only get it to do two cases: Case 1 (outlined in the post), where ID-1 is retained for the longer sub-segment, and ID-3 is assigned to the shorter segment.  And Case 2, where I let it regenerate both, and I received ID-3 and ID-5 for the two child segments.

I have not yet ruled out user error on that, though, because we had a few emergencies that required me to set aside that testing for a while, and then we learned AR deployment was being pushed back, anyway, so I haven't made it back, yet.

7. Row Deletions

The only issue we have encountered with row deletions comes with our external integrated system(s), like Cityworks.  Because that system tracks work order history as attached to Assets, if you delete the row entirely, the database can't properly track work orders that were attached to the deleted Asset.  We have a status field that we mark as "Deleted/Retired", instead, and retain the row in the database.  It is a mild pain to have to remember to query them out for normal GIS operations, though.

Database Sequence Viewability

As an aside to everything already discussed, NextSequenceValue is rather "black-box", because I've yet to find an easy way to view the current Sequence Value on-demand.  I think there's a Python command that you can use to view it in the Python terminal, but I'm not positive.  Regardless, it's not currently as GUI-user-friendly as, say, Domains & Subtypes.

Similarly, there's no easy way to view all of the currently-created Database Sequences you have in a database, except for that same Python command.