Received this query from a customer this morning…
For your information, I’m average in SQL.
What I would really like to know is if you can give me some tips regarding the use of Oracle SQL Developer in relation to a completely new database. So, are there functions/tools in Oracle SQL Developer which can help me better understand a new database and it’s structure/tables/etc. I’m not looking for tips regarding creating a new database.
I’m hoping for the average customer, the system you will be working with has referential integrity defined IN THE DATABASE. I’m of course talking about foreign key constraints on your tables.
If that’s true…well, wait Jeff, how do we know?
Find a driving table – hopefully they are decently named, something like departments or customers or employees or parts or sales or beers…
Click on it to open it.
Go to the ‘Model’ page.
So I can clearly see that my DEPARTMENTS table is providing information to the EMPLOYEES table and it’s getting information from the LOCATIONS table. I can also see the column names, their data types/sizes, indexes, keys, and more.
Now, if you want to tackle this for your entire database/schema – then you’ll want to import your entire data dictionary to a data modeler design.
Once that’s open, you can see EVERYTHING.
And you can also add the object/column comments to the diagrams to help you answer questions like, what the heck is that column for. This of course assumes your data dictionary is documented via object/column comments.
The Problem with EVERYTHING
The problem with everything in many systems is that it will be too much info for you to process. So you’ll want to break things up into smaller (and hopefully RELATED) pieces.
We make fairly pretty easy with a featured called ‘SubViews.’
Find your driving table in the design/model tree, and right-click:
This will build a subview for you, automagically, of that table and all of the related tables. This will be much easier to get your mind around.
Ok, But We Don’t Have Any Foreign Keys
You’re not alone. We MIGHT be able to help.
You can ask the modeler to GUESS the relationships.
But Wait, There’s More!
We ship a bunch of reports you can run to learn more about your database.
So you can just ask questions, and these reports will help you get the answers. I’ve highlighted a few below that might be of interest…
2 Comments
Jeff,
While the modelling features built in to SQL Developer are great, I’ve long thought that for anything more than trivial models, standard ERDs become unwieldy.
If they’re hard to use, they’re of limited value – they are above all a communication tool that should enable developers, DBAs and other roles to collaborate over optimal logical and physical data storage designs.
Zoom is enabled in the Modeller tool, but not, it seems in the Model tab on table definitions. Scroll bars enable some navigation, but enabling cursor keys or “drag the canvas” might help a bit. On one of the driving tables in the system I currently work on, even in a sub-view there are literally hundreds of relationships to the driver. The lines become a nearly unusable solid black bar.
I have some other thoughts about how this might be made more usable, but enough, I have other work to do.
Regards,
J
we knew if we put ANY of the full modeler features in the Model tab, then users wouldn’t be happy until we had the FULL modeler product in that tab
so anything more than look-see, click the modeler button, then you can do anything you want
also, that’s not an ERD…