normalizing two related tables

[Frank Swarbrick]

Consider the following...
- A customer table with a customer number column as the primary key, and
also a column called SHORTNAME.
- An account table with an account number column as the primary key, a
customer number column as a foreign key (referencing the customer table, of
course), and a column called SHORTNAME.

Obviously the account table is not properly normalized.

Now consider the fact that there are many existing programs/queries that
read the SHORTNAME column from the account table.

Is there some way that we can 're-define' the SHORTNAME column to somehow do
an implicit join with the SHORTNAME column on the customer table, rather
than using data actually stored in the account table?

Obviously I am trying to avoid having to change all of the programs/queries
to explicitly do this join.

DB2/LUW v9.1

Be gentle. I am still very much a RDBMS newbie!

Frank

[Lennart]

Frank Swarbrick wrote:

Consider the following...
- A customer table with a customer number column as the primary key, and
also a column called SHORTNAME.
- An account table with an account number column as the primary key, a
customer number column as a foreign key (referencing the customer table, of
course), and a column called SHORTNAME.

Obviously the account table is not properly normalized.

Now consider the fact that there are many existing programs/queries that
read the SHORTNAME column from the account table.

Is there some way that we can 're-define' the SHORTNAME column to somehow do
an implicit join with the SHORTNAME column on the customer table, rather
than using data actually stored in the account table?

Obviously I am trying to avoid having to change all of the programs/queries
to explicitly do this join.

DB2/LUW v9.1

Be gentle. I am still very much a RDBMS newbie!

Two random thoughts

1) rename account to something else, say X and then create a view over it:

create view account as
select X.account_number, customer.SHORTNAME, X....
from X, customer
where ...

You might need to create instead of triggers on the account view

2) create a superkey in customer as:

alter table customer add constraint AK_CUSTOMER
UNIQUE ( customer_number, SHORTNAME )

then move the f.k in account so that it points to this new key in customer:

alter table account drop constraint ...
alter table account add constraint ...
foreign key ( customer_number, SHORTNAME )
references customer (customer_number, SHORTNAME)
on ...

Still not normalized but the f.k will prevent any anomalies. Of course
this means some hazzle for the use cases that updates SHORTNAME in
either table.

You might need to fix some data in account table before you can add the
new f.k

As mentioned, these are just random thoughts that at best will give you
some ideas.
HTH
/Lennart

Frank

[Brian Tkatch]

On Wed, 27 Jun 2007 21:56:03 +0200, Lennart
<er******************@gmail.comwrote:

>Frank Swarbrick wrote:

>Consider the following...
- A customer table with a customer number column as the primary key, and
also a column called SHORTNAME.
- An account table with an account number column as the primary key, a
customer number column as a foreign key (referencing the customer table, of
course), and a column called SHORTNAME.

Obviously the account table is not properly normalized.

Now consider the fact that there are many existing programs/queries that
read the SHORTNAME column from the account table.

Is there some way that we can 're-define' the SHORTNAME column to somehow do
an implicit join with the SHORTNAME column on the customer table, rather
than using data actually stored in the account table?

Obviously I am trying to avoid having to change all of the programs/queries
to explicitly do this join.

DB2/LUW v9.1

Be gentle. I am still very much a RDBMS newbie!

Two random thoughts

1) rename account to something else, say X and then create a view over it:

create view account as
select X.account_number, customer.SHORTNAME, X....
from X, customer
where ...

You might need to create instead of triggers on the account view
2) create a superkey in customer as:

alter table customer add constraint AK_CUSTOMER
UNIQUE ( customer_number, SHORTNAME )

then move the f.k in account so that it points to this new key in customer:

alter table account drop constraint ...
alter table account add constraint ...
foreign key ( customer_number, SHORTNAME )
references customer (customer_number, SHORTNAME)
on ...

Still not normalized but the f.k will prevent any anomalies. Of course
this means some hazzle for the use cases that updates SHORTNAME in
either table.

You might need to fix some data in account table before you can add the
new f.k

As mentioned, these are just random thoughts that at best will give you
some ideas.
HTH
/Lennart

>Frank

I concur with both. I would think that the first is the appropriate
solution. I'd just comment on the second that the UNQIUE is not
required. Since the composite FK will keep it in sync, the uniqueness
of shortname is irrelevant.

B.

[Lennart]

Brian Tkatch wrote:
[...]

>
I concur with both. I would think that the first is the appropriate
solution. I'd just comment on the second that the UNQIUE is not
required. Since the composite FK will keep it in sync, the uniqueness
of shortname is irrelevant.

B.

Are you suggesting that one can declare a composite f.k. against
something that is not declared as primary key or unique in the parent table?
/Lennart

[Brian Tkatch]

On Thu, 28 Jun 2007 16:02:29 +0200, Lennart
<er******************@gmail.comwrote:

