Day 32 資料庫正規化(一~三) - iT 邦幫忙

首先先來定義一下什麼是資料庫正規化： 資料庫正規化的過程，其實就是將一些實體的描述資料，透過一定的程序，將表單簡化，直到一張表單只單純描述一個事實為止。

第11屆iThome鐵人賽 4 SoftwareDevelopment 後端基礎PHP+Mysql&Laravel30日養成計畫系列第 32篇 Day32資料庫正規化(一~三) 11th鐵人賽 阿寶 團隊好想工作室v3.0 2019-12-2410:50:0417900瀏覽 首先先來定義一下什麼是資料庫正規化： 資料庫正規化的過程，其實就是將一些實體的描述資料，透過一定的程序，將表單簡化，直到一張表單只單純描述一個事實為止。

簡單來說，也就是透過一定的程序，去除資料庫中冗餘的內容，讓資料能夠井然有序且有效率的儲存。

為何要執行正規化？ 提昇儲存資料與資料庫操作效率 減少資料異常 使資料庫維護更容易 正規化的資料庫特性 經過正規化後的資料庫，應具備以下特性： 欄位唯一性：每個欄位只儲存一項資料 主關鍵欄位：每筆資料都擁有一個主鍵，來區別這些資料 功能關聯性：欄位之間的關聯應該要明確 欄位獨立性：欄位之間不應存在遞移相依 你484也建過像這樣的table? 我們先來看一下這張完全不符合正規化特性的table，相信後端的朋友可能在剛開始學資料庫的時候可能也設計過類似這樣的table。

Comsumptions 姓名 性別 gender 項目 價格 數量 商店 地址 日期 訂單編號 阿寶 女 F 鉛筆、橡皮擦 20,50 1,2 久成久 東區大學路 12/17 1 豆芽 女 F 牛奶、三明治 70,10 2,1 全家 東區北門路 12/18 2 豆芽 女 F 牛奶、三明治 70,10 3,4 全家 東區北門路 12/19 3 老皮 男 M 蛋餅、奶茶 30,40 1,2 日蝕 東區成功路 12/19 4 這張名為consumptions的table裡頭儲存的是使用者的消費紀錄。

很明顯的，有幾個大問題存在於這張表中： 首先：他在項目和價格這兩個columns裡面都出現了一個欄位儲存兩項以上的資料的問題，分隔這些資料的符號甚至都不統一。

第二：性別和gender這兩個column，他們在意義上是重複的。

我們其實可以保留其中一個好了，不必浪費空間跟時間來儲存多餘的資料。

第三：這些資料並沒有一個primarykey可以辨識他們的區別，這些欄位都有可能是重複的。

例如第二筆和第三筆資料的內容長的一模一樣，假設我今天想要查詢豆芽第一次在全家消費牛奶的紀錄，我就沒辦法依據這些資料的內容來找到這比消費紀錄。

問題： 一個欄位儲存多筆資料 出現意義上重複的欄位 缺乏主鍵(PrimaryKey) 第一正規化 因此我們要透過第一正規化來修正以上的問題。

第一NF要完成的工作： 一個欄位只能有單一值 消除意義上重複的欄位 決定主鍵 第一正規化會把原本一個欄位儲存多項資料的部份分開來儲存，然後刪除意義重複的column，最後給每一筆資料一個primarykey來當作他們的標識。

經過第一正規化後的表就會長成這樣，我們把每一項商品的消費都分開來儲存，然後把多餘的欄位刪掉。

現在這張表中的每個欄位都會是有意義的。

定義pk為姓名、項目、數量、商店、日期和訂單編號（複合主鍵） comsumptions 姓名 性別 項目 價格 數量 總金額 商店 地址 日期 訂單編號 阿寶 女 鉛筆 20 1 20 久成久 東區大學路 12/17 1 阿寶 女 橡皮擦 50 2 100 久成久 東區大學路 12/17 1 豆芽 女 牛奶 70 3 210 全家 東區北門路 12/18 2 豆芽 女 三明治 10 1 10 全家 東區北門路 12/18 2 豆芽 女 牛奶 70 3 210 全家 東區北門路 12/19 3 豆芽 女 三明治 10 4 40 全家 東區北門路 12/19 3 老皮 男 蛋餅 30 1 30 日蝕 東區成功路 12/19 4 老皮 男 奶茶 40 2 80 日蝕 東區成功路 12/19 4 然而，這張表還是存在著一些問題：他的每一筆消費紀錄都要紀錄消費者的性別、商店的名稱和地址，重複內容過多。

假設今天老皮在日蝕買了一百個蛋餅，那我就要重複輸入：老皮，性別為男性，商店的地址為東區成功路，非常沒有效率。

