Maintaing Referential Integrity in ArcSDE

Discussion created by mwilloughby32 on Oct 22, 2013
Latest reply on Oct 8, 2014 by MLF

The organization I work for is in the midst of migrating all of our infrastructure asset data into ArcSDE from a foreign system. We are in the data model planning and review stage and have based our data model on the ArcGIS for Local Government data model templates.

There is a business need to have numerous related tables attached to our spatial features to track the lineage of some of the attributes that will get entered. We also need to generate sequences for primary and foreign keys, check for and enforce uniqueness of those generated sequences and finally, there will be a need to invoke some stored data management procedures on insert or update of records feature classes and/or the related tables.

We are using ArcSDE with MS SQL Server 2012. In a multi-user versioned environment, what are todays best practices for managing this:

-using database level tools (triggers, constraints, sequences, etc.) on the SDE.DEFAULT tables (probably a bad idea);
-using database level tools on multi-version views;
-using ArcGIS desktop or other custom developed application level tools instead of database level tools;
-some combination of part or all of the above.

I would prefer to have referential integrity enforced at the database level but my intuition tells me that the multi-user versioned environment will get in the way of that.

Your feedback is much appreciated.