Systems DataBase Migration

  

Hi,

I'm working on a system migration project, I have a database with more than 23 Entities, and they aren't normalized, I mean, I have same Fields and Registers in several Tables.

I would like to know if there is a proper way of doing a DataBase bootstrap / import from an Excel spreadsheet into Outsystems and link the data through Identifiers without compromising or losing the data integrity, taking into account that I have repeated fields, with repeated registers in several tables.

For example, I have the House, Sellings, Payment and some other tables, that have the fields: HouseNo, Road, and Batch in all of them.


Thanks in advance.

Basically you have to be careful on 2 aspects.

First, in the order of the bootstrap, so that all the information that is required for an entity is already on database when you need it (the foreign keys).

Second, add "lookup fields" in the excel spreadsheet so that when you are bootstraping your data, you can use aggregates to look for the ID's in the other entities using a field that will enable you to find the correct record (the field must have a unique identifiable information) and thus retrieve the correct "id" that was created in the previous step of the bootstrap.

Hope it helps.

Cheers,

Eduardo Jauch 

Hi Eduardo,

Thanks for the reply, the first part is done, I have all the tables in one big spreadsheet, and ready to bootstrap.

On the second part, I will try to use the LOOKUP function, although i don't quite understand how to use it to my advantage.


Thanks again,

Will be posting Updates.

Solution

Hi Moises,

You need to be able to make the "connections" between the tables.

So, imagine you have a table called Employee and another one called Company.
In Employee, you create an attribute CompanyId.

In your spreadsheet, you need to add a column to the employee table to tell which is the company every employee work. 

But you don't know which ID each company record will receive.
So, you chose a field in Company that uniquely identify each record, like the Company Name, and add this information to the Employee "Company" field (the extra one you created to store the "lookup" value.

Now, in bootstrap, first you need to import Company.
Each record will receive an ID.

When you do the bootstrap of the Employee, you need the Company ID, but what you have in the excel is the Company Name. So, before inserting the new Employee record in the database, you lookup in the Company table for a company with the Company Name you have. The record returned will have the ID, that you can use to update your information in order to be able do insert the new Employee record.

So, if you are mention the lookup excel function, (like vlookup), I would use it just to create the correct lookup values in Employee (in our example).

The rest is done all in the bootstrap code.

Cheers,

Eduardo Jauch

Solution

Thank you so much Eduardo, your answer helped me to solve the problem   

Glad to help! :)

Cheers,
Eduardo Jauch