數據庫設計三范式應用實戰
問題：如何將下表中列出的訂單信息存入到關系數據庫中
客戶名 總價值 商品列表
北京商戶張三 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
查詢結果
