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 a Hierarchy of Data Types 

Oracle Database Tips by Donald Burleson

Displaying data type usage

In object-oriented data models, the ability to "nest", or embed data definition within other definitions has tremendous benefits for the database administrators who must manage the data definitions.  In pre-relational databases it was possible to see "where-used" information for any data item in the enterprise. 

A bill-of-material relationship existed in the data dictionary to relate both the sub-data items of a data type as well as the higher-level components where the data item is used.  Also, since each any every method is now stored in the database, it is possible to see where a data item is referenced.  For some relational databases that still allow external programs, the database pre-compiler will record all external programs that reference the data type.  Hence, user-defined data types with a robust dictionary will allow the DBA to see every place where a data type is used so that they can identify every place where a data definition needs to be changed.

For example, consider the following data dictionary report for our full_address datatype:

Full_address data type

Consists of:

(street_address, city_address, zip_code)

Part of:

(customer_address, employee_address, vendor_address)

Used in methods:


Used in C Programs:


In this example listing from a data dictionary, we can see that the data dictionary has provided everything that we need to know about the full_address data type.  This can be an extremely useful feature for database objects, especially when data types change their definition.  Consider how simple it would be to change a zip code from 5 digits to 9 digits, or change a year field from 2 digits to 4 digits.  Every place where the data type exists can be easily identified.

Designing a hierarchy of data types

One of the challenges of abstract data typing is the definition of a hierarchy of related data types.  In order to get the benefit of re-usable data structures, it is necessary to define a hierarchy of data types. 

As we know by intuition, data types naturally form a recursive many-to-many relationship with other data types, such that a data type may be composed of data types, while at the same time being a part of a larger data type. (Figure 5.2)

Figure 5.2 A Model for a recursive data type hierarchy

This relationship has be expressed in  a set occurrence diagram, where we can see the linkages between the has_parts and the is_a_part relationship. (Figure 5.3)  Here wee see that the full_address data types has the components, street_address, city_address and zip_code, while at the same time, full_address is cast as the customer_address and the employee_address datatypes, participating in the customer_stuff and the employee_stuff data types.

Figure 5.3 A set occurrence diagram for data types

In the relational model, the relationship between data types is expressed by creating an intersection record to establish the many-to-many relationship. (Figure 5.4)

Figure 5.4  A tabular representation of data types

Here wee see that the junction table has only two columns, HAS-PARTS and IS-A-PART.  By issuing a relational JOIN operation, we can build the data structures for any data item.  For example, the following SQL will display all of the components within the full_address datatype.  In this example, we join using the has_parts relationship to see the sub-components of the full_address:

        is_a_part = 'full_address';


Conversely, we can also display where a data type appears as a sub-part in a larger data type.  In the following example, we can display where the full_address participates:

        has_parts = 'full_address';


Now that we understand the internal representation of user-defined data types within the databases dictionary, let's move on to take a look at how they are used and manipulated within database methods.




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.