relations in oracle objects?

Question

how to make relations 1 to n and n to n in oracle objects?

Answer 1

Unless 'Oracle Objects' is a product of some sort (capital letters help distinguish ordinary words from product names), then you do it the same way as you do in any other DBMS.

For a 1:n relationship:

CREATE TABLE Master
(
    PK_Column    <sometype> NOT NULL PRIMARY KEY,
    ...
);
CREATE TABLE Detail
(
    FK_Column    <sometype> NOT NULL REFERENCES Master,
    OtherColumn  <anothertype> NOT NULL,
    PRIMARY KEY (FK_Column, OtherColumn),
    ...
);

For an n:m relationship:

CREATE TABLE TableN
(
    N_Identifier  <sometype> NOT NULL PRIMARY KEY,
    ...
);
CREATE TABLE TableM
(
    M_Identifier  <anothertype> NOT NULL PRIMARY KEY,
    ...
);
CREATE TABLE CrossRef
(
    N_Identifier  <sometype>    NOT NULL REFERENCES TableN,
    M_Identifier  <anothertype> NOT NULL REFERENCES TableM,
    PRIMARY KEY (N_Identifier, M_Identifier),
    ...
);

The SQL syntax is more or less DBMS-neutral (it should hew close the SQL standard syntax).

Answer 2

[EDIT] I believe the question is referring to Oracle Objects for OLE (OO40) [/EDIT]

For this example, consider a one-to-many relationship between order and line_item. (An order may have zero, one or more line_item, and a line_item is associate with exactly one order.) We're jumping past all of the modeling steps, and getting to a shell of what the definitions might look like.

One option is to use a reference:

create type order_typ as object
( id    integer
, ...
);

create table order_obj_table of order_type;

create table line_item
( order_ref ref order_typ scope is order_obj_table
, ...
);

Another alternative it to use a nested table (called a collection type):

create type line_item_typ as object
( id  integer
, ...
);

create type line_item_collection_typ as table of line_item_typ;

create type order_typ as object
( id          integer
, line_items  line_item_collection_typ 
, ...
);

[EDIT]

ADDENDUM:

Tony Andrews asks (quite reasonably) why one would want to use "nested tables". Tony points out that the resulting database structures will be "harder to access", by which he means (I think) the required query constructs are "non-standard" SQL.

Quite frankly, I can't think of a good reason that I would use a nested table, but I must at least acknowledge that OO4O does provide support for nested tables.

Why would one choose to use OO4O at all? It provides (ostensibly) improved performance against an Oracle database, by virtue of a native driver that avoids the overhead incurred by ODBC or OLE. It's also a technology specifically for Oracle, writing an application against the OO4O interface means that the app will essentially be tied to an Oracle database, which may be okay if there's no requirement for the app to support multiple (interchangeable) database engines.

More information and examples for OO4O are available from Oracle web site:

http://www.oracle.com/technology/tech/windows/ole/index.html

[/EDIT]