Data modeling: best way to create data model with existing data and FKs

Hi there,

I have downloaded some data from an existing database (between 100-1000 records), and I would like to create a core for this data to use in other applications.

This data is in the form of Excel sheets, so I was bootstrapping the data in to OS to various entities. To create a data relationship model however, I am wondering what the best way to do this is, especially regarding Foreign Keys and Identifiers.

This data already has Foreign Keys and Identifiers per table, so for example Table 1 has a FK reference to Table 2's Primary Identifier. Some tables have multiple FKs.

I mapped out the Entities first, with corresponding attributes from the Excel data. I imported the FKs and Primary Identifiers as Integer data type. It's at the next step that I'm stuck:

Mapping the entities together with the right FK-PK references to each entity. I changed the corresponding FK data types to the corresponding Primary Identifiers, however the entities display no records and throw up an error in Service Center:

"Timer BootstrapAtp1s error (inside action 'BootstrapAtp1s'). Timer duration = 0 secs:The INSERT statement conflicted with the FOREIGN KEY constraint "OSFRK_OSUSR_S3N_ATP112_OSUSR_S3N_PROJECTS4_PROJECTTECHID". The conflict occurred in database "IDO7JY006", table "dbo.OSUSR_S3N_PROJECTS4", column 'ID'.
The statement has been terminated. [retry 2 of 3 scheduled] "

I understand that this error has to do with the underlying database not finding the right keys to map.

Therefore my question: what better way is there to achieve this data relationship model?

I found this post from a couple of year ago, in which they advised to simply import all the keys as integers, have OS create auto numbered Ids per entity and then to "map the legacy system's Ids with the OutSystems created Ids. " What do I have to understand as in "mapping"?

https://www.outsystems.com/forums/discussion/47705/bootstrap-to-upload-initial-bulk-data-through-exceldata-from-legacy-systems/

The screenshot gives an example of what my data should look like:

"TechId" is the Id from the database, Id is the auto-generated Id from OS. 
"ProjectTechId" should be a foreign key of the project's TechId. 
"Status" should be the foreign key of the Status Entity's Id.


Thanks in advance.


edit: I have tried to set the FK constraints to "non-mandatory", but this yielded the same type of error.

Schermafbeelding 2022-07-27 103648.png

Solution

Hi JP, 

It does seem like creating the entities first and populating them with the data afterwards was for some reason creating some issues. 

Instead, creating the entities by importing the data and then amending the entities to fit the data was what seemed to be the best path to follow.

After doing than, I didn't seem to have any problems anymore with FK mismatches and it was smooth sailing form then on. (Changing the data types was perfectly doable, although I of course still always had to "cut&paste" the amended entity when setting the different Identifier, in order to update the underlying database.)

This issue is solved now and I was able to build out the core.

Thanks for your help.


Kind regards,


Rémi

Hi Rémi


Rather than diving into the specifics of your question i would like to alert you to the following documentation page where it advises not to use of Data Definition Language, and that includes DDL in bootstrap processes that create database entitites.

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

Regards

JP

Hi JP, 

Thanks for your post! I took a look and as far as I know, I'm not using any custom SQL to create database entities, nor utilizing DDL for this action (at least not intentional).

I am using the standard bootstrapping action created by OS when selecting "bootstrap from Excel to entity". I'll attach a picture to illustrate.

The problem only arises when setting one of the attributes from an entity as a FK, relating to the Identifier of another table.


 That's when I get the error "The INSERT statement conflicted .... with FK constraint....."

Kind regards,


Rémi

Well in that case i wouldn't change data types. 

First step i would create all the entities and populate them

Second Step Create de foreign key columns (manually if necessary)

Third Step Create a timer to match the relevant foreign keys update all the entitites .


Solution

Hi JP, 

It does seem like creating the entities first and populating them with the data afterwards was for some reason creating some issues. 

Instead, creating the entities by importing the data and then amending the entities to fit the data was what seemed to be the best path to follow.

After doing than, I didn't seem to have any problems anymore with FK mismatches and it was smooth sailing form then on. (Changing the data types was perfectly doable, although I of course still always had to "cut&paste" the amended entity when setting the different Identifier, in order to update the underlying database.)

This issue is solved now and I was able to build out the core.

Thanks for your help.


Kind regards,


Rémi

Hi JP,


Thank you for the info, I will have a look in to it.


Kind regards,


Rémi

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