Okay to use OBJECTID field for relating tables? Or use GUID?

3550
4
10-06-2011 03:13 PM
Highlighted
New Contributor II
Hello,

I am trying to make a geodatabase that I can also edit within Access.  Typically when I've built databases in excel I've used autonumber field to create a uniqueID for each row and then used unique ids for relating tables together. I am now trying to build this is a database so I can view the data in ArcGIS but do most of the editing in Microsoft Access.  When I bring the tables into the geodatabes the unique id (which is an autonumber in Access) automatically becomes the ObjectID in ArcGIS.  This is fine except I'm wondering if it is okay to be using the ObjectID field to be relating relational tables or if I should be using a seperate GUID field instead and if so how do I set up a GUID so it automatically generates a unique id in ArcGIS and/or Microsoft Access. I've never used GUIDs before so I'm not sure how to best approach using them for this problem (if in fact they do work for this specific task)

The attached pic shows how my tables are set up in Microsoft Access using unqiue ids with autonumber format in Microsoft Access.

Thanks in advance.
4 Replies
Highlighted
Esri Esteemed Contributor
Use an integer column, but not the objectid.  Don't use a GUID unless you *really* need
certified uniqueness, because the keys are scrambled in a way that provides poor performance
for parallel join operations.

- V
0 Kudos
Highlighted
New Contributor II
Thanks for the advice.  I'll use an integer field instead of OBJECTID or GUID. Just one further question...is there a way or code that I can use to autonumber the integer field? In Access it only lets you have one autonumber per table so I'm not sure how best to ensure I have a unique id in the integer field I create, without the data entry person having to manually enter one.

Thanks.

Karl
0 Kudos
Highlighted
MVP Honored Contributor

Use Attribute Assistant to do the auto numbering.  It comes as part of the Local Government Information Model (LGIM) templates like the Address Data Management template (you don't need to the the LGIM).  To create an auto increment number use the Attribute Assistant toolbar, the GenerateID table and DynamicValue table.  Press the download button to get to the link to download it.

After extracting the files double click the esriAddin files for Attribute Assistant and Address Management to install them while ArcMap is closed.  In the GenerateID table you add a row with the feature class name, the current number of the sequence in your table.  Use 0 if you are starting with nothing numbered, but this only works for blank feature classes/tables.  Use the last existing number of an existing sequenced field if it already has records (see the autoincrement calculation to initialize an existing long field).

OBJECTID *Sequence NameSequence CounterInterval ValueComments
20WHYDRANT444481<Null>

Then in the DynamicValue table you enter the feature class/table name holding the feature class name (WHYDRANT), the field to be incremented (FACILITYID), the Value Method (GENERATE_ID), a Value string (WHYDRANT|0|[seq] - meaning increment a normal number with no leading zeros stored in the WHYDRANT feature class), and make On Create True and all other options false.

OBJECTID *Table NameField NameValue MethodValue InfoOn CreateOn Change (Attribute)On Change (Geometry)Manual OnlyRule WeightComments
147wHydrantFACILITYIDGENERATE_IDWHYDRANT|0|[seq]TrueFalseFalseFalse<Null><Null>

Add both of these tables to your map from the directory you extracted it to in the \AddressDataManagementLG\MapsandGeodatabase\LocalGovernment.gdb.  Also add the feature class (like wHydrant under the WaterDistribution dataset).  Now add the Attribute Assistant toolbar (right click toolbar area and choose Attribute Assistant) and make sure that the first button is active (the red exclamation mark in the icon disappears).  Add a new feature and notice the next number in the series for your field was added.

Highlighted
Esri Esteemed Contributor
Best practice is to leave the OBJECTID alone; if you move up to a real RDBMS and ArcSDE
technology, you'll need to adhere to this guidance more closely.  The same goes with GUIDs --
they only impact performance significantly when you venture into the "large" table range
(hundreds of thousands to tens of millions of rows).

Keep in mind that personal geodatabases are deprecated, and will not be supported by
64-bit ArcGIS 10.1.

- V