How to normalize data in ObjectGears

22.03.2016

Every database system administrator or developer has probably met following situation already couple of times. You start with a text field that gives users flexibility in data input but realize later on that  you would rather need a reference to a master data table (foreign key), in order you address data quality and denormalization. E.g. you need to record other properties of the referred entity or link it to other information from your system. How ObjectGears can help with solving this issue? That`s the topic of this article.

ObjectGears screens out the user from the database implementation. This is why it provides also functions that would otherwise be solved by the database administrator.

You may come with following idea how to solve the above stated issue in ObjectGears:

  1. Create a new class for the given master data

  2. Create a column with reference to the new class/master table in the class containg the text column that shall be replaced

  3. Export data from the class with text column into file

  4. Import data from the file to the class containg master data by means of function importing unique records

  5. Filter records according to the text field in the class step by step and set corresponding records in the column with reference to the master data by means of mass data update

Steps 4 and 5 could be solved also by creating a mapping table containg various values in the text column and corresponding values of the master class and by an import with mapping query used for the import.

ObjectGears provides also other possibilities that you can use for more extensive modifications in the data model and data migrations.

Function CreateTypeRows

You can launch this function in the ObjectGears script window. There are following input parameters to be used with the function: Identifier of the text column, identifier of the column with master data values and identifier of the column with reference. This single command solves everything for you. If you want to perform data cleansing, do it before running this command by filtering and mass data changes.

Master class export by means of Admin tool

You can perform the same operation also in Admin tool. You will select classes and columns in comboboxes.