Benefits of User-Defined Data Types Oracle Database Tips by Donald Burleson

It is interesting to note that the ability to represent user-defined data types was commonly used within pre-relational database, and was lost when the relational model was introduced.  In pre-relational databases, there were only a small number of allowable data types (numeric and character), but these databases allowed for the atomic values to be grouped into larger units.  These larger units could then be easily moved around within the database.  For example, a full_address construct could be defined and copied into numerous record definitions, where it could be manipulated as if it were a single unit.

There are several reasons why ADT's are useful within an object database:

1. Encapsulation - Because each user-defined data type exist as a complete entity, including the data definitions, default values, and value constraints, this entity insures uniformity and consistency.  Once defined, a user-defined data type may participate in many other user-defined data types, such that the same logical data type, always has the same definition, default values and value constraints, regardless of where it appears in the database.

2. Reusability - As a hierarchy of common data structures are assembled, these can be re-used within many definitions, saving coding time and insuring uniformity.

3. Flexibility - The ability to create real-world data representations of data allows the database object designer to model the real world as it exists.

As you can see there are many compelling reasons to have user-defined data typing, provided that the data types are properly analyzed and incorporated into the database object model.  Let's take a look at some of the implementation issues that relate to the object model and data typing.

Nesting of user-defined data types

Now lets take this concept one step further and consider how  user-defined data types can be nested within other data types.  A basic example, would be to create a data type that would encapsulate  all of the data in a table:

 CREATE TYPE    customer_stuff (
    full_name           customer_name,
    home_address     customer_address
    business_address customer_address);

 With the customer_stuff type defined, table definition becomes simple:

 CREATE TABLE CUSTOMER (customer_data           customer_stuff);

Using this type of user-defined data type we are essentially duplicating the object-oriented concept of encapsulation.  That is, we are placing groups of related data types into a container that is completely self-contained and has the full authority of the innate relational data types such as int and char.

Select customer_stuff.customer_name.zip_code
from customer
where customer_stuff.customer_name.zip_code like '144%';

Manipulating ADT's

In many object databases it is possible to directly retrieve and pass an abstract data type.  While this is a matter of convenience for most programmers, there are some very useful applications of this technique, especially for the object/relational databases.  In object/relational databases, ADT's can be directly retrieved and used inside other tables.  In the following Oracle example, a new table called good_customer is created by retrieving the entire ADT from the customer table.

create table  good_customer as
(select value(a) from customer a where type = 'good')

In this example, the good_customer table will inherit the same ADT as the customer table.

Another feature of abstract data typing is the simplification of the transfer of data structures between the database and the front-ends.  In a traditional relational database, each column has to be moved, one at a time, from the database into the map field for the display screen.  In the following example, we see the C syntax to move data onto a display screen:

strcpy(db_cust_first_name,screen_cust_first_name);
strcpy(db_cust_last_name,screen_cust_last_name);
strcpy(db_cust_MI,screen_cust_MI);

strcpy(db_cust_street_address,screen_cust_street_address);
strcpy(db_cust_city,screen_cust_city);
strcpy(db_cust_zip_code,screen_cust_zip_code);

When using an ADT, a single statement can be used to transfer the data from the database onto the display screen:

strcpy(db_customer_struct, screen_customer_struct);

Summary

Now that we have a general understanding of the function and operation of abstract data types within the database object model, we can now move to on to more advanced topics.  In the following chapters we will introduce pointers to objects, the creation of aggregate objects, and the use of inheritance within the database object model.  It is the synergy of all of these features that makes the object-oriented and object/relational database a powerful new addition to the arsenal of systems development tools.

 

---