proper use of types - or of something else?

From: Robert William Vesterman <bob.work_at_vesterman.com>
Date: 2000/07/19
Message-ID: <3975fe81.18203994@News.CIS.DFN.DE>#1/1

I have a question about how to generalize fields into structures.

Let's say that I want a table that looks something like this:

create table house_tbl
(

id	number		primary key not null,
addr_1	varchar2(30)	not null,
addr_2	varchar2(30)	null,
city	varchar2(30)	not null,
state	varchar2(2)	not null references state_tbl ( id ),
zip	varchar2(10)	not null,
bed	number		not null,
bath	number		not null,

);

But, when I go to add in my "office_tbl", I notice that there are a bunch of similar fields - all of the address stuff. I'm going to make many tables with addresses in them, I want them consistent (e.g. I want every address' addr_1 field to be 30 long), and if I ever have to make a change to them - like, adding in a country field and changing the state field so that it's only applicable if the country is US - I want to make that change in only one place.

So, now I want to do something like this:

create type address_type as object
(

addr_1	varchar2(30)	not null,
addr_2	varchar2(30)	null,
city	varchar2(30)	not null,
state	varchar2(2)	not null references state_tbl ( id ),
zip	varchar2(10)	not null

);

create table house_tbl
(

id	number		primary key not null,
address	address_type	not null,
bed	number		not null,
bath	number		not null

);

Unfortunately, I'm not allowed to specify things like "not null" or "references" within a type definition.

So now I do something like this, instead:

create type address_type as object
(

addr_1	varchar2(30),
addr_2	varchar2(30),
city	varchar2(30),
state	varchar2(2),
zip	varchar2(10),

member function is_valid returns boolean );

create type body address_type as
member function is_valid returns boolean is declare

	cursor state_cur is
		select id from state_tbl
		where state_tbl.id = self.state_id;
	state_rec state_cur%rowtype;
begin
	if ( addr_1 is null or
		city is null or
		state is null or
		zip is null ) then
		return false;
	end if;
	
	if not state_cur%isopen then
		open state_cur;
	end if;

	fetch state_cur into state_rec;
	if state_cur%notfound then
		return false;
	end if;

end;
end;

create table house_tbl
(

id	number		primary key not null,
address	address_type	not null,
bed	number		not null,
bath	number		not null

);

create trigger check_house_addr
before insert or update on house_tbl
for each row
begin

	if not :new.address.is_valid then
		raise_application_error ( -20001, "Invalid address" );
	end if;

end;

Now, I guess that something along those lines would work. But there are several problems with it:

(1) It's an awful lot of code to do something that you would think
should be able to be done just by typing "not null references state_tbl ( id )";

(2) It relies upon the tables that use the address_type type to do
validation - if some future programmer makes a table with an address, and doesn't set a trigger similar to check_house_addr, then the address is not validated;

(3) It requires coding on the client end to support the application
error;

(4) Overhead? I'd imagine that this is not the most efficient thing to
do every time that I'm updating a house's record, but I'm not sure.

In general, the above solution flat-out sucks, in my opinion. So what would you do instead of it?

Obviously, one answer is "Just put the individual address fields into each table and don't lose sleep over it", but "address" was just an example; my question is meant to apply in general.

Another answer would be "make an address table, and stick a foreign key for it into the house table". But then I would have to do two inserts instead of one, whenever I want to create a house.

Any other ideas?

Thanks,

Bob Vesterman.    Received on Wed Jul 19 2000 - 00:00:00 CDT