Oh, pretty pictures! I can do that!
The following are my favorite database design related posts and some tips and tricks for getting more comfortable with Oracle SQL Developer Data Modeler.
- SQL Developer Data Modeler Primer: Designs, Models, & Diagrams – Opening, Saving, and Navigating your designs
- Entity Relationship Diagrams (ERD) – Logical vs Physical Models
- Rant – Mixed Case Object Names
- Response to Normalization Myths That Make Me Crazy – warning, this is a rant too
- What Dora the Explorer can teach us about navigating our data models. Hint: It’s the map!
- How to hide non-key columns and resize your entities in SQLDev Data Modeler v3.1
- Build a mini-ERD based of selected tables and their related objects
- Model too big to deal with? Break it up into smaller chunks, AKA SubViews
- Lost your model? Database no longer exists? That’s OK, we can visualize and reverse engineer your DDL scripts too!
- How to setup your favorite data types for faster column and attribute design
- Managing Different Physical Implementations of your Relational Data Model Maybe you want a model for your Exadata implementation versus your test environment, so you can define different partitioning and storage schemes.
- Synchronizing Your Model With the Database (and vice versa) – how to compare and merge changes from model to database and back again
Just because you CAN create objects with mixed case names, doesn’t mean you should Mr-I-build-the-TaBlE-table!
I was inspired to write this after reading Karen Lopez aka @datachick‘ rant on folks that don’t normalize their data. If you don’t follow Karen or subscribe to her blog posts, then you’re really missing out.
Unless you have a 100″ display setup for your design machine, it can be really hard to navigate your data models. They often have a lot of white space separating the tables and entities. This blog shows you very quickly how to navigate your models.
Some tables are big. Not, wow-that’s-a-lot-of-data big, but, wow-did-you-really-need-200-columns big. This post shows you how to hide the white noise and shrink the table diagrams so that you can get back to the business of herding your tables.
I don’t need an entire database or schema for my model. I only care about this table and anything that’s related to it. See how to build this model in just a single ctrl-drag-and-drop of the mouse.
Some models just can’t be made smaller. Rather than give up, consider creating some mini-models. SubViews allows you to organize groups of objects and their relationships.
No database, no problem! As long as you have a DDL script, we can create a model for it!
You have several hundred data types to choose from. Most of the time you go with VARCHARs, DATE, NUMBER, and ________. Set those data types as ‘favorites’ and jump right into them when it’s time to design and alter your tables.
Twitter
RSS
GooglePlus
Facebook
Nov 29, 2012 @ 12:33:33
Jeff,
Your posts have been very helpful in getting familiar with SQL Developer and the Data Modeler. I have not been able to find good training or even an up to date book on SQL Developer. Do you have any suggestions on where to find good up to date training material for SQL Developer and Data modeling. Oracle University has a course, but it is not offered yet in the Training On Demand format or in a Live Virtual class with a schedule that works for us on the west coast. The most up to date book I can find is on version 2.1. I think the Oracle online tutorial covers version 1.5. Any suggestions would be appreciated.
Thanks,
David
Nov 29, 2012 @ 12:38:03
Books are good the day they’re printed – not so much the day after.
I’ve been playing around with the idea of publishing an e-book version of my SQL Developer posts, but fleshed out and more tightly integrated. But for now I’m concentrating on more content.
We do have our hands on labs that you can run through, have you seen those?
http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html
Nov 29, 2012 @ 12:59:47
Jeff,
Thanks for the quick reply! I for one would be very interested in an e-book if you put one together. Your posts are great, but this information would be even better presented in a structured format and fleshed out a bit more. An ebook would be a great way to provide comprehensive content that is awkward to assemble from various posts! The DeveloperDays VM has been a great resource — all we need now is your new ebook to go alongside.
Thanks again for your posts,
David
Feb 18, 2013 @ 17:55:40
Hi Jeff I attended one your webinars on the Data Modeler a few weeks ago and I asked you a question during on how to compare a single subview to the database. At the time you thought there was a way but couldn’t find it during the session. I was wondering if you know how to do it. This would be very helpful as we break down our subviews into complete separate entities.
Thanks.
Feb 19, 2013 @ 09:27:42
Yes. Open the subview diagram. In the diagram, right click to launch the Context menu. At the bottom of that popup, you’ll see ‘Synchronize SubView with Data Dictionary’ and vice versa.
Apr 26, 2013 @ 13:44:27
Hi Jeff,
Ive just started using this tool, and I am finding the synchronise a bit confusing.
I basically want to compare “model” as master to any database and produce “alters”.
Am I able to “untie” the model from the source database I got it from?
I dont think I really understand the difference between “source connection” and “redirect connection” to be honest.
Basically I dont get the alter scripts I am hoping for all of the time, and was wondering if the soruce connection shoudl be the one I want to sync to, or not.
Apr 26, 2013 @ 14:24:42
Dan, does this post help sort things any? If not, then I’ll be happy to get into the details with you.
Once you decide what ‘version’ of the design is the truth – your model or the database – then doing the synch and generating Alters is pretty straightforward.
Apr 26, 2013 @ 18:17:23
Jeff, It was that post that lead to my question
Basically, I created the model from a dev database, and now want to compare to a different database and bring that in-line with the model.
The “source” connection always says the original dev database. Is that correct? I cant seem to change that.
I would think the model should be “database-less” and only need a connect string to compare the model to a dictionary.
Thanks
Apr 26, 2013 @ 19:45:02
So when you do a RE, it ties the model TO that database you imported the objects from. You can compare it to another database, but you have to either:
On Monday I’ll try to put some notes together so this is more clear.
Apr 27, 2013 @ 06:53:32
Thanks Jeff,
So basically you are saying that the model cannot be “untied” from a database if you want to use it to produce incremental model changes for deployment to live?
It would be handy to know which XML file to edit.
Thanks
Apr 29, 2013 @ 09:13:35
Actually what I’m saying is you need to upgrade to v3.3 – because in that version when you do a synch, you’re able to redirect the compare to the database you want to use
I’ll write up a quick post on that new feature today.
Apr 29, 2013 @ 18:01:55
Ok Thanks Jeff.
I will do some more testing on 3.3 and see how the synch features compare.