Restricting values to domain codes (beyond attribute table)?

Business rules vs data integrity rules

When you model your database, you specify the business logic in two spaces:

a. The data integrity rules. This includes among others having an integer column so users won't be able to enter strings.

b. The business domain rules. This includes having check/unique/foreign key constraints on columns so it won't be possible to store duplicate values or illegal values.

I think it's a valid choice to use geodatabase domains to set up rules for the business domain, however you should keep in mind that those rules are really just helpers as they do not expose any restrictions on the database table itself. On the other hand, you might like your geodatabase playing a central role in defining the business rules for ArcGIS end users.

Possible solution

Here is what has worked for me best over last years. Say you have a feature class Roads (stored as a database table) which stores information about the roads and it has at least one column called Type.

1. Create a new database table called RoadType with two columns, Id and Description.
2. Populate this table according to your business domain rules (road can be of major, minor, highway, interstate, motorway, ramp type). So, you will have say 10 rows in this table.
3. Create a foreign key connecting the Roads feature class Type column with the RoadType table Id column. This means that it won't be possible to define for a feature as a road type a value that is not in the Id column.
4. Now you use this RoadType table to create a geodatabase domain (can be done either in arcpy if you are comfortable with scripting or using GP tools).

Advantages of the solution

1. You keep your business domain rules in a plain database table available for other applications who can work with SQL and they don't have to work with XML (as when one is trying to get information about geodatabase domains using plain SQL). If your database table will be edited by other applications that are not aware of ArcGIS geodatabase domains (maybe you have a non-spatial batch operation loading data into the Roads feature class every night), you can be sure that the data integrity will be enforced.

2. You are still using geodatabase domains for ArcGIS users who are editing data in a rich client such as ArcMap and when editing feature's attributes, they will see the available choices for a field that has a domain associated with it. This is indeed helpful.

3. When you find out that you should add a new road type, it's all about writing a short SQL snippet that will insert new rows into the RoadType table and then using arcpy to update the geodatabase domain accordingly.

4. This makes maintaining the geodatabase domains very easy - should you move your database data to another database where you don't have Esri enterprise geodatabase enabled, your data integrity rules will still be enforced. Also, if you will load your database tables into a new enterprise geodatabase, you can always create geodatabase domain using the RoadType table since your business rules table (RoadType) is following your feature class.

QA/QC using ArcGIS

One would also like to perform various QA/QC operations on the business data. Non-spatial data integrity can be enforced fairly easily as described above. However, checking for spatial integrity can be a lot more difficult to perform. Even though one could write SQL using Oracle Spatial or ST_Geometry functions, I'd suggest using ArcGIS tools for this.

You can use multiple tools for QA/QC that can do a lot of checks in an automated fashion. It is important to note that you can schedule your Python modules to run at a certain time using Windows Task Scheduler.

1. Geodatabase topology (you can also use GP tools such as Validate Topology and Export Topology Errors as part of your Python script).

2. Data Reviewer extension provides a lot of checks. Again, you can easily schedule a task as specified here in the Help page.

3. Custom Python scripts that where you can use the power of arcpy to generate reports on the QA/QC done and edit your data as needed.

An alternative is to check for erroneous values after they've been entered, rather than prevent them from being entered in the first place.

It's been a while since I've done this. A rough workflow is: For each table, for each field with a domain, check all values against the associated domain.

This could be done in Python, although it would be painfully slow. I prefer to do it in procedural SQL (PL/SQL is lightning fast). I can check the entire database in less time than it takes for ArcPy to warm up (approx. 5 seconds).

Rough steps:

1 . List of all fields and their domains, for each table using SQL (table_name, field_name, domain_name)

2a. Loop through list of fields, check against lookup tables

2b. Or, instead of a loop, create a function to check values against lookup tables