SQL-第二、三正規化

SQL-第二、三正規化

在第一正規化文章裡 我們的健康檢查資料表，有很多的眼睛的健康檢查時間 明顯一個資料表已經不夠用了，會有太多筆 必須多增加資料表來處理這個問題

過多的日期，選擇不易

1. 多個相同類型的值

   check-up	date
   eye	2023-05-30, 2023-06-07, 2023-06-17

   當你查詢此筆資料時，這麼多的日期，會令人感到疑惑 而且查詢也會比較複雜，你要去分割字串，不只麻煩還不夠直覺

2. 有相同資料的欄位

   check-up	date1	date2	date3
   eye	2023-05-30	2023-06-07	2023-06-17

   雖然沒有很多的日期，不過有很多一樣的欄位 同樣會令人感到疑惑 查詢同樣麻煩，要加上滿多的 OR

設計新的資料表

移除了第一張表的日期，加入第二張資料表

health
health_id
check_up

第二張表也加入id，可以保證不重複

chekc_up_date
check_id
date

然後是，如何使這兩張表產生連結，達到正規化並且容易 查詢的目的

連結兩張資料表

由於health資料表已經正規化，也有一個獨一無二的主鍵 把這一個主鍵提供第二個資料表，第二個資料表也可以第一正規化

chekc_up_date
check_id
date
health_id

health_id這一個新的欄位叫做外鍵，它參照另一張資料表的主鍵

外鍵

1. 外鍵使用的主鍵，也稱為父鍵，對應的表稱為父資料表
2. 外鍵可以是NULL
3. 外鍵不需要獨一無二
4. 不一定是父資料表的主鍵，但要有唯一性

建立有外鍵的資料表

CREATE TABLE health
(
   health_id INT NOT NULL  AUTO_INCREMENT,
   check_up VARCHAR(20),
   PRIMARY KEY(health_id)
);


CREATE TABLE check_up_date
(
   check_id INT NOT NULL  AUTO_INCREMENT,
   date VARCHAR(20),
   health_id INT,
   PRIMARY KEY(check_id),
   FOREIGN KEY(health_id) REFERENCES health (health_id)
);

先加入父資料表
INSERT INTO health
(check_up)
VALUES
('eye');

health_id	check_up
1	eye

馬上加入子資料表的資料 特別加入health_id是3的資料 目前父資料表只有一筆health_id是1的資料

INSERT INTO  check_up_date
(date, health_id)
VALUES
('2023-05-11', 3);

得到一串錯誤訊息 Cannot add or update a child row: a foreign key constraint fails (my_db.check_up_date, CONSTRAINT check_up_date_ibfk_1 FOREIGN KEY (health_id) REFERENCES health (health_id))

這個錯誤是外鍵限制的錯誤

外鍵限制

插入外鍵的值，必須已經存在在父資料表裡面，這叫參照完整性

增加了這一個限制，可以防止你意外破壞了外鍵的規矩

不過我們思考一下，我們哪時候有加上外鍵限制嗎?

外鍵限制是預設的

以這個例子是名子是check_up_date_ibfk_1

你可以通過create or alter 指令去指定外鍵限制的名子

CREATE TABLE check_up_date
(
    check_id INT NOT NULL  AUTO_INCREMENT,
    date VARCHAR(20),
    health_id INT,
    PRIMARY KEY(check_id),
    CONSTRAINT health_health_id_fk
    FOREIGN KEY(health_id) REFERENCES health (health_id)
);

刪除外鍵限制

根據不同DB語法不同

• MySQL

  ALTER TABLE check_up_date DROP FOREIGN KEY health_health_id_fk;
  

• SQL Server / Oracle / MS Access

  ALTER TABLE check_up_date DROP CONSTRAINT health_health_id_fk;
  

外鍵限制的名稱，最好樣養成良好的命名習慣，以防你日後改面心意想要修改時才好解除

解除之後，上面的錯誤就不會出現，因為你已經不用遵守父資料表的完全參照

刪除資料在父子資料表的相對關係

當你想要刪除health資料表的某筆資料時

而且它又是check_up_date某幾筆資料的外鍵時

你無法立刻刪除它

你必須先刪掉check_up_date裡某些使用此外鍵的資料

才可以刪掉health的那筆資料

這樣可以防止資料孤兒的狀況產生，資料孤兒太多會拖類整理查詢的速度

資料表的關聯

資料表的關係有三種主要的方式

• 一對一
• 一對多
• 多對多

思考出自己的資料是屬於哪一種，你就可以設計出適合的資料庫綱要(schema)

