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.
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
- Create a new database table called
RoadType with two columns,
- 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.
- Create a foreign key connecting the
Roads feature class
Type column with the
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
- 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
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.
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.
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.
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.
Geodatabase topology (you can also use GP tools such as
Validate Topology and
Export Topology Errors as part of your Python script).
Data Reviewer extension provides a lot of checks. Again, you can easily schedule a task as specified here in the Help page.
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.