ThatJeffSmith

Managing Different Physical Implementations of your Relational Data Model

Near the end of a product demonstration with a customer yesterday, someone asked how they could build physical models for each of their different environments using Oracle SQL Developer Data Modeler (SQLDev.)

Imagine having a application and database setup ‘out of the box’ that adequately described your meta data. But, you might have one implementation of that system for a very very large customer that required database partitioning. The physical aspects of that environment could be drastically different. The ability to create different ‘sites’ in SQL Dev allows you to do just this.

So what is a ‘site?’

Each physical model is based on an RDBMS site object. An RDBMS site is a name associated with a type of database supported by Data Modeler. Several RDBMS sites are predefined (for example, for Oracle 11g and Microsoft SQL Server 2005). You can also use the RDBMS Site Editor dialog box to create user-defined RDBMS sites as aliases for supported types of databases; for example, you might create sites named Test and Production, so that you will be able to generate different physical models and then modify them.

Physical models derive all of their properties from a relational model with only the physical implementation details differentiating the two. This is why you will not find partitioning information in a relational model. That type of decision happens at the physical layer.

Partitions are part of your physical implementation

So while I may choose to build a model for our very large customers, I can also have a default model for our customers that do not require custom mods. Or I could have a functional test environment where I store everything in a single tablespace and do not really care what is stored where.

So where do sites come in again?

When you go to create a new physical model, you are given the option to choose from some default environments – namely, the RDBMS vendors and versions of said databases that the tool supports. For example, you’ll see things like Oracle 11g or DB2/UDB 8.1.

If you want to create a custom environment, or site, then you will need to use the Tools – RDMBS Site Administration screen.

Create your Site labels and associate them with the proper RDBMS

Once you have gone through this step, you’ll be able to choose from the new sites when you ask for a new physical model.

Creating a new physical model for your Exadata implementation?

Note that you’ll not see anything different in the diagram portion of the model. You will only see the information stored in the physical model via its browser tree elements.