SQL Developer Data Modeler Quick Tip: Use SubViews

thatjeffsmith SQL Developer 15 Comments

Tell Others About This Story:

Modeling is cute when you’re in a class and the sample application is for a rotisserie league for fantasy football, or you need a shopping cart ordering system for online retail. You spend a few hours and might come up with several dozen tables.

But what about in the real world when you are trying to wrap your head around something with several thousand (or more!) objects?

Enter the SubView

SubViews allow the user to take a group of entities or objects from the main model area and place them in a separate space. It’s always easier to break down really big problems into smaller, easier to digest pieces. So instead of having the ENTIRE list of VIEWS from SYS in 11gR2, I might only be looking at the views that the SCHEDULER depends on. There’s only 30 or so of those objects.

There are 2 ways to add an object to a SubView

  • Drag the object from the tree into an existing SubView diagram space
  • Multi-select the objects from the model space, and choose ‘Create SubView from selected

To get an idea of why this might be necessary, check out how far out I need to zoom to just see all the objects in my model.

3800 views in one model - not very pretty

Create the SubView

Since my objects are all over the model, I need to drag them from the tree to an existing SubView. So I need to create the SubView first. Easiest way to do that is mouse-right-click on the ‘SubView’ tree parent node and choose ‘New SubView.’ Once it’s created, you’ll see a new model space on the right. If you don’t see it, simply select the new SubView in the tree and mouse-right-click and choose ‘Show Diagram.’

Add the Objects to the new SubView

Find your objects in the model tree, select them, and drag to the SubView.

Drag objects to your new SubView.

A few notes about SubViews (from the Help)

“There is no difference between performing changes in one of the subviews or in the complete relational model. Any changes made are immediately reflected in the complete relational model and any relevant subviews. However, you can remove tables and views from a subview without deleting them from the complete relational model.”

So, making changes to objects in the SubView is no different than making changes to the objects in the main modeling space. Also, if you do a RE (fancy acronym for Reverse-Engineer) from multiple schemas, each schema will automatically be represented in the model with a SubView.

OK, so now you have 30+ odd views in a model, now what?

I did something ‘fun.’ After reading Kris’ blog on how to build your own data dictionary view posters, I decided to do the same for my SCHEDULER views. So step 1, use the Tools – Views to Table Wizard for the views. Then – and here’s the tricky part – figure out what SHOULD be the Primary Key (PK) for the tables. Once set, you can right-click on the model and choose ‘Discover Foreign Keys.’ It will use the column names of those PKs and matching column names in other entities to identify what’s ‘related.’

How can we share our model?

Use the ‘File – Print Diagram – …’ feature to export your ERD (or any of the other diagrams) to the format you desire. I chose to share mine via image file (PNG.)

Oracle 11gR2 Scheduler Views Diagrammed

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

Comments 15

  1. New guy here – formerly DB2.

    Created sub-view with the right click on my main view then created sub view with selected objects – no problem. But now I want to remove it from my main model without removing it from either the browser or the sub view. Right clicking it on the main model and select delete object deletes it from everywhere. Thanks.

  2. Hi, Is there a way in the relational or logical model that I can virtually group tables together and have that represented visually on the diagram? Basically I would like to have a visual block on the diagram that goes around a few tables and then I want to name the block maybe. Is that possible?

    Thanks!

  3. Hi,

    I’m trying to figure out if the use of the subviews can help me with a huge database, but i have a problem with it.

    It is written : “There is no difference between performing changes in one of the subviews or in the complete relational model. Any changes made are immediately reflected in the complete relational model and any relevant subviews. However, you can remove tables and views from a subview without deleting them from the complete relational model.”

    So, i created the relational model from data dictionary and i created some subviews.
    Let’s assume i delete a table from a subview using “delete object”. The table doesn’t appear anymore in all the subviews neither in the main relational model. Fine.
    Now i update my relational model (from the same data dictionary of the same database) using “merge”. The deleted table is now there in the main relational model, but not anymore in the subviews… (the deleted table has FKs, so it should be linked with the ones in the subviews…)
    So i am wondering if the update works fine, or if i made mistakes.

    The thing is, once i created the main view and the subviews, i would like to update it when the database changes (in an automatic way). Is it possible ?

    1. I understand that the subviews “don’t know” that I would like the new table to appear inside.
      But i’m trying to find a way how it could be done. If a new table is created, i would like it to be shown on all subviews where the table has FKs…

      It may not be that easy, may be not even possible…

  4. Hi Jeff,

    I am trying to use subviews but have a problem.

    Have a big relational model imported from the database, using
    the dictionary feature in sql developer (data modeler functions).

    Then, create a subview and droped the desired tables into the subview.

    The tables have pk, fk, indices, etc defined for them.

    But, can´t see graphically some pre-existing relationships beetwen
    the 2-3 tables already dropped on the subview.

    They are listed there in the tree, under subview—>fk relationships.

    What could be my problem?

    best regards,

    thx in advance for your help

    hernando

    1. thatjeffsmith Post
      Author
  5. Hi,

    I am documenting a 353 table relational datamodel.

    I would like to make use of 6-8 subviews – some as extracts of the total design.

    I tried copy/paste (using right-click menu – no CtrlC/V shortcut exists – boommer) – BUT this results in new objects NOT a copy of the object view.

    I would like og copy part og the design to a subview.

    Is this possible ??

    1. thatjeffsmith Post
      Author
      1. Well, I got a nice result using subviews.

        So I put in notes to mark “table sections” and notes with colors matching the “Classifications” – nice.

        With 340 tables updates are frequent and the neightmare begins.
        One new table is inserted in the “main” diagram and to change the subview the section i selected and a new subview created.

        Ups!

        All notes are discarded – left is a naked subview with no notes demaring table secion headers or notes acting as color legend.

        Please tell me “peace of cake – just ….”

        Perhaps I could create a subview with notes acting as an overlay in PDF and merge it with the diagram PDF ??

        Rgds
        Henrik

  6. Hello Smith ,

    I have a problem with Fk names , when i try to do the reverse.
    exemple:
    In my logical model i put realtion names like : ” ***** ***** **** **** ***** **”
    when i try to pass to my relationnal model the Fk names are like ” ***** _*****_****_****_*****_**”.

    and i have this error :
    — ERROR: FK name length exceeds maximum allowed length(30)

    i tried to fix it on .. model name / properties / param / names and denom/ model and i did :

    {child abbr}_{parent abbr}_FK for Forreign keys but it does not work.

    thanks !!!

  7. Hi Jeff,

    I have dragged some of the objects from schema to relational model and then I saved the design. But when I close and opened the design it shows nothing. Could you please tell me if I am missing any basic step. BTW, the file I saved is .dmd file.

  8. Hi Smith,

    Can you please suggest me how can i create a model for 800 tables under 1 schema.

    I tried but its never ending process.

    1. thatjeffsmith Post
      Author

      I imagine designing 800 tables would take a VERY long time indeed. Is that what you’re needing help with – or are you having issues with the generation of the DDL script..or something else?

  9. Pingback: SQL Developer Data Modeler Quick Tip: Hide and Resize

  10. Pingback: SQL Developer Data Modeler Quick Tip: Use SubViews – All Things Oracle

Leave a Reply

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