When we create a super-and-sub type (a.k.a. Inheritance) entity relationship in a logical data model, it’s very clear what the business requirements being communicated are – that different subsets of attributes comprise the “cumulative entity” being modeled depending upon the business context.

So the Toad Data Modeler 3.0 logical diagram shown below relates that Entity1 always has the parent attributes of E1_Attribute1-4, and then depending upon the business context it will have either child attributes E2_Attributes 1-4 or E3_Attributes1-4. And we know that is exclusively true (i.e. only one or the other, and not both) due to the “X” in the super-and-sub type indicator.

So when it comes time to physically implement this logical data model for usage within a relational database, what are our choices? Well there are at least three alternatives, which I’ll cover over the next couple blogs separately – so that we only have to digest one way at a time.

Look below at Toad Data Modeler 3.0’s “Inheritance” definition dialog.

We’re going to choose the N-1 options, meaning that super and sub-types are implement by fully inheriting the parent into both sub-types, thus yielding the physical model shown here:

This is a very common choice for implementation. It keeps the coding pretty easy for the application developers. Plus it’s a pretty straight-forward and fairly easy to comprehend translation concept. However even though this is a very common implementation choice, nonetheless most data modeling tools will generate incorrect or incomplete DDL for this scenario. Most tools will properly generate the two tables with their proper primary and foreign keys all properly inherited and implemented. But they most often forget that to the business this is really just one business entity. And so having two tables with primary keys does NOT prevent one from loading the same key value to each table (which to the business is not allowed). But look at the trigger code Toad Data Modeler generates for this implementation – it specifically checks to verify and guarantee that the primary keys are unique across tables! That’s a very easy item to miss during physical modeling and the actual database construction. You usually don’t find out until the data is messed up!

/*
Created: 11/14/2007
Modified: 11/14/2007
Model: Physical Model
Database: Oracle 10g
*/

-- Create tables section ---------------------------------------------------------------------------

-- Table Entity2
CREATE TABLE "Entity2"(
  "E2_Attribute1" Integer NOT NULL,
  "E2_Attribute2" Integer NOT NULL,
  "E2_Attribute3" Integer,
  "E2_Attribute4" Integer,
  "E1_Attribute1" Integer NOT NULL,
  "E1_Attribute2" Integer,
  "E1_Attribute3" Integer,
  "E1_Attribute4" Integer
)
/

-- Add keys for table Entity2
ALTER TABLE "Entity2" ADD CONSTRAINT "Unique Identifier1" PRIMARY KEY ("E1_Attribute1")
/

-- Create triggers for table Entity2
CREATE TRIGGER "InheritanceEntity2"
  BEFORE INSERT
  ON "Entity2"
  for each row
declare numrows integer
begin
  select count(*) into numrows from Entity3  where E1_Attribute1 = :NEW.E1_Attribute1
  if (numrows > 0) then
    RAISE_APPLICATION_ERROR(-20003,'Exlusive Inheritance violation with entity Entity3');
  end if;
end;
/ 

-- Table Entity3
CREATE TABLE "Entity3"(
  "E3_Attribute1" Integer NOT NULL,
  "E3_Attribute2" Integer NOT NULL,
  "E3_Attribute3" Integer,
  "E3_Attribute4" Integer,
  "E1_Attribute1" Integer NOT NULL,
  "E1_Attribute2" Integer,
  "E1_Attribute3" Integer,
  "E1_Attribute4" Integer
)
/

-- Add keys for table Entity3
ALTER TABLE "Entity3" ADD CONSTRAINT "Unique Identifier1" PRIMARY KEY ("E1_Attribute1")
/

-- Create triggers for table Entity3
CREATE TRIGGER "InheritanceEntity3"
  BEFORE INSERT
  ON "Entity3"
  for each row
declare numrows integer
begin
  select count(*) into numrows from Entity2  where E1_Attribute1 = :NEW.E1_Attribute1
  if (numrows > 0) then
    RAISE_APPLICATION_ERROR(-20003,'Exlusive Inheritance violation with entity Entity2');
  end if;
end;
/