The following are a few of my favorite database design related posts and some tips and tricks for getting more comfortable with Oracle SQL Developer Data Modeler.
If you’re looking for an exhaustive list of ALL of my data modeling themed posts, that would be here. There’s about 50 and counting. If you can’t find what you’re looking for, just drop me a note! I’m always happy to write something up by request.
101: Start Here
- SQL Developer Data Modeler Primer: Designs, Models, & Diagrams – Opening, Saving, and Navigating your designs
- Step-by-step how to: Import an existing database and its objects to a relational model
- Accessing the Modeler inside of Oracle SQL Developer
- Entity Relationship Diagrams (ERD) – Logical vs Physical Models
- Search & Replace
- Dimensional Modeling – A Slidedeck overview of the modeling approach in SQL Developer
- 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
- How to setup your favorite data types for faster column and attribute design
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.
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.
Comparing Models, Source Control, & Advanced Stuff
This isn’t necessarily ‘advanced,’ but it does assume you already know the basics.
- Searching Your Models – Maybe the most powerful feature – SEARCH!
- Synchronizing Your Model With the Database (and vice versa) – how to compare and merge changes from model to database and back again
- DIFFs & Alter Scripts – I read this post at least every other week, you’ll want to bookmark this one
- Using the Modeler to Compare Database Schemas
- The Reporting Repository – how to query your model, and make your data dictionary reports available to SQL Developer users
Design Philosophy and Rants
- Rant – Mixed Case Object Names
- Response to Normalization Myths That Make Me Crazy – warning, this is a rant too 🙂
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.