>Brian Tkatch wrote:
[...]

>>
I concur with both. I would think that the first is the appropriate
solution. I'd just comment on the second that the UNQIUE is not
required. Since the composite FK will keep it in sync, the uniqueness
of shortname is irrelevant.

B.

Are you suggesting that one can declare a composite f.k. against
something that is not declared as primary key or unique in the parent table?
/Lennart

Ugh, you're right, i forgot that ridiculous DB2 limitation. I ran into
that once before myself.

Thanx for the correction.

B.

[Serge Rielau]

Brian Tkatch wrote:

>Are you suggesting that one can declare a composite f.k. against
something that is not declared as primary key or unique in the parent table?

Ugh, you're right, i forgot that ridiculous DB2 limitation. I ran into
that once before myself.

Brian,

Trying to reverse engineer the requirement here:
a) Allow creation of FK to any parent without enforcing uniqueness of
the parent
b) Automatically derive a unique constraint on the parent when defining RI?
c) Something else

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

Brian Tkatch wrote:

On Thu, 28 Jun 2007 16:02:29 +0200, Lennart
<er******************@gmail.comwrote:

[...]

>>Are you suggesting that one can declare a composite f.k. against
something that is not declared as primary key or unique in the parent table?
/Lennart

Ugh, you're right, i forgot that ridiculous DB2 limitation. I ran into
that once before myself.

I dont think the limitation lies within DB2. Eventhough it should be
possible for the DBMS to derive the uniqness of p.k + another attribute,
I'm not aware of any sql dbms that does this (and I would be surprised
if the standard allows it). I also think that it could cause some
confusion if implemented that way

/Lennart

[Brian Tkatch]

On Thu, 28 Jun 2007 11:58:50 -0400, Serge Rielau <sr*****@ca.ibm.com>
wrote:

>Brian Tkatch wrote:

>>Are you suggesting that one can declare a composite f.k. against
something that is not declared as primary key or unique in the parent table?

Ugh, you're right, i forgot that ridiculous DB2 limitation. I ran into
that once before myself.

Brian,

Trying to reverse engineer the requirement here:
a) Allow creation of FK to any parent without enforcing uniqueness of
the parent
b) Automatically derive a unique constraint on the parent when defining RI?
c) Something else

Cheers
Serge

If it's good enough for the parent now, why change it just because the
child wants to use it as a composite FK?

I don't understand why it has to be UNIQUE at all.

B.

[Brian Tkatch]

On Thu, 28 Jun 2007 18:35:35 +0200, Lennart
<er******************@gmail.comwrote:

>Brian Tkatch wrote:

>On Thu, 28 Jun 2007 16:02:29 +0200, Lennart
<er******************@gmail.comwrote:

[...]

>>>Are you suggesting that one can declare a composite f.k. against
something that is not declared as primary key or unique in the parent table?
/Lennart

Ugh, you're right, i forgot that ridiculous DB2 limitation. I ran into
that once before myself.

I dont think the limitation lies within DB2. Eventhough it should be
possible for the DBMS to derive the uniqness of p.k + another attribute,
I'm not aware of any sql dbms that does this (and I would be surprised
if the standard allows it). I also think that it could cause some
confusion if implemented that way

/Lennart

Even without the PK, i don't understand why a parent must be UNIQUE.
IIRC, one build of DB2 doesn't even require that. And, how else could
we have a many-to-many relationship?

In my case, i wanted to use RI where the parent included NULLs. And
since NULLs are kept on the INDEX, it could not be UNIQUE. I had to
add a new TABLE with just one COLUMN in it and FK to it from both
TABLEs.

B.

[Lennart]

Brian Tkatch wrote:
[...]

Even without the PK, i don't understand why a parent must be UNIQUE.

I have a hard time imagining what the consequences would be otherwise.
For example, what would:

foreign key (x)
references T (x)
on delete cascade

mean if T.x were not unique?

IIRC, one build of DB2 doesn't even require that.

With all due respect, I strongly doubt it. If you can provide the name
of that version I will reconcider ;-)

And, how else could
we have a many-to-many relationship?

I dont follow, what does many-to-many have to do with unique keys?
/Lennart

[...]

[Brian Tkatch]

On Fri, 29 Jun 2007 09:18:46 +0200, Lennart
<er******************@gmail.comwrote:

>Brian Tkatch wrote:
[...]

>Even without the PK, i don't understand why a parent must be UNIQUE.

I have a hard time imagining what the consequences would be otherwise.
For example, what would:

foreign key (x)
references T (x)
on delete cascade

mean if T.x were not unique?

If all instances of the value are gone.

>IIRC, one build of DB2 doesn't even require that.

With all due respect, I strongly doubt it. If you can provide the name
of that version I will reconcider ;-)

