The Data Warrior

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

//platform.twitter.com/widgets.js

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:

1. Create the parent or super type entity
2. Create the potential sub type entity
3. Set the Super Type Entity property on the candidate Sub Type Entity to associate it with the parent.

Set Super Type

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:

Displaying Sub Types in Barker Notation with Box-in-Box

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:

Displaying Sub Types in Bachman Notation

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!

Happy Modeling

Kent

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.