The Data Warrior

Changing the world, one data model at a time. How can I help you?

Better Data Modeling: Showing Super & Sub Types in #SQLDevModeler (SDDM)

So this started with a not so innocent tweet from Jeff Smith:


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

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 Subtypes in Barker Notation with Box-in-Box

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 Subtypes in Bachman Notation

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


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.

Single Post Navigation

2 thoughts on “Better Data Modeling: Showing Super & Sub Types in #SQLDevModeler (SDDM)

  1. 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

  2. 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.

Leave a Reply to Kent Graziano Cancel reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: