topic Re: Using GlobalID for a Relationship in Data Management Questions

Using GlobalID for a Relationship

nataliejones1 — Sun, 29 Jan 2012 12:13:03 GMT

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.

Re: Using GlobalID for a Relationship

VinceAngelo — Sun, 29 Jan 2012 23:07:48 GMT

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

Re: Using GlobalID for a Relationship

JacquelinePursell — Tue, 20 Sep 2016 17:57:50 GMT

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?

Re: Using GlobalID for a Relationship

MitchHolley1 — Tue, 20 Sep 2016 18:31:53 GMT

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

Re: Using GlobalID for a Relationship

JacquelinePursell — Tue, 20 Sep 2016 22:09:32 GMT

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.

Re: Using GlobalID for a Relationship

VinceAngelo — Wed, 21 Sep 2016 14:39:19 GMT

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

Re: Using GlobalID for a Relationship

JacquelinePursell — Fri, 23 Sep 2016 17:47:14 GMT

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.

Re: Using GlobalID for a Relationship

VinceAngelo — Fri, 23 Sep 2016 17:57:20 GMT

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

Re: Using GlobalID for a Relationship

JacquelinePursell — Fri, 23 Sep 2016 20:59:06 GMT

Yes, I do know that.

This is not for some random day when I want to change the IDs for fun. This is for when I want to do a cold swap of a feature but keep the attribute table and the relationships intact. There are so many times in my career when I have had to replace hundreds or thousands of bad shapes for better shapes and because all the databases I've ever encountered had a straightforward user created ID, I was able to preserve this information and swap them out seamlessly. The usage of the GlobalID for a relationship worried me on being able to do this, just like if I used the ObjectID.

Unfortunately I need to edit offline with this database and there will be 2+ users at a time in the field and some of them contractors. The GlobalID is so easy to use when editing offline because of the random nature and no user prompt but I really did not want to use it. If I didn't use a Global ID then I would have to create a very intense and complicated stored procedure/trigger that would create the sequential ID I want offline. I don't have the skills for that and we don't have enough money for it either to send it out.

I'm not sure what kind of "warranty" you are implying... If the GlobalID was untouched and it made a hideous error and destroyed all the data in my database, do you really think ESRI would guarantee to restore it? no, I don't think so. I'm not altering their stored procedures. Also, in ESRI's own help documents is all kinds of backend manipulation of the GlobalID:

Inserting a value into a global ID or GUID column in SQL Server using SQL—Help | ArcGIS for Desktop

and...

Next_GlobalID—Help | ArcGIS for Desktop

Re: Using GlobalID for a Relationship

VinceAngelo — Fri, 23 Sep 2016 21:46:20 GMT

There are, of course, few warranties in the software world, and collisions in 128-bit space are possible,

but very remote. I'm just pointing out that they're less remote (by many orders of magnitude) if you are

assigning values using two different algorithms (the examples you referenced use the same algorithm

that ArcGIS uses, so they're not likely to conflict).

If this is the only way to meet your requirement, so be it. But I do urge caution (and a UNIQUE index

and robust error handling during assignment)

- V

Re: Using GlobalID for a Relationship

BlakeTerhune — Wed, 28 Sep 2016 16:43:21 GMT

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.

Re: Using GlobalID for a Relationship

JacquelinePursell — Wed, 28 Sep 2016 17:00:58 GMT

If I could create my own unique index that could do multiple offline devices then I would not feel the need to worry about altering a global ID. I do not have the skills and we don't have the money to hire anyone out for it. I would be more than fine using a user created ID too but we are shifting towards using more contractors to collect data and relying on random people to stay unique isn't possible. Using the GlobalID feels like I'm giving up because it's just too easy to use.

At least the scenario I outlined earlier about cold swapping out features is a rare event and it will never be me picking out some random ID, it will always be reusing the old ID to keep the relationships intact. At least the ESRI GlobalID trigger only occurs upon an insert of a new record and I am just updating a record so there is no conflicts there either.

But if you or anyone else has an actual stored procedure built that does what I need, please share it! Even if it was not for SQL Server, it can be easily modified.

Re: Using GlobalID for a Relationship

JacquelinePursell — Wed, 28 Sep 2016 17:07:12 GMT

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.

Re: Using GlobalID for a Relationship

MaximeDemers — Thu, 07 Mar 2019 19:34:34 GMT

I have read all this post. It was very interesting.

To summarize:

GlobalId: Easy to Use but less performant because of inefficient indexing. There is also a limitation when you need to delete and re-insert a feature. The relationships will be lost.
GUID: Same as GlobalId except that you have to create it yourself somehow. However the relationships are not lost when you delete and re-insert a feature.
A sequential Id: Efficient index. You need to manage it yourself. Risk of loosing uniqueness especially when created on client side or offline.

Am I correct? Is there something to correct or to add?

Thank you