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.
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.
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.)
28 Comments
1. I have a model with round 60 subviews. I need a list of the subviews in xls. Can I export the subviews names?
2. Alternatively if 1. not possible: Can I create from subviews new separate models? Then I would be able to export the separate models to xls
Hi Jeff,
i am new to db. if you make any changes in sql query it gets reflected in ER diagram. is it possible to reverse this, i.e changes in ER diagram should reflect in the SQL query and tables
Thanks
Queries don’t change a model. Models don’t change queries either.
Please give an example if exactly what you’re trying to cut achieve.
i have a ER diagram. i edit the ER diagram. the changes i make in ER diagram shud reflect in the tables. is it possible to achieve this
If you have a relational model, yes. Compare your model to the DB, generate an alter script, review it, then run it against your database.
Hello Jeff,
i am a c++ programmer.. i want to program to read relations, primary key, foreign key from either ER diagram or table.. can u give me some ideas to do this.. i am totally new to DB…
Can you elaborate on your question a bit, what exactly would you like to build?
Hello jeff,
I have an ER diagram.. on our screen we are able to read from tables or from ER diagram, the relation ship between two tables primary key, foreign key etc.. same thing i should be able to read through my program, i mean i need to program in C++ how to read cardinality, primary and foreign keys(complete info) from tables or ER Diagram..
the model is stored in XML files, so you can reverse engineer our xml, OR, you could connect to the database and read the relationships yourself from the data dictionary views.
I’m in the process of creating an ERD for 700-800 tables. Everything is under 1 schema, but the various tables are used in different modules of our system. I am creating subviews for tables in each module, which is fantastic. I actually get useful diagrams this way.
My only concern is the performance of this model. It takes about 15-20 minutes just to open the file in the data modeler. Is there anything I can do about this?
I really prefer to use subviews instead of creating separate models for each module. The ability to search for missing foreign keys, and reverse engineer changes into the model are just too great to give up.
Thanks!
Bump up the size of your JVM to 2 or 3GB
I upgraded from 4.1.5 to 4.2, and also bumped it up to 3GB and it now takes 3 minutes to open the relational model. Thanks!
Out of curiosity, do you recommend having a single model for 700+ tables, and just using subviews to break everything up? Or does it make more sense to have seperate models for each module?
I think I would just recommend you go with what makes sense and is easier for you.
If it were me, I’d have a design/model per application module….unless modules were linked, and then I’d have it all in one, and use subviews to break up the modules.
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.
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!
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 ?
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…
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
You have to add the tables and their relationships to the subview, not just the tables. So select the tables and lines in the diagram, right-click, create subView from selected.
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 ??
Yeah, when you have them selected, right-click and say ‘Create SubView from selected.’
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
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 !!!
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.
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.
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?
Pingback: SQL Developer Data Modeler Quick Tip: Hide and Resize
Pingback: SQL Developer Data Modeler Quick Tip: Use SubViews – All Things Oracle