Implications of Pointers for Database Design

Oracle Database Tips by Donald Burleson

So, we can now see that the ability to support abstract data types provides us with a lot of very powerful features, including:

1. The ability to store repeating groups within a cell of a table.

2. The ability to "nest" tables within tables.

3. The ability to provide pointer-based navigation for relational data.

4. The ability to represent aggregate objects.

Consider the ramifications for item pointer-based navigation to relational data. If we are allowed to bypass SQL, imagine the possibilities! With Oracle we could navigate a data model without having the overhead of joining tables together. More important, we now have the ability to represent "complex" objects. This means that we can pre-create objects that are composed of sub-objects without having to build-them each time that we want to see them. Since the aggregate objects have an independent existence, we can attach methods to these objects. In addition, we can also save database overhead by pre-creating complex database objects, having them instantly available to the database. To illustrate this concept, let's create an order_form for a customer. When a customer places an order, they telephone the order department and indicate the following:

Their name, address and payment method
Their choices of items, and the quantities for each item

In a traditional relational database we would populate three tables to represent this relationship. They would be the CUSTOMER, ORDER, ORDER_LINE, and ITEM tables. Creating the order form would involve the following SQL:

SELECT
   customer.first_name,
   customer.last_name,
   customer.street_address,
   customer.city_address,
   customer.zip_code,
   customer.payment_method,
   order.order_date,
   item.item_description,
   order_line.quantity_ordered,
   item.item_price,
   order_line.quantity_ordered * item.item_price
FROM
   customer,
   order,
   order_line,
   item
WHERE
   order.customer_ID = customer.customer_ID
   AND
   order.order_ID = order_line.order_ID
   AND
   order_line.item_ID = item.item_ID
   AND
   customer.customer_ID = 123;

As we can clearly see, the re-creation of the data for an order form would involve a four-way table join. Using the object-relational features of abstract data types ADTs), we could define a new object called order_form that would contain all of the pointers to the various components that comprise the order form. Note that it is never necessary to re-build an order_form object unless items are deleted or added to the order_form. For example, when the quantity_ordered column in the order_line table is changed, the order_form object will automatically pick-up this new value when the order_line row is de-referenced.

Here's how it might work. We first need to define a pointer to return the row that corresponds to the customer who has placed the order. Let's assume that we have already created an ADT for the entire customer row and defined the customer table as follows:

CREATE TABLE customer (customer_data customer_adt);

We can now create a customer_ref type to hold the pointer to the customer:

CREATE TYPE customer_ref
AS TABLE OF customer_adt;

Since we will only be retrieving one order row, we can do the same thing for the order table row.

CREATE TYPE order_ref
AS TABLE OF order_adt;

Therefore, the first component of the order_form object will be the reference to the customer and order rows:

CREATE TABLE order_form (
customer customer_ref,
order order_ref);

OK, now we have the customer and order data. Now we need to establish the pointers to represent the many-to-many relationship between the ORDER and ITEM tables. Let's start by defining a repeating group of all of the order_lines for each order:

CREATE TYPE item_list
AS TABLE OF order_line;

Now, that we have defined the item_list, let's take a stab at defining a order_form:

CREATE TABLE order_form (
   customer     customer_ref,
   order        order_ref,
   lines        item_list);

Now, we will need to establish pointers to all of the items that are referenced in the order_line table. But how can we do this? We do not know the item_ID numbers that participate in the order until we have retrieved the order_line rows. In this case we need to establish "owner" pointers inside each order_line row so that we will be able to de-reference the item table. Let's assume that the line_item table has been defined as shown below to include a reference pointer to the item table:

/* example of an owner pointer */
CREATE TYPE item_ref
AS TABLE OF item;

CREATE TABLE line_item (
   order_ID         integer,
   item_ID          integer,
   item_pointer     item_ref,
   quantity_ordered integer);

So, let's see how we could display all of the data for the order_form object:

CREATE TABLE order_form (
   customer     customer_ref,
   order            order_ref,
   lines             item_list);

SELECT
   DEREF(customer.customer_last_name),
   DEREF . . .
   DEREF(order.order_date),
   DEREF(item_list.item_name), /* if item is a foreign key in line item */
   DEREF(order.quantity_ordered)
   DEREF(DEREF(order_line.item_pointer)) /* this returns item data */

Note: Pointer twizzling

The concept of storing a pointer to an array of pointers (**char in C++) has another remarkable feature. Since we have a pseudo-table with all of the pointers for rows in a table, we can change the sequence of these row references without ever accessing the rows themselves. In object-oriented parlance this concept is known as pointer "twizzling".

NOTE: a row can only be referenced if it has been defined with an OID. To do this the table must be defined as:

CREATE TABLE customer OF customer_stuff;

as opposed to:

CREATE TABLE customer
(
customer_data customer_stuff
);

These are the SAME table definitions; the only difference is that the first has a column called OID$ that contains a unique object-ID for each row.