11 February 2011

Data Modeler: Working with Different Database Sites

SQL Developer Data Modeler supports the option to create different implementations (physical DDL scripts) based on the same relational model (tables, columns and constraints).  The Data Modeler itself can be visualized as a model!  Your complete design can be made up of logical (ERD) models, relational, physical models and indeed datatype and process models. If we just think of the logical, relational and physical, then one logical model can be transformed or forward engineered into one or more relational models.  (i.e your entities can be transformed to tables) and then each of those relational models can have many physical models.

So you may, for example, do a generic logical model for an airline application, but transform it to a number of slightly different relational models (applying different transformation rules or perhaps different glossaries.  Now once you have that relational model set, you can have a number of different physical models. Often we have a test, development and production environment and want to have slightly different physical settings for these. We can do that in the physical model.

So let's assume you have the relational model all set.  Add new database sites using the Tools > RDBMS Site Administration as shown above. This invokes the RDBMS Site editor. Here you can add as many new sites as you need. They can be reused, so there is no need to be too specific, you only need to add new sites if you want more than one, say Oracle Database 11g, physical model in a design. In the next image we show that three additional sites have been added for the Oracle 11g Database.(click on any of the images to see them in full size)

Now return to the relational model and create the new physical models. To do this, expand the Relational model and select the Physical Model node.  Right-click for the context menu and select New. This allows you to choose the database site you wish to associate with the model. You can do this for each of you test, development and production sites.  In the image shown above right, there are 4 different physical models.

Once you have made the physical property updates for each physical database, you can start the DDL generation.  Ensure you select the correct Physical model from the database sites in the DDL File Editor dialog before you start the generation.

Cloning Sites
Now each of these sites will have all the relational detail from the diagram, so the tables, columns, constraints will all be the same. You've created the different sites, so that you can have different tablespaces and possibly user definitions, but chances are you'll want your sequences and PL/SQL code to be the same.  You can do this by adding that content to one of the physical models and then cloning the others from this. Select the new physical model and right-click, select Clone From.  You can now select the model you want to clone.