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

"sp" <splash78kas@yahoo.com> wrote in message
news:16dfd1a8.0405101157.6d9b2e93@posting.google.c om...
> 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