Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Designing class hierarchies for Oracle

Don Burleson


The IS-A relationship (pronounced "is a") is a data relationship that indicates a type/subtype data relationship. While traditional Entity/Relation modeling deals only with single entities, the IS-A approach recognizes that many types or classes of an individual entity can exist. In fact, the IS-A relationship is the foundation of object-oriented programming, which allows the designer to create hierarchies of related classes and then use inheritance and polymorphism to control which data items will participate in the low-level objects.


After establishing a class hierarchy with the E/R model, the object-oriented principle of generalization is used to identify the class hierarchy and the level of abstraction associated with each class. Generalization implies a successive refinement of a class, allowing the super-classes of objects to inherit data attributes and behaviors that apply to the lower levels of a class.

Generalization establishes taxonomy hierarchies. Taxonomy hierarchies organize classes according to their characteristics in increasing levels of detail. These hierarchies begin at a very general level and then proceed to a specific level, with each sublevel having its own unique data attributes and behaviors.

In Figure 2.12, the IS-A relationship is used to create a hierarchy within the EMPLOYEE class.  The base class for an employee has the basic data items such as name, address and phone number.  However, there are sub-classes of employees, executives and hourly employees, each with their own special data items and methods.

Figure 2.12 - An Entity/Relation model with added IS-A relationships.

Let's look at another example. Consider the application of the IS-A relationship for a vehicle dealership, as shown in Figure 2.13. As you can see, the highest level in the hierarchy is VEHICLE. Beneath the vehicle class, you might find car and boat subclasses. Within the car class, the classes could be further partitioned into classes for TRUCK, VAN, and SEDAN. The VEHICLE class would contain the data items unique to vehicles, including the vehicle ID and the year of manufacture. The CAR class, because it IS-A VEHICLE, would inherit the data items of the VEHICLE class. The CAR class might contain data items such as the number of axles and the gross weight of the vehicle. Because the VAN class IS-A CAR, which in turn IS-A VEHICLE, objects of the VAN class inherit all data items and behaviors relating to the CAR and VEHICLE classes.

Figure 2.13 - A class hierarchy for a vehicle rental company.

These types of IS-A relationships, while valid from a data modeling viewpoint, do not have a simple implementation in Oracle. Because Oracle can represent hierarchies in a relational database in two ways.

Oracle has the ability to "create type" within type and model this relationship directly, but it is not a popular approach because the structures are hard to change.

The first technique (incorrect IMHO) is to create sub-tables for car, boat, sedan, and so on. This encapsulates the data items within their respective tables, but it also creates the complication of doing unnecessary joins when retrieving a high-level item in the hierarchy. For example, the following SQL would be required to retrieve all the data items for a luxury sedan:

   vehicle.key = car.key
   car.key = sedan.key
   sedan.key = luxury.key;

It is wrong, always wrong, to add fake one-to-many relationships for different types of the same thing.  It is poor modeling and imposes a lost-data issue, clogs the schema with tables and makes the SQL too cumbersome.

The second approach is to create a mega-table, with each data item represented as a column (regardless of whether it is needed by the individual row). A TYPE column could identify whether a row represents a car, van, or sailboat. In addition, the application must have intelligence to access only those columns applicable to a row. For example, the sail-size column would have meaning for a sailboat row, but would be irrelevant to a sedan row.

The IS-A relationship is best suited to the object-oriented data model, where each level in the hierarchy has associated data items and methods, and inheritance and polymorphism can be used to complete the picture. It is important to note that not all classes within a generalization hierarchy will be associated with objects. These non-instantiated classes only serve the purpose of passing data definitions to the lower-level classes.

The object-oriented paradigm allows for abstraction, which means that a class can exist only for the purpose of passing inherited data and behaviors to the lower-level entities. The classes VEHICLE and CAR probably would not have any concrete objects, while objects within the VAN class would inherit from the abstract VEHICLE and CAR classes.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.