一對一

資料表A的一筆資料最多只能與資料表B的一筆資料關聯

line
line_id
account

Plateform_A
GameA_id
account
line_id(外鍵)

例如現在有一些平台可以透過line申請帳號

而且一個line只能申請一個帳號，反過來看一個平台帳號

也只能對應一個line的帳號，這個就是一對一

一對一很少使用，有幾個使用的時機供參考

1. 如上述的例子，不需要每次知道其他平台的資訊 可以抽出資料，讓查詢的速度更快而且資料表也更清晰

2. 假如有一些比較敏感的資料，可以存在另外一個資料表，控制存取次數
3. 如果是有特別大的資料在某些欄位，也可以分出來，另外用一張資料表 儲存

一對多

資料表A的一筆資料能與資料表B的多筆資料關聯 但資料表B的一筆資料只能與資料表A的一筆資料關聯

例如房地產，它是一對多的關係

people
people_id
money

real_estate
real_estate_id
price
people_id_id(外鍵)

一個人可以有很多房地產，但是一個房地產只會與一個人有關連

多對多

這是根據求職往的調查，青年男女的夢想職業如下

男性夢想職業
科技工程師
電競選手
網紅
銀行家
社群小編
主廚
作家
演藝人員
機師
老師

女性夢想職業
社群小編
網紅
烘焙師傅
空服員
作家
演藝人員
模特兒
銀行家
音樂家
藝術家

每個男女都有很多的夢想職業，而且每個夢想職業也是很多男女的目標

這種很多與很多的關係稱為多對多

多對多的關係圖

使用外鍵來觀察

職業名稱與women_id會有很多重複

這樣會造成資料表太大並且造成搜尋比較慢

Junction table

加入兩個表的主鍵，組成一個稱為Junction table的表

可以大大減少重複的資料，增加db的效能

組合鍵

剛剛所使用的Junction table就是一種組合鍵，因為它有雙主鍵

組合鍵的定義就是一個以上的主鍵

不只這樣，組合鍵也跟後續的正規化有關係

以下是一個組合鍵table的示範 欄位是地區、工作、職缺數、工作類別

area(主)	job(主)	vacancies	category
台北	社群小編	99	行銷企劃人員
高雄	演藝人員	13	傳播藝術
香港	演藝人員	101	傳播藝術
台中	作家	12	文字工作者
台北	音樂家	12	傳播藝術

互相關聯的欄位

設計資料表時，有時候兩個欄位會互相有關係，當A欄位被改動時，B就必須改動 這就稱為A對B有功能相依性

當你關聯鍵的時候

當你與主鍵有相依性的時候，事情就會更複雜一點了 因為主鍵有可能有很多個

vacancies相依於area與job 職缺數與 地區與工作相關 這是完全相依於主鍵

category相依於job不相依於area 工作類別與工作關聯與地區不關聯 這叫部分相依性

第二正規化

第二正規化要符合兩個條件

1. 符合第一正規化
2. 沒有部分相依性

上述的表由於category有部分相依性 要解決這個問題，可以把有部分相依的欄位獨立成一個資料表

area(主)	job(主)	vacancies
台北	社群小編	99
高雄	演藝人員	13
香港	演藝人員	10
台中	作家	12
台北	音樂家	12

job	category
社群小編	行銷企劃人員
演藝人員	傳播藝術
演藝人員	傳播藝術
作家	文字工作者
音樂家	傳播藝術

更快速可以達到第二正規化的方式是 符合第一正規化並且只有一個主鍵

遞移功能相依性

指的是非主鍵的欄位只要更改，就會造成其他非主鍵欄位的變動 此稱為遞移功能相依性

如職缺數與最近新增就是此等關係 職缺數只要變多，最近新增的工作一定也是變多的

area(主)	job(主)	vacancies	recently_added
台北	社群小編	99	27
高雄	演藝人員	13	7
香港	演藝人員	10	4
台中	作家	12	5
台北	音樂家	12	4

第三正規化

要達成第三正規化要滿足兩個條件

1. 符合第二正規化
2. 沒有遞移功能相依性

以上述的資料表為例子的話

job_status_id(主)	vacancies	recently_added
	1	99	27
	2	13	7
	3	10	4
	4	12	5
	5	12	4

area(主)	job(主)	job_status_id
台北	社群小編	1
高雄	演藝人員	2
香港	演藝人員	3
台中	作家	4
台北	音樂家	5

資料間的關係應以主鍵為主，不考慮主鍵以外的因素