Using GlobalID for a Relationship

5167
13
01-29-2012 04:13 AM
nataliejones1
New Contributor
I have a feature class and a documents table to store the related documents.  I want to create a relationship between them and was thinking of using the GlobalID. With a lookup table holding the globalIDs of both.  Is it ok to use the GlobalID for this purpose or is it like the ObjectID and liable to change?

Any advice appreciated.
0 Kudos
13 Replies
VinceAngelo
Esri Esteemed Contributor
The main problem with using UUIDs is that they're randomly allocated.  This means that when you drive a
join through them, the index will be an inefficient lookup value.  It's not a large deal with small tables but
with a very large table it can have a significant cost.

You also need to be careful when copying tables with UUID values, because many of the copy methodologies
are likely to assume you want new values in the target table.

You'd probably be better off using an integer sequence of your own allocation or a base-36 string sequence
(AAAA-9999 stores 1679616 discrete values in 4 human-readable bytes).

- V
0 Kudos
JacquelinePursell
Occasional Contributor

I am running into a design issue with a database we will be using for offline editing.  Contractors want to use a GlobalID and I have put my foot down on not using it.  

However, after in-depth discussions with my database guys, to create an auto-generated and sequential sequence to use as an ID, it could take forever with all the loops on all the related tables to insert it in and a beast to write.  Plus with it being offline, we aren't sure if the stored procedures gets copied over as well and then with 2 or more people collecting data, we will have ID conflicts.  If we would always be online editing, I would have no issues with triggers and stored procedures but offline ESRI tends to take the reins and I'm not sure how it will handle it.

I would love to never use the GlobalID as the relationship key but how can I achieve the custom ID offline without conflicts?  

0 Kudos
MitchHolley1
MVP Regular Contributor

I know using ArcCollector for offline editing, relationships must be based on a GlobalID field. 

0 Kudos
JacquelinePursell
Occasional Contributor

Very interesting...  That makes sense that it should be a GlobalID but it's just like an OID in that you can't overwrite it or preserve it if you needed to swap out the feature itself.

0 Kudos
VinceAngelo
Esri Esteemed Contributor

UUIDs have their place; I just try to make sure that I use them appropriately.

The easiest way to configure non-UUID offline IDs is to assign a core element or element set to a device "AAA-CZZ" for device1, "DAA-FZZ" for device2.  Leaving room for expansion is key, but depending on what is being collected, you may want to make the allocation root less obvious -- If you have a reliable time subsystem on the device, then the algorithm could be quite sophisticated.

- V

0 Kudos
BlakeTerhune
MVP Regular Contributor

You'd probably be better off using an integer sequence of your own allocation or a base-36 string sequence
(AAAA-9999 stores 1679616 discrete values in 4 human-readable bytes).

Hopefully I'm not derailing this thread too much, but do you have an example of how you would configure the trigger to insert the sequence value into the (versioned) table? We are doing something similar and have issues with sequence values occasionally getting skipped or not changed. We're on 10.2.2 in Oracle 11g.

0 Kudos
JacquelinePursell
Occasional Contributor

I want an example too.  I'm giving up and using the GlobalID's because it's too easy but if I had something to go on maybe I could accomplish this.

0 Kudos
JacquelinePursell
Occasional Contributor

My database guy got me access to a sandbox I can play in.  I created a feature class and added a globalID on it and then I ran a very simple update statement to the globalID in SQL Server Management Studio.  Voila!  As long as the GlobalID isn't NULL you can change it to any ID you want!  Even the one you used to have in the database like the old feature!  I swapped these all around a few times with no issue!

UPDATE Parent
SET GLOBALID='DE00A00E-A060-0AC4-B555-2DE5B6AF04D4'
WHERE OBJECTID=2;

So even though the front end of ArcGIS is extremely prohibitive and makes the GUID/UUID looks scary, you can easily manipulate this value. 

0 Kudos
VinceAngelo
Esri Esteemed Contributor
 As long as the GlobalID isn't NULL you can change it to any ID you want!

This is not completely true, since it can't be NULL and it must be UNIQUE.

The main benefit of the UUID type is the reliable uniqueness aspect, but that reliability

is compromised by assigning values outside the algorithm.  So, while you can set

it to any value, doing so voids the warranty, and makes your organization responsible

for allocation of IDs from that date forward.

- V