Re: Object Table

Mikito Harakiri wrote:

> "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> news:3F4CD7C4.EC870DF1_at_exxesolutions.com...
> > A better use of an object would be something like this:
> >
> > CREATE OR REPLACE TYPE address_t AS OBJECT (
> > address_1 VARCHAR2(40),
> > address_2 VARCHAR2(40),
> > city VARCHAR2(30),
> > state_prov VARCHAR2(2),
> > postal_code VARCHAR2(10),
> > country VARCHAR2(25))
> > /
> >
> > CREATE TABLE person (
> > first_name VARCHAR2(30),
> > last_name VARCHAR2(30),
> > person_add address_t);
> >
> > CREATE TABLE organization (
> > org_name VARCHAR2(30),
> > org_type VARCHAR2(5),
> > org_add address_t);
> >
> > Notice that the 'object' is leveraged to assure that all addresses are
> stored
> > identically.
>
> Now please write a query that tells how many addresses are in the system.
> Got it? Wouldn't it be cleaner just to have a Address table without Address
> object?

Surely you jest.

CREATE OR REPLACE TYPE address_t AS OBJECT ( address_1 VARCHAR2(40),
address_2 VARCHAR2(40),
city VARCHAR2(30),
state_prov VARCHAR2(2),
postal_code VARCHAR2(10),
country VARCHAR2(25));
/

CREATE TABLE person (
first_name VARCHAR2(30),
last_name VARCHAR2(30),
person_add address_t);

INSERT INTO person
VALUES
('Daniel','Morgan',address_t('123 Main St','Suite 100','Seattle','WA','98000','US'));

INSERT INTO person
VALUES
('Jack','Cline',address_t('100 N. Broadway','Apt C','Seattle','WA','98000','US'));

INSERT INTO person
(first_name, last_name)
VALUES
('Akiko','Toyota');

COMMIT; SELECT COUNT(*)
FROM person
WHERE person_add IS NOT NULL;

What's the issue?

In truth you and no one else has ever been asked for the number of addresses in a system. How many customers? Perhaps. But not addresses.

The advantage of an object is that you can make a subclass from it. If you don't use objects as they were designed to be used ... then you might as well use a boat anchor.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)