数据库设计三范式应用实战 [转]

数据库设计三范式应用实战

数据库设计三范式应用实战

问题：如何将下表中列出的订单信息存入到关系数据库中
客户名                  总价值         商品列表
北京商户张三      1000元         上衣：20
大连商户李四      1500元         上衣：10；裤子：15；
上海商户王五      7500元         上衣：30；裤子：45；鞋子：60；

粗略设计方案

根据订单上三栏内容，拟用一个表来存储订单信息，此表称为Order表，字段如下：
Customer：可变字符型，用于存储客户地址姓名等信息。
Total：数字类型，用于存储一个订单的总商品价值
GoodsList：可变字符型，用于存储商品名和商品数量

这样的设计是否符合数据库设计三范式呢？可以一条条对照一下。

数据库设计三范式

范式（Normal Form）共有五种，但第四和第五种难于实现，并非必要。前三种标准格式为：
第一范式（1NF）要求信息必须是原子级的，信息不可再分。
第二范式（2NF）要求数据符合第一范式的标准，另外数据元素被组织成组，消除了冗余的数据。每个组包含一个主键和非关键数据，非关键数据必须在功能上依赖于主键。
第三范式（3NF）要求数据元素符合第二范式的标准，同时非关键数据不能包含依赖性。
个人对其的总结：1.信息不可分；2.以分组消除冗余数据，组内有主键作为唯一标识；3.组内部的非主键数据不能相互依赖。现在来看看粗略设计方案是否符合数据库设计三范式
1.Customer和GoodsList列可分，不符合第一范式。
2.无主键，且三大基本信息都不依赖于主键，没有进行合适分组，不符合第二范式。
3.total列明显依赖于GoodsList列，不符合第三范式。

因为粗略设计方案不符合三大范式，我们有必要对其进行修改。

修改后的第二次设计方案

首先为了满足第一范式，将三大数据列细分如下：

customerName：用于存储客户名称
customerAddress：用于存储客户地址
total ：用于存储商品总价值
Goods1：订单商品一
GoodsCount1：订单商品一的数量
Goods2：订单商品二
GoodsCount2：订单商品二的数量
Goods3：订单商品三
GoodsCount3：订单商品三的数量

到此，信息已经不可再分，这样的方案满足了第一范式的要求。

第二次设计方案存在的问题

第二次设计方案虽然满足了范式一，但是还有以下问题：
1.三个订单商品列和订单数量列高度相似。
2.如果客户订单商品类别确定在三种内还可以，一旦超过只有再增加列，更麻烦的是商品类别数量不确定。
3.各个字段作用差别很大，似乎不该放在同一张表的同一行中。

上述问题说明第二次设计方案还有待改造，让我们再来看看它是否合乎第二，三范式。

第二范式及解释

第二范式（2NF）要求数据符合第一范式的标准，另外数据元素被组织成组，消除了冗余的数据。每个组包含一个主键和非关键数据，非关键数据必须在功能上依赖于主键。
上面这段话中，组实际上就是“数据表”的意思，第二范式告诉我们，应该把数据元素按功能分开，分别存储到不同表中，而且每个表都该含有一个主键，非关键列在功能上依赖于关键列。

第三范式

第三范式（3NF）要求数据元素符合第二范式的标准，同时非关键数据不能包含依赖性。
第二次设计方案中，总价值total是依赖于商品类别和商品数量的。我们必须取消这样的非关键列之间的依赖性。
通过观察我们可以发现，总价值=商品单价*商品数量的总和，这样，总价值这一列就不需要存在了，直接计算得出即可。

接下来形成了第三次设计方案

订单表orderTable
id：主键，订单流水id
customerId：下订单的客户id，客户表Id的外键

订单商品表ordergoods
id:主键
orderId:订单表id的外键
goodsId：商品表id的外键
count：商品数量

商品表goods:
id:主键
name：商品名
price：单价

客户表customer：
id：主键
name：客户名
address：客户地址

诸表建表语句

