9.7.1 Example of Nested Records

In the following example I declare a record type for all the elements of a phone number (phone_rectype), and then declare a record type which collects all the phone numbers for a person together in a single structure (contact_set_rectype).

DECLARE    TYPE phone_rectype IS RECORD       (intl_prefix   VARCHAR2(2),        area_code     VARCHAR2(3),        exchange      VARCHAR2(3),        phn_number    VARCHAR2(4),        extension     VARCHAR2(4)       );    TYPE contact_set_rectype IS RECORD       (day_phone#    phone_rectype, /* Nested record */        eve_phone#    phone_rectype, /* Nested record */        fax_phone#    phone_rectype, /* Nested record */        cell_phone#   phone_rectype  /* Nested record */       );    auth_rep_info_rec contact_set_rectype; BEGIN

Without nested records, my contact_set_rectype would look like this:

DECLARE    TYPE phone_rectype IS RECORD       (day_intl_prefix VARCHAR2(2), day_area_code VARCHAR2(3),        day_exchange VARCHAR2(3), day_phn_number VARCHAR2(4),        day_extension VARCHAR2(4),         eve_intl_prefix VARCHAR2(2), eve_area_code VARCHAR2(3),        eve_exchange VARCHAR2(3), eve_phn_number VARCHAR2(4),        eve_extension VARCHAR2(4),         fax_intl_prefix VARCHAR2(2), fax_area_code VARCHAR2(3),        fax_exchange VARCHAR2(3), fax_phn_number VARCHAR2(4),        fax_extension VARCHAR2(4),         cell_intl_prefix VARCHAR2(2), cell_area_code VARCHAR2(3),        cell_exchange VARCHAR2(3), cell_phn_number VARCHAR2(4),        cell_extension VARCHAR2(4)       );    auth_rep_info_rec contact_set_rectype; BEGIN

The structure of this unnested record is very cumbersome for two reasons: it is difficult to read and it is hard to maintain.

• Unnested records are difficult to read. Even though I used prefixes for each of the phone numbers so that I know which parts belong to which number, it still takes a conscious and belabored effort to sort through the fields and make the connections. There are just too many fields and too much repetition for the mind to focus and take it all in. With the nested record, I shift the complexity of each individual phone number down a level into its own record type. This way the contact_set_rectype is self-explanatory: it's made up of four different phone numbers.

• Unnested records are hard to maintain. Today, my phone number might have only five components. Why not just lay them all out there in the record? Because down the road my manager tells me that a phone number needs to also store a PIN (Personal Identification Number) for calling cards. As a result, I have denormalized my data structure within the unnested record (with all the same downside denormalization has in the RDBMS). I now need to add four different fields to this record, all with the same datatypes and the right naming conventions. The more coding I do, the greater the chance that I will introduce errors into that code.

  With nested records, on the other hand, I could simply add a single field to the phone_rectype record type. I do not have to change the contact_set_rectype record. The contact_set_rectype automatically has that extra field available to it in each of its nested records. The resulting nested record structure allows me to easily keep all phone numbers in one place and access them instantly.

9.7.2 Dot Notation with Nested Records

Although I still use the dot notation to refer to a field with nested records, now I might have to refer to a field which is nested several layers deep inside the structure. To do this I must include an extra dot for each nested record structure. Suppose that I have a record with two levels of nested records:

DECLARE    TYPE level3_rectype IS RECORD (textline VARCHAR2(2000));    TYPE level2_rectype IS RECORD (paragraph level3_rectype);    TYPE level1_rectype IS RECORD (story level2_rectype);    level1_rec level1_rectype;

Then the fully qualified name for the textline variable is:

level1_rec.story.paragraph.textline

Assignments with nested records follow the same rules as those for "flat" records. The following statement, for example, sets the fax phone number's area code to the home phone number's area code:

auth_rep_info_rec.fax_phone#.area_code :=    auth_rep_info_rec.home_phone#.area_code;

9.7.3 Aggregate Assignments of Nested Records

I can also perform an aggregate assignment of one nested record to another. To set the fax number equal to the home phone number, I simply execute the following:

auth_rep_info_rec.fax_phone# := auth_rep_info_rec.home_phone#;

This is possible because both the fax_phone# and the home_phone# records have the same type, namely the phone_rectype. You can even perform this kind of assignment between records which are nested within different enclosing records, as the following example illustrates:

DECLARE    /* Using the phone_rectype defined earlier in chapter... */    TYPE contact_set_rectype IS RECORD       (day_phone# phone_rectype,        eve_phone# phone_rectype,        fax_phone# phone_rectype,        cell_phone# phone_rectype       );    TYPE emerg_contacts_rectype IS RECORD       (emerg1_phone# phone_rectype,        emerg2_phone# phone_rectype       );    auth_rep_info_rec contact_set_rectype;    in_an_emergency_rec emerg_contacts_rectype; BEGIN    in_an_emergency_rec.emerg1_phone# :=       auth_rep_info_rec.day_phone#; END;

The emerg1_phone# and the day_phone# have the same record type: phone_rectype. So even though the enclosing records auth_rep_info_rec and in_an_emergency_rec have different record types, the nested record assignment works. A direct aggregate assignment between the two enclosing records as shown below would fail to compile:

in_an_emergency_rec := auth_rep_info_rec; -- This would fail!

9.7.4 Denormalizing Program Data with Nested Records

At the beginning of this chapter I mentioned that nested records let you carry over the normalized data structures of your database to the memory structures in your programs. Using nested records I was able to avoid hardcoding the internal structure of a phone number multiple times in my contact set record. Oddly enough, you can also use nested records to take your normalized data from the database and denormalize that data inside your program so it is easier to manipulate.

Well-designed relational data structures are usually normalized, which means (roughly) that you eliminate redundant information and repeating groups. Consider a company entity. It might have four different kinds of addresses: receiving, shipping, accounts payable, and customer relations. Each address is made up of seven attributes or columns: address lines one through four, city, state, and zip code. Rather than burden the company table with 28 (7 � 4) columns for all four addresses, you would create a separate table of addresses with a foreign key back to the company table. By creating a separate table for company addresses, you also can easily add another kind of address for a company at a later date.