Alternative to ObjectID as Primary Key for Related Tables

[ColeS]

I???ve seen differing opinions about using the ObjectID as the primary key in a relationship class, but the majority opinion seems to be that it is not a good idea because the ObjectID can change in cases where you export data.  However, I haven???t found a good alternative way to have a primary key that is 1) unique and 2) automatically generated upon the addition of a new feature.

How should this work in an enterprise geodatabase with multiple versions, feature service web editing, and disconnected editing? 

We have several web and tablet applications that use the API for flex.  Currently, if a user wants to edit a feature that already has a primary id that has been previously assigned, the user can easily edit the related tables.  But, if the user adds a new feature, they need to come up with and enter the primary key on the spot so the related tables have something to relate to.  You could see how this could be a problem, the user cannot be expected to come up with this ID.  

An additional issue is that the data gets checked out in the morning and then returned in the evening, so if there are multiple users making additions to the database at the same time how could we prevent duplicate IDs?  This makes me think that there needs to be a geographic component to the ID or something to make it unique.

Does anyone else run into this problem- if so how do you deal with it?  Perhaps I am not looking at this in the right light, please enlighten me!   I am not an advanced programmer, but if you have a programming solution to this I would love to hear about it so I can read more into it.

Possible solutions in:
-Database triggers?
-ArcObjects scripts? 

Thanks.

[AsrujitSengupta]

Cole,

I don't know if this can be a valid answer to your questions, but you can simply "Copy/Paste" or "Export/Import from XML Workspace Document" the data to maintain the OBJECTIDs if desired... Its only the Import/Export process which changes the OBJECTIDs....

Just might help!

Regards,

[MarcoBoeringa · Accepted solution]

This issue has been resolved long ago by adding the option to create Global ID and GUID fields. These are used in Replication as well! As I understand it, the Global ID field is automatically maintained (as you want), while the GUID field must be maintained by you (or automatically at the database level through some trigger). See the topic "Global Identifiers" in this Help page:

ArcGIS field data types

As that Help page says:

"Developers can use them in relationships or in any application requiring globally unique identifiers. In a relationship, if a Global ID field is the origin key, a GUID field must be the destination key."

Just mind the caveat I set in bold: the primary key must be of field type Global ID, thus automatically maintained, and the foreign key a GUID field, that you probably set to define the relationship.

[VinceAngelo]

While SE_UUID_TYPE columns are a way to handle distributed unique IDs, they come
with a very real cost -- The randomness across 128  bits that preserves uniqueness
causes severe fragmentation, which negatively impacts join speed.

Since the primary purpose of this key is to serve as a lookup, I strongly encourage
you to review your methodology, to see if you can assign a sequence-based key
for joins after the edited data has migrated back into the primary tables (using the
slow UUID values for update ONCE, rather than stacking the deck against join
performance ALWAYS).

- V

[MarcoBoeringa]

Since the primary purpose of this key is to serve as a lookup, I strongly encourage
you to review your methodology, to see if you can assign a sequence-based key
for joins after the edited data has migrated back into the primary tables (using the
slow UUID values for update ONCE, rather than stacking the deck against join
performance ALWAYS).

- V

So essentially you are suggesting to Cole:

- To use GlobalID and GUID columns during the check-out "low-volume" field editing to establish and allow setting of relationships.

- Convert these relationships to a less costly type of relationship ("sequence based key") upon importing into the main enterprise geodatabase using some other - maybe using a 32 bit integer? - type of field, to store the same relationship, possibly automated using database triggers & stored procedures?

- Web applications, if not used off-line, should use the less costly sequence based key, while the off-line tablet applications should use the GlobalID & GUID...

[VinceAngelo]

Yes.

Note that the poor join performance on UUID will not be obvious with low feature counts,
but as features are added, it will likely slow joins noticeably, until a point (100-200k features)
when suddenly the join will be unbearably slow.  If you plan to never exceed 10k features,
then the extra effort may not be worth it.

- V

[ColeS]

Thank you everyone for the suggestions.  I had always thought GlobalIDs were primarily for replication and were "off the table" for use as keys (perhaps that isn't the case?), but the suggestions pointed me in the direction of using a GUID.  I delved deeper into the Flex code and found the UIDUtil.createUID() to create a unique ID that the application will update during disconnected editing. 

Thanks for your help!

[ThomasColson]

Using sequential GUID's "mostly" eliminates your fragmentation problem, but there still is a performance hit just due to the size of the GUID column.