Re: Object Table

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3F4D0BBA.54B7D408_at_exxesolutions.com...
> 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?

No, no, no, you had person *and* organization, remember?

> 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.

That is what object propellerheads always told us. Ad-hoc querying is unnecessary.

> The advantage of an object is that you can make a subclass from it.

And what subclasses of address do you have in mind? Received on Wed Aug 27 2003 - 15:34:23 CDT