Re: object to relational mapping advice

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Tue, 11 May 2004 01:18:07 GMT
Message-ID: <jRVnc.31520$TT.11014@news-server.bigpond.net.au>

"sp" <splash78kas_at_yahoo.com> wrote in message news:16dfd1a8.0405101157.6d9b2e93_at_posting.google.com...
> I needed some advice on database design. I have my object model ready
> and wante d to design a corresponding relational model.
>
> I have 2 classes paarent & child
>
> Class A
> {
> int parent_id
> hashmap children // key = date_id , value = child object
> }
>
> Class B
> {
> int child_id
> int parent_id
> int date_id
> }
>
> There is a one-to-many mapping between parent and child classes.I am
> not sure of the constraints I need to put betn the correpsonding
> Tables A & B
>
> TIA
>
> splash

splash,

I have chosen a strictly relational implementation.

The constraints I have implemented are:
(1) Each instance of Class A has a unique identifier (parent_id) which is not null.
(2) Each instance of Class B has a unique identifier (date_id) which is not null. This is deduced from your definition of the hashmap object, children. (3) Each instance of Class B belongs to exactly one instance of Class A.

A suggestion for the physical design for Class A would be: CREATE TABLE class_a_physical

   (

      parent_id
         NUMBER
         CONSTRAINT class_a_pk
            PRIMARY KEY

   )
;

A suggestion for the physical design for Class B would be: CREATE TABLE class_b_physical

   (

      child_id
         NUMBER,
      parent_id
         NUMBER
         CONSTRAINT class_b_parent_id_nn
            NOT NULL,
      date_id
         NUMBER
         CONSTRAINT class_b_pk
            PRIMARY KEY

    )
;

A suggestion for the physical design of the relationship between instances of Class A and Class B would be:
ALTER TABLE class_b_physical

   ADD CONSTRAINT class_b_parent_id_fk

      FOREIGN KEY ( parent_id )
      REFERENCES class_a_physical ( parent_id )

;

If I were keen on implementing the Object-Relational features of Oracle, I would follow the advice given by Thomas Kyte in Chapter 20 of "Expert One-on-One Oracle" (A-Press:2003). In particular, I would follow the example given on p.898:

In this type, I have removed the reference to the parent_id attribute because it belongs to Class A.
CREATE OR REPLACE TYPE class_b
AS OBJECT (
   child_id

      NUMBER,
   date_id

      NUMBER
   )
;

A hash map is simply an in-memory representation of a table with a primary key. (See pp. 516 to 518 of "Java in a Nutshell" by David Flanagan (3rd Ed.) (O'Reilly:1999).
CREATE OR REPLACE TYPE class_b_hashmap
AS TABLE OF class_b
;

The type representation would be as follows: CREATE OR REPLACE TYPE class_a
AS OBJECT (
   parent_id

      NUMBER,
   children

      class_b_hashmap
   )
;

And the collection of all instances of Class A would be represented as: CREATE OR REPLACE VIEW class_a_collection

   OF class_a
   WITH OBJECT IDENTIFIER( parent_id )
   AS
   SELECT

         parent_id,
         CAST(
            MULTISET(
               SELECT
                     date_id,
                     child_id
                  FROM
                     class_b_physical b
                  WHERE
                     a.parent_id = b.parent_id
            ) AS class_b_hashmap
         ) AS children
      FROM
         class_a_physical a

;

Now I would have an object-relational view over my relational tables. If you want to continue down this path, read Thomas Kyte's book.

Douglas Hawthorne Received on Mon May 10 2004 - 20:18:07 CDT