规范化两个相关表 [英] normalizing two related tables

问题描述

考虑以下因素...

- 以客户编号列为主键的客户表，

也是一个名为SHORTNAME的列。

- 一个帐号表，其中包含一个帐号列作为主键，一个

客户编号列作为外键（引用客户表，

课程） ），以及一个名为SHORTNAME的列。


显然帐户表格没有正确规范化。


现在考虑一下这个事实程序/查询

从帐户表中读取SHORTNAME列。


我们可以通过某种方式重新定义SHORTNAME列以某种方式使用客户表上的SHORTNAME列进行隐式连接，而不是使用实际存储在帐户表中的数据来获得
？


显然我试图避免更改所有程序/查询

来明确地进行此连接。

DB2 / LUW v9.1


温柔。我仍然是一个RDBMS新手！


弗兰克

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

推荐答案

Frank Swarbrick写道：

Frank Swarbrick wrote:

请考虑以下事项......

- 以客户编号列为主键的客户表，并且

也是一个名为SHORTNAME的列。

- 一个帐户表，其中包含一个帐号列作为主键，一个

客户编号列作为外键（引用客户表，

课程）和一个名为SHORTNAME的专栏。


显然帐户表格没有正确规范化。

现在考虑一下这样一个事实，即有很多现有的程序/查询

从帐户表中读取SHORTNAME列。


是否有一些我们可以重新定义SHORTNAME列以某种方式执行的方式

与客户表上的SHORTNAME列隐式连接，而不是我们b
$ b实际存储在帐户表中的数据？


显然我试图避免必须更改所有程序/查询

来明确地进行此连接。


DB2 / LUW v9.1


温柔。我仍然是一个RDBMS新手！

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!

两个随机的想法


1）将帐户重命名为其他东西，比方说X然后创建一个视图：


创建视图帐户为

选择X.account_number，customer.SHORTNAME，X ....
来自X的
，客户

其中......


您可能需要在帐户视图中创建而不是触发器


2）在客户中创建一个超级密钥：


alter table customer add constraint AK_CUSTOMER

UNIQUE（customer_number，SHORTNAME）


然后移动账户中的fk，使其指向客户中的这个新密钥：


alter table account drop constraint ...

alter table account add constraint ...

外键（customer_number，SHORTNAME）

引用customer（customer_number，SHORTNAME）

on ...


仍未正常化，但fk将防止任何异常。当然

这意味着一些hazzle用于更新桌面上的SHORTNAME

。

您可能需要修复帐号表中的一些数据，然后才能添加

新fk


如上所述，这些只是随机的想法，充其量只会给你

一些想法。

HTH

/ Lennart

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

Frank

2007年6月27日星期三21:56:03 +0200，Lennart

< er ******** **********@gmail.com写道：

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

> Frank Swarbrick写道：

>Frank Swarbrick wrote:

>请考虑以下内容......
- 以客户编号列为主键的客户表，以及名为SHORTNAME的列。
- 包含帐号列作为主键，
客户编号列作为外键（引用客户表，当然）和一个名为SHORTNAME的专栏。

显然帐户表没有正确规范化。

现在考虑一下这个事实，即现有的程序很多/从帐户表中读取SHORTNAME列的查询。

是否有某种方式可以重新定义SHORTNAME列以某种方式进行隐式连接使用客户表上的SHORTNAME列，而不是使用实际存储在帐户表中的数据？

显然我正在努力避免更改所有程序/查询
明确地做这个连接。

DB2 / LUW v9.1

要温柔。我仍然是一个RDBMS新手！

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

两个随机的想法

1）将帐户重命名为其他东西，比如X然后在其上创建一个视图：

创建查看帐户作为
选择X.account_number，customer.SHORTNAME，X ....
来自X，客户
其中......

您可能需要在帐户视图上创建而不是触发器
2）在客户中创建超级密钥：

alter table customer add constraint AK_CUSTOMER
UNIQUE（customer_number） ，SHORTNAME）

然后在帐户中移动fk，使其指向客户中的这个新密钥：

alter table account drop constraint ...
alter table帐户添加约束...
外键（customer_number，SHORTNAME）
引用客户（customer_number，SHORTNAME）
上...

仍未规范但fk将防止任何异常。当然
这意味着在两个表中都会更新SHORTNAME的用例。

在添加
之前，您可能需要在帐户表中修复一些数据。 />如上所述，这些只是随机的想法，充其量只会给你一些想法。

HTH
/ Lennart

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

>Frank

我同意两者。我认为第一个是适当的

解决方案。我只是评论第二个问题，即UNQIUE不需要
。由于复合FK将保持同步，因此短名称的唯一性

无关紧要。


B.

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.

Brian Tkatch写道：

[...]

Brian Tkatch wrote:
[...]

>

我同意都。我认为第一个是适当的

解决方案。我只是评论第二个问题，即UNQIUE不需要
。由于复合FK将保持同步，因此短名称的唯一性

无关紧要。


B.

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

您是否建议可以声明复合fk反对

在父表中未被声明为主键或唯一的东西？

/ Lennart

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

这篇关于规范化两个相关表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！