Skip to content
August 7, 2006 / Steven Pousty

OBJECTIDs Primary Keys, and Documents for geodb best practices

I am now on to making many to many relationship classes because a patch of Weeds can have many species and a species can belong to many patches. I have a little test UML diagram where I try out the concepts for a simple schema. I try to create a Many to Many between a table and a polygon featureclass. I can’t get the relationship class to understand if I specify the objectid as the primary key and the polygonid (integer) as the foreign key. If I change it to some arbitrary text field for both PK and FK it works fine.

Then I do some searches on support and find this gem of a thread, which basically states never use OBJECTID field as a PK because when you export the featureclass all the items may renumber themselves. WTF? Which may lead to a much larger philosophical problem. How do I autogenerate primary keys? Can I use autogenerated OIDS as long as they are not in the ObjectID field? Oh wait – you can not have more than one OID field in featureclass. So it looks like there is no way to get an autogenerated column in a geodb without writing code. This has to be wrong. Please for the love of all that is right in the world of data consistency let this be wrong!

Where is this written? Where would I even begin to look for this. It would be nice to know these things before you actually design a geodb and have it in production.

Advertisements

4 Comments

Leave a Comment
  1. geometrybag / Aug 7 2006 3:28 pm

    Steve
    you should be able to use Globalids and guid field types. you can create them using the case tools in uml, or in catalog in 9.2. Globalid’s will be auto generated, though they can only join to a GUID data type which is not auto generated. You will still get an issue if you export the data. see:

    http://webhelp.esri.com/arcgisdesktop/9.1/body.cfm?tocVisable=1&ID=1469&TopicName=ArcGIS%20data%20types

  2. thesteve0 / Aug 7 2006 4:26 pm

    Thanks! I tried that and it worked to generate a unique field but there are quite a few problems with using a GUID, most of which are summarized nicely at:

    http://www.databasedev.co.uk/primary_keys.html

    Plus, since it is the replica ID field I bet this could cause problems in other unexpected areas if I actually used it.

    There doesn’t seem to be a good solution at this point – especially with regards to a Personal GeoDB. Access only allows 1 autoincremented column per table and ObjectID already grabs that. So I can’t even set a column to autoincrement manually. I could use a class extension for a feature class but that seems so wrong. The biggest drawback I see it at you have to install the dll on any machine that wants to even view the data.

    I am still ruminating and trying to think of a different solution…

  3. matt m / Aug 7 2006 9:08 pm

    What confuses me about this is why, in your model, the relationship table does not have a separate PK column. To achieve many to many, I would expect a join table with a generated PK sequence, two FK columns, one pointing to the data table, one to the polygon table, and then a type field or some such that adds attributes to the relationship. Both the data table(weed species) and the polygon table(weed patch) should have their own independent PK sequences.

    Having a two-part PK or a non-artificial PK column is for the textbooks, not for the real world! Also- using UML to design databases can be hazardous to your sanity (object-relational mismatch and all that).

  4. thesteve0 / Aug 7 2006 9:53 pm

    Hey Matt:
    That picture is very simple just to see how the UML works for doing these kinds of things. I really should have used a different picture since there are several errors in that picutre. I have used composite primary keys before and they have worked well when there was no attributes in the join table. I am not saying my picture is how things should be done, just showing the odd way you do many to many with UML and geodbs.

    I agree that this exercise is detrimental to my sanity (see my posts from the last week). I am coming to the conclusion that a geodb is neither object nor relational but some sort of propreitary almalgam of the two. It makes my relational side squirm as I try to do what I think is right only to find that things can’t really be done that way.

    I have to work with ESRI software and what you are seeing is me grappling with how to understand the way ESRI wants you to store your data. I am sure you will see more postings trying to work my way through this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: