ThatJeffSmith

SQL Developer Data Modeler Quick Tip: Use SubViews

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