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

From: Chris Muir <cmuir_at_mitswa.com.au.no.spam>
Date: Thu, 30 Jul 1998 09:54:37 GMT
Message-ID: <35c03ac3.88770214@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 - 04:54:37 CDT