I thought is was os/390, i don't know though. It was mentioned in
passing when i first stumbled on this limitation.

>And, how else could
we have a many-to-many relationship?

I dont follow, what does many-to-many have to do with unique keys?

A many-to-many requires an FK. If one side has to be UNIQUE, it can't
be "many".

B.

>

/Lennart

[...]

[Lennart]

Brian Tkatch wrote:

On Fri, 29 Jun 2007 09:18:46 +0200, Lennart
<er******************@gmail.comwrote:

>>Brian Tkatch wrote:
[...]

>>Even without the PK, i don't understand why a parent must be UNIQUE.

I have a hard time imagining what the consequences would be otherwise.
For example, what would:

foreign key (x)
references T (x)
on delete cascade

mean if T.x were not unique?

If all instances of the value are gone.

Well, I cant even imagine the anoalies one can derive from such a
system. IMO it's like opening Pandora's Box.

>>IIRC, one build of DB2 doesn't even require that.

With all due respect, I strongly doubt it. If you can provide the name
of that version I will reconcider ;-)

I thought is was os/390, i don't know though. It was mentioned in
passing when i first stumbled on this limitation.

I never worked with os/390, and if what you describe is true, I'm glad I
didnt. But, yes it is a limitation, a limitation that protects your data
from beeing corrupted.

>>And, how else could
we have a many-to-many relationship?

I dont follow, what does many-to-many have to do with unique keys?

A many-to-many requires an FK. If one side has to be UNIQUE, it can't
be "many".

A key identifies something, no? What prevents me from doing:

create table vehicle_type (
vehicle_type_id int not null primary key,
vehicle_type_short_name char(5) not null,
vehicle_type_description varchar(20)
);

insert into vehicle_type values (1,'mc','Motorcycle'), (2,'mc','Monster
Truck');

-- No, I dont care about vin numbers and iso whatever at the moment
create table vehicle (
vehicle_id int not null primary key,
vehicle_type_short_name char(5) not null,
foreign key (vehicle_type_short_name)
references vehicle_type
);

insert into vehicle values (1,'mc');

-- Now I'm the proud owner of both an mc and a truck, since

select v.id, vt.vehicle_type_description
from vehicle_type vt, vehicle v
where v.vehicle_type_short_name = vt.vehicle_type_short_name;

I see little gain and an awfull lot of trouble, dont you agree?
/Lennart

[Knut Stolze]

Lennart wrote:

>>>IIRC, one build of DB2 doesn't even require that.

With all due respect, I strongly doubt it. If you can provide the name
of that version I will reconcider ;-)

I thought is was os/390, i don't know though. It was mentioned in
passing when i first stumbled on this limitation.

I never worked with os/390, and if what you describe is true, I'm glad I
didnt. But, yes it is a limitation, a limitation that protects your data
from beeing corrupted.

It's not true. DB2 for z/OS requires a unique constraint being referenced,
too. (Primary keys are just unique constraints.) You will get
SQLCODE -538.

>>>And, how else could
we have a many-to-many relationship?

I dont follow, what does many-to-many have to do with unique keys?

A many-to-many requires an FK. If one side has to be UNIQUE, it can't
be "many".

Many-to-many relationships have to be modeled with an additional table.

CREATE TABLE a ( a INT NOT NULL PRIMARY KEY )
CREATE TABLE b ( b INT NOT NULL PRIMARY KEY )
CREATE TABLE r (
a INT NOT NULL,
b INT NOT NULL,
PRIMARY KEY (a, b),
FOREIGN KEY (a) REFERENCES a(a),
FOREIGN KEY (b) REFERENCES b(b) )

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Brian Tkatch]

On Fri, 29 Jun 2007 21:10:19 +0200, Lennart
<er******************@gmail.comwrote:

>Brian Tkatch wrote:

>On Fri, 29 Jun 2007 09:18:46 +0200, Lennart
<er******************@gmail.comwrote:

>>>Brian Tkatch wrote:
[...]
Even without the PK, i don't understand why a parent must be UNIQUE.

I have a hard time imagining what the consequences would be otherwise.
For example, what would:

foreign key (x)
references T (x)
on delete cascade

mean if T.x were not unique?

If all instances of the value are gone.

Well, I cant even imagine the anoalies one can derive from such a
system. IMO it's like opening Pandora's Box.

>>>IIRC, one build of DB2 doesn't even require that.

With all due respect, I strongly doubt it. If you can provide the name
of that version I will reconcider ;-)

I thought is was os/390, i don't know though. It was mentioned in
passing when i first stumbled on this limitation.

I never worked with os/390, and if what you describe is true, I'm glad I
didnt. But, yes it is a limitation, a limitation that protects your data
from beeing corrupted.

