Data sync between environments: auto number or not?

Hi all,

I'm thinking of building a data synchronization process that synchronizes data from our production environment to one of the lower environments. The process of how to accomplish such a synchronization mechanism is clear to me but I'm wondering how to keep the id's of the foreign keys correct. All our entities have the Id column set to auto number. So synchronizing data from production to testing probably leads to different id's and therefor incorrect foreign keys.

What is your opinion how to deal with the auto number id's?

The way I see it, I have two options. The first option is to change the auto number of all Id columns to no. Downside is I have to change all my crud wrappers so that an id is generated when inserting a record. The upside is I can just truncate an entity and insert all records from another environment during data sync. And I can sync the entities in any particular order.

The other option is to have an entity in which I keep the id's from both environments and use this during data sync to convert an id from the production environment to an id in another environment. Upside is I keep the auto numbers and stay with OutSystems best practice. Downside is, entities have to be synced in a particular order and I have to build conversion logic for every entity.

Any thoughts, experiences, tips?

mvp_badge
MVP

You need to sync entities in a specific order anyway if you use any other Delete Rule on foreign keys than Ignore. In general, I would strongly advise against giving up data integrity or the ease of auto-numbers just for the sake of syncing production data to another environment.

So using mapping tables is, in my opinion, your best shot.

True @Kilian Hekhuis. I didn't realize that the entities have be synced in specific order anyway, because of the delete rules on foreign keys. Thanks!

mvp_badge
MVP

You're most welcome :). Happy coding!

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.