数据库设计三范式应用实战
问题:如何将下表中列出的订单信息存入到关系数据库中
客户名 总价值 商品列表
北京商户张三 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 ordergoods.orderid,ordergoods.count,goods.name,goods.price
from ordergoods,
goods
where
ordergoods.goodsid=goods.id
order by orderid
查询结果