>>>And, how else could
we have a many-to-many relationship?
I dont follow, what does many-to-many have to do with unique keys?

A many-to-many requires an FK. If one side has to be UNIQUE, it can't
be "many".

A key identifies something, no? What prevents me from doing:

create table vehicle_type (
vehicle_type_id int not null primary key,
vehicle_type_short_name char(5) not null,
vehicle_type_description varchar(20)
);

insert into vehicle_type values (1,'mc','Motorcycle'), (2,'mc','Monster
Truck');

-- No, I dont care about vin numbers and iso whatever at the moment
create table vehicle (
vehicle_id int not null primary key,
vehicle_type_short_name char(5) not null,
foreign key (vehicle_type_short_name)
references vehicle_type
);

insert into vehicle values (1,'mc');

-- Now I'm the proud owner of both an mc and a truck, since

select v.id, vt.vehicle_type_description
from vehicle_type vt, vehicle v
where v.vehicle_type_short_name = vt.vehicle_type_short_name;

I see little gain and an awfull lot of trouble, dont you agree?
/Lennart

Why is that trouble? Get a better DBA! :)

B.

[Brian Tkatch]

On Mon, 02 Jul 2007 13:02:51 +0200, Knut Stolze <st****@de.ibm.com>
wrote:

>Lennart wrote:

>>>>IIRC, one build of DB2 doesn't even require that.

With all due respect, I strongly doubt it. If you can provide the name
of that version I will reconcider ;-)

I thought is was os/390, i don't know though. It was mentioned in
passing when i first stumbled on this limitation.

I never worked with os/390, and if what you describe is true, I'm glad I
didnt. But, yes it is a limitation, a limitation that protects your data
from beeing corrupted.

It's not true. DB2 for z/OS requires a unique constraint being referenced,
too. (Primary keys are just unique constraints.) You will get
SQLCODE -538.

Thanx for the report. Is there a platform where DB2 allows it? I seem
to remember it being mentioned.

B.

>>>>And, how else could
we have a many-to-many relationship?

I dont follow, what does many-to-many have to do with unique keys?

A many-to-many requires an FK. If one side has to be UNIQUE, it can't
be "many".

Many-to-many relationships have to be modeled with an additional table.

CREATE TABLE a ( a INT NOT NULL PRIMARY KEY )
CREATE TABLE b ( b INT NOT NULL PRIMARY KEY )
CREATE TABLE r (
a INT NOT NULL,
b INT NOT NULL,
PRIMARY KEY (a, b),
FOREIGN KEY (a) REFERENCES a(a),
FOREIGN KEY (b) REFERENCES b(b) )

[Knut Stolze]

Brian Tkatch <N/Awrote:

On Mon, 02 Jul 2007 13:02:51 +0200, Knut Stolze <st****@de.ibm.com>
wrote:

>>Lennart wrote:

>>>>>IIRC, one build of DB2 doesn't even require that.
>
>With all due respect, I strongly doubt it. If you can provide the name
>of that version I will reconcider ;-)

I thought is was os/390, i don't know though. It was mentioned in
passing when i first stumbled on this limitation.

I never worked with os/390, and if what you describe is true, I'm glad I
didnt. But, yes it is a limitation, a limitation that protects your data
from beeing corrupted.

It's not true. DB2 for z/OS requires a unique constraint being
referenced,
too. (Primary keys are just unique constraints.) You will get
SQLCODE -538.

Thanx for the report. Is there a platform where DB2 allows it? I seem
to remember it being mentioned.

If there is one, it could only be AS/400. But I doubt it because there is a
lot of effort going into ensuring that the 3 flavors of DB2 support the
same SQL syntax and semantics as much as possible.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

If there is one, it could only be AS/400. But I doubt it because there is a
lot of effort going into ensuring that the 3 flavors of DB2 support the
same SQL syntax and semantics as much as possible.

http://publib.boulder.ibm.com/infoce...zmsthctabl.htm

looks all regular to me..Brian, did you ever work with DB2 for vm/vse
perhaps?
Matter of fact I'd gladly accept any product that does this....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

On Mon, 02 Jul 2007 08:59:20 -0400, Serge Rielau <sr*****@ca.ibm.com>
wrote:

>Knut Stolze wrote:

>If there is one, it could only be AS/400. But I doubt it because there is a
lot of effort going into ensuring that the 3 flavors of DB2 support the
same SQL syntax and semantics as much as possible.

http://publib.boulder.ibm.com/infoce...zmsthctabl.htm

looks all regular to me..Brian, did you ever work with DB2 for vm/vse
perhaps?

Nope.

>Matter of fact I'd gladly accept any product that does this....

Cheers
Serge

I must be mistaken then. I thought last time i asked about this
someone mentioned where it was the case.

But if the two of you say no, that's i'm pretty sure it ain't so.

B.