數(shù)據(jù)庫設(shè)計(jì)三范式應(yīng)用實(shí)戰(zhàn)
問題：如何將下表中列出的訂單信息存入到關(guān)系數(shù)據(jù)庫中
客戶名 總價(jià)值 商品列表
北京商戶張三 1000元 上衣：20
大連商戶李四 1500元 上衣：10；褲子：15；
上海商戶王五 7500元 上衣：30；褲子：45；鞋子：60；
粗略設(shè)計(jì)方案
根據(jù)訂單上三欄內(nèi)容，擬用一個(gè)表來存儲(chǔ)訂單信息，此表稱為Order表，字段如下：
Customer：可變字符型，用于存儲(chǔ)客戶地址姓名等信息。
Total：數(shù)字類型，用于存儲(chǔ)一個(gè)訂單的總商品價(jià)值
GoodsList：可變字符型，用于存儲(chǔ)商品名和商品數(shù)量
這樣的設(shè)計(jì)是否符合數(shù)據(jù)庫設(shè)計(jì)三范式呢？可以一條條對照一下。
數(shù)據(jù)庫設(shè)計(jì)三范式
范式（Normal Form）共有五種，但第四和第五種難于實(shí)現(xiàn)，并非必要。前三種標(biāo)準(zhǔn)格式為：
第一范式（1NF）要求信息必須是原子級(jí)的，信息不可再分。
第二范式（2NF）要求數(shù)據(jù)符合第一范式的標(biāo)準(zhǔn)，另外數(shù)據(jù)元素被組織成組，消除了冗余的數(shù)據(jù)。每個(gè)組包含一個(gè)主鍵和非關(guān)鍵數(shù)據(jù)，非關(guān)鍵數(shù)據(jù)必須在功能上依賴于主鍵。
第三范式（3NF）要求數(shù)據(jù)元素符合第二范式的標(biāo)準(zhǔn)，同時(shí)非關(guān)鍵數(shù)據(jù)不能包含依賴性。
個(gè)人對其的總結(jié)：1.信息不可分；2.以分組消除冗余數(shù)據(jù)，組內(nèi)有主鍵作為唯一標(biāo)識(shí)；3.組內(nèi)部的非主鍵數(shù)據(jù)不能相互依賴。
現(xiàn)在來看看粗略設(shè)計(jì)方案是否符合數(shù)據(jù)庫設(shè)計(jì)三范式
1.Customer和GoodsList列可分，不符合第一范式。
2.無主鍵，且三大基本信息都不依賴于主鍵，沒有進(jìn)行合適分組，不符合第二范式。
3.total列明顯依賴于GoodsList列，不符合第三范式。
因?yàn)榇致栽O(shè)計(jì)方案不符合三大范式，我們有必要對其進(jìn)行修改。
修改后的第二次設(shè)計(jì)方案
首先為了滿足第一范式，將三大數(shù)據(jù)列細(xì)分如下：
customerName：用于存儲(chǔ)客戶名稱
customerAddress：用于存儲(chǔ)客戶地址
total ：用于存儲(chǔ)商品總價(jià)值
Goods1：訂單商品一
GoodsCount1：訂單商品一的數(shù)量
Goods2：訂單商品二
GoodsCount2：訂單商品二的數(shù)量
Goods3：訂單商品三
GoodsCount3：訂單商品三的數(shù)量
到此，信息已經(jīng)不可再分，這樣的方案滿足了第一范式的要求。
第二次設(shè)計(jì)方案存在的問題
第二次設(shè)計(jì)方案雖然滿足了范式一，但是還有以下問題：
1.三個(gè)訂單商品列和訂單數(shù)量列高度相似。
2.如果客戶訂單商品類別確定在三種內(nèi)還可以，一旦超過只有再增加列，更麻煩的是商品類別數(shù)量不確定。
3.各個(gè)字段作用差別很大，似乎不該放在同一張表的同一行中。
上述問題說明第二次設(shè)計(jì)方案還有待改造，讓我們再來看看它是否合乎第二，三范式。
第二范式及解釋
第二范式（2NF）要求數(shù)據(jù)符合第一范式的標(biāo)準(zhǔn)，另外數(shù)據(jù)元素被組織成組，消除了冗余的數(shù)據(jù)。每個(gè)組包含一個(gè)主鍵和非關(guān)鍵數(shù)據(jù)，非關(guān)鍵數(shù)據(jù)必須在功能上依賴于主鍵。
上面這段話中，組實(shí)際上就是“數(shù)據(jù)表”的意思，第二范式告訴我們，應(yīng)該把數(shù)據(jù)元素按功能分開，分別存儲(chǔ)到不同表中，而且每個(gè)表都該含有一個(gè)主鍵，非關(guān)鍵列在功能上依賴于關(guān)鍵列。
第三范式
第三范式（3NF）要求數(shù)據(jù)元素符合第二范式的標(biāo)準(zhǔn)，同時(shí)非關(guān)鍵數(shù)據(jù)不能包含依賴性。
第二次設(shè)計(jì)方案中，總價(jià)值total是依賴于商品類別和商品數(shù)量的。我們必須取消這樣的非關(guān)鍵列之間的依賴性。
通過觀察我們可以發(fā)現(xiàn)，總價(jià)值=商品單價(jià)*商品數(shù)量的總和，這樣，總價(jià)值這一列就不需要存在了，直接計(jì)算得出即可。
接下來形成了第三次設(shè)計(jì)方案
訂單表orderTable
id：主鍵，訂單流水id
customerId：下訂單的客戶id，客戶表Id的外鍵
訂單商品表ordergoods
id:主鍵
orderId:訂單表id的外鍵
goodsId：商品表id的外鍵
count：商品數(shù)量
商品表goods:
id:主鍵
name：商品名
price：單價(jià)
客戶表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' )
查詢訂單總價(jià)值
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
查詢結(jié)果
查詢訂單貨物細(xì)節(jié)
select ordergoods.orderid,ordergoods.count,goods.name,goods.price
from ordergoods,
goods
where
ordergoods.goodsid=goods.id
order by orderid
查詢結(jié)果
