Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Nested tables and joining them to their containing rows

with one comment

Nested tables let you implement ID dependent tables. ID dependent tables are solely dependent on another table. Data modelers choose between ID- and non-ID dependent tables. The non-ID dependent table is the most common. It describes implementing ordinary tables. ID dependent tables require you to nest a table in another. Most designers opt for non-ID dependent relationships because they avoid nesting tables.

Some teams denormalize the table by creating multiple columns. A classic example is an address table. Since an address can have multiple street address values because of building, suite, and apartment numbers. You’ve probably seen them implemented as STREET_ADDRESS1, STREET_ADDRESS2, et cetera. The problem with this design is that a customer may require third street address column. An alternative is to implement a comma delimited list (technically, violating first normal form).

The following implements an ID dependent model or nested table:

Create a contact table and seed it with two rows:

CREATE TABLE contact_book
( contact_id NUMBER
, first_name VARCHAR2(20)
, middle_name VARCHAR2(20)
, last_name VARCHAR2(20));

INSERT INTO contact_book VALUES ( 1,'Brad',NULL,'Scott');
INSERT INTO contact_book VALUES ( 2,'Jeff',NULL,'Kenney');

Create a scalar collection type, implement the collection in an address table, and seed it with two rows:

CREATE TYPE street_list IS TABLE OF VARCHAR2(30);
/

CREATE TABLE address_book
( address_book_id NUMBER
, street_address STREET_LIST
, city VARCHAR2(30)
, state VARCHAR2(2)
, postal_code VARCHAR2(10))
NESTED TABLE street_address STORE AS street_table;

INSERT INTO address_book VALUES
( 1, street_list('203 North La Salle','Suite 1400'), 'Chicago', 'IL', '60601');

INSERT INTO address_book VALUES
( 2, street_list('203 North La Salle','Suite 1800'), 'Chicago', 'IL', '60601');

Create a function to convert the varying number of rows into a single string with line returns (that’s the CHR(10) call):

CREATE OR REPLACE FUNCTION format_address(set_in STREET_LIST) RETURN VARCHAR2 IS
  retval VARCHAR2(2000);
  current NUMBER;
BEGIN
  IF set_in IS NOT NULL AND set_in IS NOT EMPTY THEN
    current := set_in.FIRST;
    WHILE current IS NOT NULL LOOP
      retval := retval || set_in(current)||CHR(10);
      current := set_in.next(current);
    END LOOP;
  END IF;
  RETURN retval;
END format_address;
/

Query the collection with the FORMAT_ADDRESS function, which resolves the one to many join between the scalar columns and array returned by the TABLE function:

SELECT   cb.first_name
||       DECODE(cb.middle_name,NULL,' ',cb.middle_name||' ')
||       cb.last_name||CHR(10)
||       format_address(ab.street_address)
||       ab.city||', '
||       ab.state||' '
||       ab.postal_code||CHR(10) mail_label
FROM     contact_book cb JOIN address_book ab
ON cb.contact_book_id = ab.contact_book_id
WHERE ab.street_address IS NOT NULL
OR ab.street_address IS NOT EMPTY;

This provides you with a formatted address list for envelops:

Brad Scott
203 North La Salle
Suite 1400
Chicago, IL 60601

Jeff Kenney
203 North La Salle
Suite 1800
Chicago, IL 60601

Written by maclochlainn

May 25, 2008 at 10:59 pm

One Response

Subscribe to comments with RSS.

  1. […] into a scalar value. It’s an easy way to leverage a nested table. You can find the full code here. You might think that this is a neat opportunity to use a result cache function. I did, but the […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: