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

[KarlZimmer]

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.

[VinceAngelo]

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

[KarlZimmer]

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

[RichardFairhurst]

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 Name	Sequence Counter	Interval Value	Comments
20	WHYDRANT	44448	1	<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 Name	Field Name	Value Method	Value Info	On Create	On Change (Attribute)	On Change (Geometry)	Manual Only	Rule Weight	Comments
147	wHydrant	FACILITYID	GENERATE_ID	WHYDRANT|0|[seq]	True	False	False	False	<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.

[VinceAngelo]

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