In R&H; How do you ensure that primary key attributes - like the Route-ID - are and remain unique?

[MajaMesserli]

Attributes which are defined as key attributes can be changed manually. This way it is no longer guaranteed that they are really unique.

- A Unique index is not possible because it is too strict and a historization is not possible.

- Unique Index on multiple fields, e.g. the from_Date, To_Date fields,is not a solution either, because the date fields are not single values, they define time ranges. 

How do you deal with it?

[RyanKoschatzky]

Maja,

All our LRS editors have a training class on our documented business rules that must complete before they are allowed to edit in production. This includes them editing individually on a lower stage and having an experienced editor seat with them for while when they start editing on production. Our route id is 11 digits.

Different routes classes have different uniqueness. Some are one number per county and others for the state. We have a simple counter program to track the next number. Roads and Highways does have some time overlap prevention and warnings built in. On top of that we have a robust data reviewer process set up that editors must run before posting. We also run system wide checks frequently to ensure bad data is not getting through. Since we edit in SDE, we can and do delete editors version when there is issues. While that might be extreme, it is quicker and safer to redo the work then to troubleshoot the existing problem and deal with unknown downstream issues. Most folks learn from these experiences and helps to prevent future issues. Its not 100% but education, training and verification help to cut down on the issue. 

Hope this helps and answers your question.