又如果這間日蝕搬到了民族路上，豈不是還要在這一百筆資料裡一筆一筆做修改？ 問題： 出現過多重複資料 因此我們要透過第二正規化來修正上述的問題 第二正規化 第二NF要完成的工作： 消除部分相依 部分相依的意思為跟主鍵只有一部份有關係，另一部份沒有關係的欄位，我們要把這些欄位獨立於另一張表。

mysql預設會以id作為pk，若該表以完成第一正規化，設定id為pk則直接滿足第二正規化，因為pk為單一鍵(已經是最小單位了，自然不會有和pk"部分相依的情況") 但這邊的主鍵指的不是形式上的'id'這個欄位，而是能用來辨識資料區別的複合主鍵，例如購物紀錄中的消費者、物品、價錢、時間這些欄位所組成的複合主鍵。

(我自己是將id理解成那些複合主鍵的代碼，畢竟在大部分的情況下我們所稱的主鍵就是primarykey，就是像id這種欄位) 舉這邊的例子來看，第二正規化要新增一個獨立的table來儲存這些一直重複出現的欄位，接著透過foreignkey來關聯這些table 第二正規化後，另外建了兩個獨立的表，items跟consumers來儲存消費者資訊和商品資訊。

consumptions分別和items跟consumers關聯，把這些資料分出去儲存後，orders就單純的只儲存消費者和購買的商品，以及消費日期三種資料而已。

在orders中，消費者對應到consumers的姓名，商品對應到items的名稱，而消費者和商品就是orders的foreignkey。

consumers id 姓名 性別 2 阿寶 女 3 豆芽 女 4 老皮 男 shops id 商店 地址 1 久成久 東區大學路 2 全家 東區北門路 3 日蝕 東區成功路 items id 項目 價格 商店id 1 鉛筆 20 1 2 橡皮擦 50 1 3 牛奶 70 2 4 三明治 10 2 5 蛋餅 30 3 6 奶茶 40 3 orders id 消費者id 商品id 數量 總金額 日期 訂單編號 1 1 1 1 20 12/17 1 2 1 2 2 100 12/17 1 3 2 3 3 210 12/18 2 4 2 4 1 10 12/18 2 5 2 3 3 210 12/19 3 6 3 4 4 40 12/19 3 7 3 5 1 30 12/19 4 8 3 6 2 80 12/19 4 然而，這樣還是會有一個問題，就是遞移關係。

所謂遞移關係，在這個範例裡就是指：總金額是依賴商品及數量的資訊，而商品id和數量又和主鍵直接相關，那總金額和主鍵之間的關係就是遞移關係。

用個比較容易理解的方式來說明：為了避免數量改變而總金額沒改到造成資料錯誤，應該把總金額那個欄位移除。

問題：存在主鍵以外的欄位與主鍵間接(遞移)相依 第三正規化 在第三正規化的規範中，要消除資料表中與主欄位的遞移相依 第三NF要完成的工作： 消除資料表中的遞移相依 消除遞移相依：非主鍵屬性的欄位都只能和候選鍵相關，非主鍵屬性的欄位彼此間應該要是獨立無關的 遞移相依：欄位1和主鍵相關，欄位2和欄位1相關，欄位2和主鍵就是地移相依 候選鍵：欄位組合讓資料能是唯一的，並且是最小唯一 主鍵為訂單編號，而總金額和非主鍵欄位商品、數量存在遞移相依 consumers id 姓名 性別 1 阿寶 女 2 豆芽 女 3 老皮 男 shops id 商店 地址 1 久成久 東區大學路 2 全家 東區北門路 3 日蝕 東區成功路 items id 項目 價格 商店id 1 鉛筆 20 1 2 橡皮擦 50 1 3 牛奶 70 2 4 三明治 10 2 5 蛋餅 30 3 6 奶茶 40 3 orders id 消費者id 單價 數量 商品id 日期 訂單編號 1 1 20 1 1 12/17 1 2 1 50 2 2 12/17 1 3 2 70 3 3 12/18 2 4 2 10 1 4 12/18 2 5 2 70 3 3 12/19 3 6 3 10 4 4 12/19 3 7 3 30 1 5 12/19 4 8 3 40 2 6 12/19 4 BUT， 但是，並不是每次建表都要照著正規化的規範才是最好的。

像前面這個範例，假如我的資料量不大，但我的業務需求是要反覆的去查詢某個消費者買的什麼商品，是在什麼地方的資料。

例如，我想找到今天豆芽買牛奶的地址，我必須先從consumptions關聯到items，再關聯到shops。

這樣似乎反而比第二正規化前更缺乏效率。

結論 因此，資料庫正規化要進行到什麼程度，應視對資料庫操作的需求和資料量而定，而不是每次建表都只要無腦的全部照著正規化的規範去設計你的表格就好。

