Loading...

News

2015-02-24

The value of Oracle’s SQL Modeller in Data Model Creation and Version Management


It is common in most green field development projects to have some type of data modelling tool (e.g. ERWin, SQL Modeller, Toad Data Modeller) in use...

It is common in most green field development projects to have some type of data modelling tool (e.g. ERWin, SQL Modeller, Toad Data Modeller) in use. These tools are often used up to the point of production deployment. It is quite common for data model management practices to lapse or fail after this point. It is not uncommon for data models to be managed on an ongoing basis by adhoc development scripts. This approach can be inefficient and error prone. I have found the Oracle SQL Modeller tool to be valuable in the way that it can be used to manage data model versions and to generate DDL scripts which migrate one model (e.g. production data model) to match another (e.g. UAT data model). An example is outlined below.

Consider the scenario where you have a new version of a data model in UAT that needs to be deployed to Production. Figure one below illustrates an example production schema (Demo_V1) for a student database, while figure two illustrates a UAT schema (Demo_V2).

Figure 1 – Production Schema/V1 for Student Database

 

Figure 2 – UAT Schema/V2 for Student Database

You will notice that the UAT model has additional objects relative to the Production model.

In a normal development cycle, scripts would need to be prepared to migrate model V1 to model V2. This can be achieved in Oracle SQL Modeller using the Tools->Compare/Merge Models option.

Figure three below illustrates the output of the model comparison process.

Figure 3 – Output of Model Comparison Process.

Clicking on the DDL Preview button illustrated in figure three above facilitates the viewing of the SQL DDL code which will migrated model V1 to model V2. This is illustrated in figure four below.

Figure 4 – Preview of DDL to migrate data models

With reference to figure 4 above, it can been seen how running the script in figure four against the production database (V1) would alter it to match the UAT database (V2).

Considering the above example, it is easy to see how Oracle Data Modeller in conjunction with good version control tools and processes can be used to migrate data models/environments in an efficient and risk free manner.

By Stavros Mothonaios, Senior Software Developer 

Back

Other News Articles

  • Page 1 of 2
  •  
  • >
  • >>