Managing Different Physical Implementations of your Relational Data Model

thatjeffsmith SQL Developer 6 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 6

  1. Jeff,

    I’m able to compare two previous versions of my models following your instructions from the link, I’m not sure but i was doing something wrong before

    Also I figured out using storage template we can apply the properties to the new tables

    As you indicated going forward comparing two version using version history when are we going to get this feature so we do not have to check out the older versions.

    So far it seems pretty east when you have a model and you are making changes to design which is stored in your repository and applying the changes with DDL to the DB

    But what if each time you get a updated relational model from client which is not in your repository and you have to compare against your HEAD revision from your model from the repository that way you will end up with adding new model from client each time in your repository and changing physical properties using storage template before comparing with your HEAD revision

    Is there a better way when we we get a modified version of the model from the client we store it in our repository as upgraded version from their previous version that way we can compare two versions of clients otherwise it would be like comparing two different models even though it is same model with some changes each time each time and the time to change all the physical properties will be time consuming if we have many tables

    I hoped you are not bombarded with so many questions

    Thanks,
    Zafar

  2. Hi Jeff,

    Please ignore my previous comment, I did follow your link from the comments and I’m able to checkout
    with revisions

    Also regarding my first question SQL developer 4.0 has storage options in the compare and it is generating DDL when you modify physical properties
    tablespace/users or add partitions so i guess i got the answer for my first question

    Jeff could you please clear my questions with your expert answers

    1)When I check out just dmd file which is part of the list with other xml files in the revision folder I do not get dmd file after checkout

    But if i checkout revision directory i get the model to compare.

    Also I guess if we are at revision 10 and want to compare revision3 and revision 4
    we will be checking out revision 3 and revision 4 in two work copies and opening one in the modeler and doing compare with the other
    as you exaplined in your link

    2) While I was browsing through each revision folder i found some folders has only xml file but no dmd file when the action is A,M etc
    So how version is upgraded each time is it upon commiting the design from pending area after add/update/delete
    actions on the design and saving the design in the working copy

    Or are you suppose to checkout the current model in the branch and merge/tag with trunk to upgrade the version

    And my last question would be if our client send us a new logical model/relational

    And we added physical properties like tablespace/user etc and version control the model say at revision 1

    Client send us again same model with two new entities/tables

    What we would like to do is pull the physical properties which we added at revision 1 and add physica properties only to the
    two new new tables client added in their logical/relational model before saving the model as new version 2

    How can we achieve this

    Thanks in advance

    Regards,
    Zafar

  3. Thanks Jeff.

    When I tried to checkout the model from Team->Subversion->Checkout and browsed through the folders in the SVN repo

    I have folders for example

    scott
    and SQL developer created sub folders under each model directory (businessinfo,datatype,logical etc)

    schema2 and so on

    But i do not see any .dmd file.

    I’m able to get the dmd file only if I checkout the whole folder say scott

    Is there something I’m not doing correct while doing checkout.

    I would really appreciate if you put some screenshots or steps

    Thanks in advance.

    Your site is excellent with very good vedeos and easy explanations

    Thanks & Regards,
    Zafar

  4. Hi Jeff,

    I would like to know two things

    1) As Nilesh pointed out how to generate DDL if we make changes to physical properties in the model
    such as tablespace name and push the changes from the DDL generated to the DB

    2)How to do we version control Data models
    I have saved initial design in the subversion and have made few changes and commited those changes to SVN

    Now when I try to checkout old version I get all the folders SQL developer created when I saved the model from SQL developer and added to the repository,
    but i did not get the dmd file for old version there is only one dmd file that I was commiting to SVN each time I modify the model

    How do i acheive this so i can have different versions of the model in the repository for comparisions

    Thanks,
    Zafar

    1. thatjeffsmith Post
      Author

      for #2 – you shouldn’t be checking out files and folders, only the designs/models in the SQL Developer UI. If you open the multiple versions from the repository, you can do a Compare. Does this help?

  5. Hi Jeff,

    In SQL Datamodeler, Is there a way to generate DDL to make changes to Existing Database based on changes that were made to Physical Model in datamodeler.

    As an example
    1. Import Model from Data Dictionary from Dev Database
    2. Make changes to only Physical Model, as an example, change two B-tree indexes to Bitmap with partitioning

    Can we now generate the effective DDL which will update the Dev Database with these changes.

Leave a Reply

Your email address will not be published. Required fields are marked *