create table customer(
   id int(10) primary key not null, 
   name VARCHAR(255), 
   address VARCHAR(255)
) 

create table goods(
   id int(10) primary key not null,  
   name VARCHAR(255), 
   price DOUBLE(10,2) 
) 

create table orderTable(
   id int(10) primary key not null,
   customerid int(10) not null,
   foreign key(customerid) references customer(id)
)

create table ordergoods(
   id int(10) primary key not null,
   orderid int(10) not null,
   goodsid int(10) not null,
   count  int(10),
   foreign key(orderid) references orderTable(id),
   foreign key(goodsid) references goods(id)
)

插值语句

insert into customer ( id, name, address ) values ( '1', '张三', '北京' )
 insert into customer ( id, name, address ) values ( '2', '李四', '大连' )
 insert into customer ( id, name, address ) values ( '3', '王五', '上海' )

 insert into goods ( id, name, price ) values ( '11', '上衣', '240' )
 insert into goods ( id, name, price ) values ( '12', '裤子', '300' )
 insert into goods ( id, name, price ) values ( '13', '鞋子', '350' )

 insert into ordertable ( id, customerid ) values ( '111', '1' )
 insert into ordertable ( id, customerid ) values ( '112', '2' )
 insert into ordertable ( id, customerid ) values ( '113', '3' )

 insert into ordergoods ( id, orderid, goodsid, count ) values ( '1111', '111', '11', '20' )
 insert into ordergoods ( id, orderid, goodsid, count ) values ( '1112', '112', '11', '10' )
 insert into ordergoods ( id, orderid, goodsid, count ) values ( '1113', '112', '12', '15' )
 insert into ordergoods ( id, orderid, goodsid, count ) values ( '1114', '113', '11', '30' )
 insert into ordergoods ( id, orderid, goodsid, count ) values ( '1115', '113', '12', '45' )
 insert into ordergoods ( id, orderid, goodsid, count ) values ( '1116', '113', '13', '60' )

 查询订单总价值

select t01.orderId,t01.customerName,t02.total from
(select 
   concat(customer.address,'商户',customer.name) as customerName,
   orderTable.id  as orderId  
from 
   orderTable,
   customer
where 
   ordertable.customerid=customer.id) t01,
(select orderid,sum(ordergoods.count*goods.price) as total
from ordergoods,
     goods
where
     ordergoods.goodsid=goods.id
group by orderid) t02
where t01.orderid=t02.orderid

--------------

select o.orderid,c.customername+c.address as userinfo, sum(goodprice*[count]) as total
from ordertable o
left join customertable c on  c.customerid=o.customerid
left join ordergood d on d.orderid=o.orderid
left join goodtable e on d.goodid=e.goodid
group by o.orderid,c.customername+c.address

查询结果

 

查询订单货物细节

 

select ordergoods.orderid,ordergoods.count,goods.name,goods.price
from ordergoods,
     goods
where
     ordergoods.goodsid=goods.id
order by orderid

 

查询结果

------------------------------

 

第一范式：

对于表中的每一行，必须且仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性.

（第一范式是通过把重复的组放到每个独立的表中，把这些表通过一对多关联联系起来这种方式来消除重复组的。）

第二范式：

第二范式要求非主键列是主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系.

（第二范式处理冗余数据的删除问题。当某张表中的信息依赖于该表中其它的不是主键部分的列的时候，通常会违反第二范式。）

第三范式：

第三范式要求非主键列互不依赖.

（第三范式规则查找以消除没有直接依赖于第一范式和第二范式形成的表的主键的属性。我们为没有与表的主键关联的所有信息建立了一张新表。每张新表保存了来自源表的信息和它们所依赖的主键。）

第四范式：

第四范式禁止主键列和非主键列一对多关系不受约束

（）

第五范式：

第五范式将表分割成尽可能小的块，为了排除在表中所有的冗余.
（）

在数据库设计时，大家应该时刻的注意到这几个范式。 其中第五范式是最难实现的。但是，还是需要尽量的去实现这些功能。