Better Data Modeling: Showing Super & Sub Types in #SQLDevModeler (SDDM)
So this started with a not so innocent tweet from Jeff Smith:
@GuillaumeSL i’m betting @KentGraziano could answer your question right away
— Jeff Smith (@thatjeffsmith) September 14, 2015
Well, I sort of answered at least part of the question (eventually), but along the way the topic of using super types and sub types came up.
Note: If you don’t know what a sub type is, you probably do not do conceptual or logical modeling, so you can stop reading now. Or google it. 🙂
So, in Oracle SQL Developer Data Modeler (SDDM for short, or #SQLDevModeler) you can specify sub type entity relationships in the Logical Model (not relational or physical).
Unless I missed an enhancement (??), you have to do this by:
- Create the parent or super type entity
- Create the potential sub type entity
- Set the Super Type Entity property on the candidate Sub Type Entity to associate it with the parent.
Note in the screen that Super Type is set to Employees.
(It sure would be nice if we could just drag and drop to do this, or better just create a new sub type entity “inside” an existing entity)
Once you have set the property, then it will appear in the diagram in one of several ways, depending on the diagram notation you pick. The default is Barker Notation with Box-in-Box Presentation turned on. That looks like this:
If you turn Box-in-Box off (right mouse on white space in the diagram then go to Notation), you can drag the sub types outside the super type display and a red line will be displayed to connect them together.
If you switch to Bachman Notation with Box-in-Box off, it looks like this:
Notice the little red lines with arrows pointing into the Employees entity? That is the sub type relationship.
So depending on your personal experience and style, you have a few options to choose from when modeling these type of relationships.
How this converts to a database table design is a whole other (and longer) topic. If you really need to know now, go buy Heli’s SQL Dev Modeler Book and read the section on Inheritance.
Or you could sign up for my online Intro to SDDM (use coupon code GRAZIANO10S for 20% off).
Better Still – do BOTH!
The Data Warrior
P.S. I will be speaking at ECO15 in Raleigh, NC next week. If you are attending be sure to say hi.
We used to do this in Oracle Designer, and it would give implementation choices:
single table with a “Type” column and all subtype columns nullable
tables for supertype and each subtype with one to one relationships super to sub.
tables for each subtype with all supertype columns in each table
Yup! All those options are there in SDDM too. Heli’s book (and my training class) go into all the details on those options and how to make them work. Just like Designer there are lots of choices to make and each one gives a slightly different physical model.
BTW – SDDM does arc relationships too just like Designer did.