Re: [Q] Oracle object types - Have I got myself confused?

Your question are very interresting.
I have not yet started looking at Oracle 8 and have no experience in OO programming.
But I'll try to anwer. If I'm wrong, I hope someone will correct.

I think there are 2 reasons for your confusion.

First :
You are talking of objects in RDBMS just in terms of data modelizing. I think that the advantage of object in RDBMS should not only be considered from data modelizing point of view (DDL) but also from a requesting point of view (DML).
You can create user datatypes, sure ! But you can create operators customized for that datatypes :

Refine your datatype person_ty, especially the address create type person_ty as object
(
person_id number(4),
firstname varchar2(10),
lastname varchar2(10),

address_num number(10),
address_street varchar2(10),
address_town varchar2(10),

You could associate an operator is_living_in using address_town. I do know how to do it, I suppose you'll find.

So you can request :
select person.firstname, person.lastname from fire_brigade_fulltime_staff where person is_living_in 'BOISE';
This query can be used against any table whose columns can totaly differs from "fire_brigade_fulltime_staff". It just needs to have a colum of the person_ty datatype.

Perhaps, my example is not too good because the request shouldn't be really more difficult WITHOUT the operator :
where person.address_town = 'BOISE'. Because your datatype is very near of classical datatypes.

If we leave off the classical datatypes, specific operators do not exist. It would be very interesting to have some of them. Consider photos. You could create an object type 'visage' with the operator is_eye_colored and select person.firstname from fire_brigade_fulltime_staff where person is_eye_colored 'blue';

Second :
The problem you have whith John Smith who becomes volunteer is the classical sub-type when data modelizing. It needs a exclusive constraint. Sure a fireman is not volunteer or fulltime at the same time.

create table fire_brigade_staff
(
person_id number(4),
status char(1) not null /*V=volunteer F=fulltime */ person person_ty
);

I hope it's not so wrong...
Chris Muir a écrit dans le message <35c03ac3.88770214_at_mitswa>...
>Howdy.
>
>Well I started looking at the new Oracle object types in Oracle8 today
>and I kind of got myself confused. I have approximately 2 years
>experience on C++ object oriented concepts but have found myself
>stumbling on the Oracle concepts. Could somebody please read my
>following logic to see if I'm on track or got things back to front?
>
>Oracle object types allow you to create new objects in the Oracle
>database. For instance a NUMBER or VARCHAR2 is an object type. These
>are what we used to call 'datatypes', it's just the terminology has
>been expanded. An object instance of a NUMBER would be 1, 7 or 123123
>etc.
>
>Oracle 8 allows you to create new object types, though these can be
>more complicated than the base datatypes such as NUMBER or VARCHAR2.
>For instance lets say everybody in the world has a first name, last
>name and address. Therefore we could create a new object type as
>follows:
>
>create type person_ty as object
>(
> firstname varchar2(10),
> lastname varchar2(10),
> address varchar2(10)
>);
>
>We can then utilise this object type just like we would use the old
>datatypes. So lets say for instance we are developing a system to
>store fire brigade staff. Therefore we could create the following
>table definition:
>
>create table fire_brigade_volunteer
>(
> volunteer_id number(4),
> person person_ty
>);
>
>Now here is my first stumbling point. I could have put the
>volunteer_id number into person_ty instead of fire_brigade_volunteer.
>Yes you are correct in saying that a volunteer_id wouldn't be
>applicable to a fulltime fire brigade member. So instead I would name
>the attribute person_id instead. So now we have the following
>scripts:
>
>create type person_ty as object
>(
> person_id number(4),
> firstname varchar2(10),
> lastname varchar2(10),
> address varchar2(10)
>);
>
>create table fire_brigade_volunteer
>(
> person person_ty
>);
>
>So what I can quickly see happening here is that for every table I
>create, it will have no attributes besides a single object type. Why
>bother with object types if it just results in a table definition that
>accesses an object type definition? Why not just have a table of
>persons which is accessed by a table of volunteers or fulltime staff?
>Read on....
>
>Lets take this further. In the example above say the fire brigade
>allocates fulltime staff id numbers, but volunteers aren't given a
>number. Therefore it would be advantages to keep the original person
>type and add the id attribute to the fulltime staff's table. But in
>turn what is to stop me having a type derived from another type here?
>So for instance a fulltime staff type derived from the person type.
>Yes I know I can technically implement this but actually why do it?
>Read on.....
>
>Now my next stumbling point is say we go to the following model:
>
>create type person_ty as object
>(
> firstname varchar2(10),
> lastname varchar2(10),
> address varchar2(10)
>);
>
>create table fire_brigade_volunteer
>(
> person person_ty
>);
>
>create table file_brigade_fulltime_staff
>(
> person_id number(4),
> person person_ty
>);
>
>Along comes user Blogs who creates a new staff member as follows:
>
>insert into file_brigade_fulltime_staff values
>(1234,person('john','smith','1 this place street'));
>
>Later on John Smith retires from the brigade but retains his services
>as a volunteer. User Blogs having totally forgotten that he already
>had entered John Smith as a fulltime staff member enters the
>following:
>
>insert into file_brigade_volunteer values
>(person('john','smith','1 this place street');
>
>So now we have duplicate data in the database and you can probably
>here the little 'normalisation' bells ringing in the background.
>There are obvious problems here if details about John Smith need
>updating.
>
>And after this very long explanation my question is what advantage
>does having object types here give you? To my knowledge you can't
>include primary keys in an object type so duplicate data is possible.
>
>
>Is it a question of the designer being aware of this and making sure
>that object types are used where there isn't a problem with duplicate
>data? Maybe the data in question should be inserted into table
>attributes rather than table object types?
>
>Is the example I've taken a bad example? Are 'people' exactly
>something you wouldn't create an object type for
>
>
>Argh! My brain hurts..... I can just see myself looking at this
>object type thing and missing the real point of the whole thing.
>
>I look forward to any help and advice you can give.
>
>Best regards,
> Chris :)
>
>Chris Muir
>Chris.Muir_at_MITSWA.Com.Au.No.Spam
>
>If you would like to contact me via email, just remove the .No.Spam
>part from the above email address.
Received on Thu Jul 30 1998 - 12:49:48 CDT