Static tables Identifiers should not be auto-number by default.

On our radar
When publishing an eSpace in a various servers, there is a high possibility of wanting to migrate data between them.

If the Static Table Id is an auto-number, then it will have different Identifiers in the database for each of its records, when compared to the original server, resulting in wrong information.

It should be filled out like the Order attribute, in order to maintain the same Identifier in the database, even when published in a different server.
Created on 11 Jun 2013
Comments (1)
I agree 100% that there needs to be a deterministic way of providing IDs for static entities, to ensure that data migration between servers works well. I've gone through one massive data migration in December, and we're preparing for another one in a few weeks, so I am VERY familiar with the pain of migrating data, and then handwriting update statements to switch the static entity IDs around for the tables that refer to them like Rubik's Cubes.

While I agree 100% about the need for deterministic IDs, where I disagree on is the idea of allowing developers to specify the ID.

The big problem with having the developer specify it, is the following scenario:

1. Record created with ID of 1.
2. Data created, tied to ID 1.
3. Static entity is deleted.
4. Developer tries to create another record with an ID of 1.

Now, the previous records that were created pointing to ID 1, will be pointing to a new record that means something completely different than what it used it. That's a problem.

The way the system currently handles it, is to ensure that even if you delete and recreate a static entity record with the same name, a new ID is assigned. This ensures that records pointing to the original do not point to the new one.

By allowing the developer to specify the ID, *at least some developers* will attempt to use "meaningful" ID. They'll want to use the ID as an order and sort by it... or the ID they specify will match a number in their business... or something else. This is a violation of what I call Most Important Rule in Database Design: NEVER have the primary key tied to business data! By allowing the devs to specify that ID, we allow them to violate this Most Important Rule. :(

My alternative proposal is to have the ID driven by something deterministic, such as the underlying Service Studio GUID. This is still a random, guanranteed-unique, non-developer-selected value, but we know that it gets determined once when the static entity record is created and never changes. Since that is a poor item to be JOIN-ing on, take the GUID as the basis for coming up with a pure integer number (do a hash of some sort?). Or still use a sequential number, but have it come from the OML.