OutSystems Entity Field Keys Linked to External Systems
December 2, 2021
– Data Migration Impact –
Think for example you have a Digital Onboarding Solution in OutSystems, and the need to have it integrated with an external system for a specific process like AML, KYC or just fraud prevention.
Should you share with the external system the OutSystems entity ID fields to reference your records, and are you aware how that will limit you in the future?
Database Entity Connections
In your usual application development in OutSystems you will create many entity relationships [1] of different types: One-to-one, One-to-many, Many-to-many. You can define PK – FK relations through Reference Attributes [2], by which OutSystems automatically creates database constraints as needed.
Since these relations between entities and fields are usually using the ID columns in OutSystems, and ID columns are auto number fields, when you are doing a data migration between OutSystems environments it is critical to maintain the relationships between the right records.
In an example with connected tables Customers and Orders:
[3]
You know the Customer ID “12345” from the above picture will have in the destination a new number (for example, “67890”) and the 3 order records will also change their Order IDs.
When you migrate the Orders table you want those 3 order records to still be connected to the same customer (whose ID is now “67890”).
To achieve this, your data migration tool (like our Data Migration Manager – DMM [4]) must create/insert the Customer record in the destination environment, check it’s new ID given automatically by the database (“67890”), and then when creating/inserting the Order records in the customer ID field put the new ID instead of the old/original one – this way you guarantee the relationship and coherence of the migrated data.
Likewise, if your data has self-references, or a circular reference, you must first insert records and then update the FK fields to guarantee those relationships.
Maintaining Primary Key Integrity
When you are working in a closed system, the attention points mentioned before are usually enough – if in such a closed system we usually assume you don’t need to keep the IDs for some business or operation need when migrating data.
Operation needs can be, for example, to guarantee data integrity in multiple, sequential, or differential migrations – to achieve this it is impossible without guaranteeing primary keys integrity like DMM does, either by memorizing previous executions to guarantee it in the Incremental Migration feature [5], or just plainly having the ability to edit the IDs.
Also, in tables where you do not have a secondary unique columns or group of columns this is the only way you can identify a row – DMM offers you the User Mapped Table feature if a group of columns is unique, but if such doesn’t exist, you need to be able to change the auto-generated IDs.
The DMM tool does this out-of-box in the background when the database connection privileges allow for it [6] – guaranteeing the same ID in the origin and the destination.
Business needs however, many times are related to the fact that solutions aren’t stand-alone and are integrated with other systems. When such external integrations happen, it is common to have IDs “traveling” between systems. Think for example of a digital onboarding solution, where a certain customer ID is propagated into another non-OutSystems system for KYC or AML purposes.
In such situations, when you are migrating data between OutSystems environments, getting a new Customer ID in the destination environment may be a problem – if for example that means a connection to the external system will no longer work since the ID is not there (or worse, it exists but to another record), and that failure for example stops a certain flow in your QA environment or creates abnormal behaviour in your application.
To maintain the IDs while migrating data in OutSystems, you will need to have DDL permissions on some database objects, namely:
– In SQLServer, Alter permissions on the OSUSR_* tables;
– In Oracle, Alter permissions on the OSSEQ_* sequences. [6]
However, in the OutSystems PaaS Cloud, such DDL privileges are not possible, so the best option is not to create these dependencies with external systems using the auto-generated IDs of entity records – instead, developers should use some business field they themselves control to identify the relationship whenever possible.
Thinking of Future Needs
Most enterprise level complex solutions will have the need at some point of their lifetime to maintain coherence and integrity of their data keys [7].
When migrating data, the DMM solution for OutSystems is the only solution that can keep those IDs, given the right database privileges [6].
As a best practice in development of integrations with other non-OutSystems solutions, try not to match records by their IDs (that are auto generated) but by some business fields, since that may save you a lot of effort in the future!
[3]
