We are designing our initial SDE database in SQL server and are struggling with the best way to handle our primary and foreign keys because of the ObjectID field that gets created when we register the table. We want a second field to also generate an auto-increment field that we can use in as a foreign key in other tables and business analyst tools. The Business Analyst processes don't return the objectID in the output which is causing us significant problems.
The issue is when I register this feature class and it has an auto-increment field gets designated as the ObjectID which is a problem when I run a proximity report in Business Analyst because I can't select the ID field as my store number. The ID should also be a foreign key to our trade area polygon feature class and another attribute table. How can we create a separate field in our database table that will also auto-increment or auto-calculate but is not the ObjectID.
Additionally, we would like our trade area table to have a separate unique ID so the demographics can be stored in a separate table and then referenced via a relationship class to the location via the trade area. However, the location and trade area have a 1 to many relationship. If we just use the trade area objectID, it is not included in the results of my spatial overlay tool in business analyst, so I can't write that back to my tradeareademographic table.
Thanks in advance for any guidance.