留言1 追蹤 檢舉 上一篇 Day31用Laravel儲存圖片 下一篇 Day33Laravel實做雙人橋牌之心得分享 系列文 後端基礎PHP+Mysql&Laravel30日養成計畫 共36篇 目錄 RSS系列文 訂閱系列文 68人訂閱 32 Day32資料庫正規化(一~三) 33 Day33Laravel實做雙人橋牌之心得分享 34 Day34LaravelFactory：填充假資料 35 Day35Laravel實作LINEBot小幫手筆記 36 Day36LaravelEloquentRelations 完整目錄 1則留言 0 微中子 iT邦新手4級‧ 2020-02-1818:04:42 正規化有五種，這篇介紹了前三種 有興趣可以看我寫的，五種都有講到(雖然就是英文的) GoodRDBDesignwiththeConceptofNormalForms 回應 檢舉 登入發表回應 我要留言 立即登入留言 iT邦幫忙鐵人賽 參賽組數 1087組 團體組數 52組 累計文章數 20492篇 完賽人數 572人 鐵人賽最新文章 Day36-使用Container建立AmazonSageMaker端點 Day35-AmazonSageMaker簡介 今天來瞄一眼龍與雀的科技：知覺共享技術Body-sharing 更新網格交易機器人 Day34-實作S3驅動Lambda函數進行YOLO物件辨識 建立第一個RESTfulapiserver(設定環境變數篇)-7(Day19) Day48.下載個範例iosapp來試著build 原來Arduino開發板的腳位與程式內數字的對應會隨著開發商不同而改變? EP32:TopStoreAppwith.NETMulti-platformAppUI(MAUI) [Day32]HexoxGitHubPages主題自訂樣式+問題排除 前往鐵人賽 技術推廣專區 [Day2]抓取每日收盤價 [Day1]基本工具安裝 利用python取得永豐銀行API的Nonce 永豐金融API測試員 [Day03]tinyML開發板介紹 [Day01]在享受tinyML這道美食之前 [Day3]使用ta-lib製作指標 計算API所需要的參數:HashID [Day4]函數打包與買進持有報酬率試算 計算API所需要的參數:IV 前往鐵人賽 熱門問題 電信商說我透過Steam或是PS4下載遊戲是異常封包而斷我網路，怎麼辦？ 有關新手SQL問題一問 有人知道怎麼解決gmail一直擋信的問題? LINUX帳戶密碼最大使用期限90天 Win10的系統還原 線上版文書處理除了Google雲端,365,還有其他選擇嗎? Windows2008R2如何讀取ISO檔 Server掛掉，Acronis的備份檔無法復原 MSSQL資料庫主機Ping的到，但無法連線 MSSQLUNION後進階排序問題請教 IT邦幫忙 站方公告 2021iThome鐵人賽精彩文章超過2萬篇，預計12月揭曉本屆獲勝鐵人 熱門tag 看更多 13th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 windows php python windowsserver linux c# 程式設計 資訊安全 css vue.js sql 分享 熱門回答 有關新手SQL問題一問 MSSQL資料庫主機Ping的到，但無法連線 TIPTOPmail MSSQLUNION後進階排序問題請教 線上版文書處理除了Google雲端,365,還有其他選擇嗎? 電信商說我透過Steam或是PS4下載遊戲是異常封包而斷我網路，怎麼辦？ 如果想把現有NAS掛給ESXI6當VM備份儲存區.請問要如何做 新手程式提問 Windows2008R2如何讀取ISO檔 求助!用ExcelUserForm來Update資料到SQL 熱門文章 【第31天】番外篇-Windows+YOLOV4本地端訓練 原來Arduino開發板的腳位與程式內數字的對應會隨著開發商不同而改變? 建立第一個RESTfulapiserver(設定環境變數篇)-7(Day19) Day48.下載個範例iosapp來試著build Day34-實作S3驅動Lambda函數進行YOLO物件辨識 【換機必學】將資料從Android轉移到iOS的兩種方法 EP32:TopStoreAppwith.NETMulti-platformAppUI(MAUI) [Day32]HexoxGitHubPages主題自訂樣式+問題排除 更新網格交易機器人 免費錄影！原來Windows10有内建的螢幕錄影工具 一週點數排行 更多點數排行 海綿寶寶(antijava) raytracy(raytracy) ccenjor(ccenjor) rogeryao(rogeryao) ㊣浩瀚星空㊣(yoching) 純真的人(jer5173) Samuel(kuanyu) huahualiu(ffang55tw) juck30808(juck30808) 淺水員(ren1244) × At 輸入對方的帳號或暱稱 Loading 找不到結果。

標記 {{result.label}} {{result.account}} 關閉