Link Search Menu Expand Document

7. 資料庫正規化 Normalization

一個資料庫會包含很多張表,那麽這些表要如何因應需求來做設計呢?該設計哪些表?該設計哪些字段?這兩節會告訴大家。

資料庫正規化(Normalization)是資料庫設計的一個非常重要的基本概念,目的是要去除重復的數據,增加數據的一致性。實際的作法是會將重復的字段,抽出來變成另一個新的表。

正規化還分成一階正規化、二階正規化、三階正規化、DK/NF正規化等等不同級別,一般來說我們的應用軟體會做到二階或三階正規化。

讓我們用實際的例子來看:假設我們要設計一個場景是紀錄「使用者 User」參與多個「活動 Event」

未正規化

user name user city zipcode event 1 event 1 date event 2 event 2 date event 3 event 3 date
ihower hsinchu 300 RubyConf 2015/9/11 JSConf 2015/5/1 CSSConf 2016/1/1
john taipei 100 RubyConf 2015/9/11

這種 Table 設計缺點很多:

  1. ihower 如果需要參加第四個活動,就必須變更 Table 的 Schema 增加更多字段。但是變更 Scmema 是一件成本很高的事情 :(
  2. john 沒有參加這麽多活動,多餘的字段都是 NULL,對資料庫來說是浪費空間
  3. ihower 跟 john 都有參加 RubyConf,但是 2015/9/11 這個活動日期重復存了。而且如果活動改日期,表示這些值都要改

一階: 移除重復語意的 columns

剛剛的 event 1, event 1 date, event 2, event 2 date, event 3, event 3 date 倆倆都是重復的,讓我們消滅它們:

user name user city zipcode event event date
ihower hsinchu 300 RubyConf 2015/9/11
ihower hsinchu 300 JSConf 2015/5/1
ihower hsinchu 300 CSSConf 2016/1/1
john taipei 300 RubyConf 2015/9/11

缺點:

  • 相比於沒有正規化的,現在新報名不需要修改 Schame 了
  • 但是重復的資料更多,包括用戶數據和活動數據

二階: 移除重復語意的 row

接下來需要拆表了,一口氣我們拆成三張表:users 表、events 表、registration 表,並且 users 和 events 要加上可識別的 id 字段。

相信對 Rails 已經很熟悉的同學,這就是 User model 和 Event model 透過 Registration model 來達成多對多關系。

users table

user id user name user city zipcode
1 ihower hsinchu 300
2 john taipei 100

events table

event id event event date
1 RubyConf 2015/9/11
2 JSConf 2015/5/1
3 CSSConf 2016/1/1

registrations table

user id event id register_at
1 1 2016-03-16 12:00:00
1 2 2016-03-16 12:30:00
1 3 2016-03-17 12:00:00
2 1 2016-03-18 12:00:00

這好多了,看起來沒有重復的資料了,如果要改 user 或 event 的數據,只需要改一個地方。

但是還有一個小地方可以改進,讓我們繼續看下去:

三階: 移除不依賴主 ID 的資料

在 users table 中,city 名字其實只跟 zipcode 相關,跟 user id 沒關系,因此這個 city 可以拆出來。在 users table 中只需要留著 zipcode 就好了。

users table

user id user name zipcode
1 ihower 300
2 john 300

zipcodes table

zipcode user city
300 hsinchu
100 taipei

小結

正規化讓數據不會重復和高度一致性,節省空間、增加修改數據時的效率、避免數據不一致的錯誤。

8. 資料庫設計實務

資料庫正規化看起來好像很難,所幸我們實務設計的時候,並不是從一階二階三階這樣慢慢思考的,而是用 Model 的關系來思考。

關聯設計 Associations

在傳統資料庫課程中,會使用 ER diagram (entity-relationship model) 這種描述關聯的標準圖表。

ERD “Crow’s Foot” Relationship Symbols Cheat Sheet

在這種圖表中,定義了一對一關系、一對多關系、多對多關系等的圖表。也就是在 Rails 中我們已經熟悉只用 has_one, has_many, belongs_to 等。

Primary Key 主鍵

所謂的 Primary Key 主鍵就是可以唯一識別的字段,在 Rails 中會預設產生一個字段是 id

這個主鍵有一些特性:

  • 不能 NULL 也不能重復
  • 最常見是 Simple ID Column Key (單一 column) 的設計。但也可以是 Compound/Composite Key (多個 columns 組成一個 primary key),但 Rails 不支援。

如何選擇你的 Primary Key ?

  • 最常見是自動遞增的整數(Auto incrementing Primary Key),這是 Rails 的預設方式,也是大家熟悉的 ID
  • UUID 通用唯一識別碼: 1. 分佈式系統喜歡用 2. 或是當作 token URL 功能
  • Natural key (例如身份證號碼, ISBN, 國碼 ISO ALPHA-2) 等等,不過你需要真的確認不會重復,例如 ISBN 其實會重復的

由於 Rails 預設使用自動遞增的整數當作 ID,一般不建議去改這件事情。在實戰應用章節中,有提到你可以自己增加別的字串來當作 Model URL。

加 primary key 的 SQL 語法:

CREATE TABLE events (id INTEGER NOT NULL PRIMARY KEY, name TEXT, capacity INTEGER);

加 auto increment primary key 的 SQL (各家語法不一樣,以下是 SQLite3)

CREATE TABLE events (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, capacity INTEGER);

這些在 Rails Migration 中已經幫我們做了。

Foreign Key (Reference Key) 外鍵

所謂的 Foreign Key 是指用來關聯一對多的字段字段,例如上述 registrations 表中的 user_id 和 event_id。外鍵的命名沒有特別規定,通常是 _id 結尾。

你不需要特別告訴資料庫這個是 foreign key,就可以當他是 foreign key 來使用。在 Rails 中,寫 belongs_to 的那個 model,就是 foreign key 字段的那個 model。

在 Rails Migration 中可以用 add_foreign_key 語法告訴資料庫這個是 foreign key,如此資料庫會提供 Referential integrity (Reference constraint) 驗證:

  • 確保新增或修改時,要參考的數據存在,不然資料庫會報錯
  • 刪除資料時,確保沒有其他資料參考我,不然資料庫會報錯

傳統資料庫設計非常重視數據的正確性,不過在 Rails 中則偏好在應用層解決,我們有學過利用 dependent 屬性來處理刪除的情況,例如:

class Event < ApplicationRecord
  has_many :registrations, :dependent => :destroy
end

其中 :dependent 可以有幾種不同的處理方式,例如:

  • :destroy 把依賴的 registrations 也一並刪除,並且執行 Registration 的 destroy 回呼
  • :delete 把依賴的 registrations 也一並刪除,但不執行 Registration 的 destroy 回呼
  • :nullify 這是預設值,不會幫忙刪除 registrations,但會把 registrations 的外部鍵 event_id 都設成NULL
  • :restrict_with_exception如果有任何依賴的 registrations 資料,則連 event 都不允許刪除。執行刪除時會丟出錯誤例外 ActiveRecord::DeleteRestrictionError。
  • :restrict_with_error 不允許刪除。執行刪除時會回傳 false,在 @event.errors 中會留有錯誤訊息。

逆正規化 denormalized

資料庫正規化並不是完全的真理,在不同場景下甚至會做逆正規化的設計。

在一般應用的場景下,也就是營運用途(OLTP)的 Schema 通常會達到比較高的正規化。但是在做分析用途(OLAP)的 Schema,則會偏好逆正規化的設計。因為不會修改數據,只會查詢,所以不會有修改數據造成數據不一致的風險。這領域叫做 Data Warehouse 數據倉庫。從 OLTP 資料庫搬資料轉到 OLAP 的過程就做 ETL (Extract-Transform-Load)

另外,在一些需要局部效能最佳化的場景,也會做一些逆正規化的設計,例如 Rails 的 計數快取 Counter Cache 功能,將數量額外用一個字段先存下來,免去之後計算的查詢時間。這也是一種逆正規化的設計。

Rails 補充

rails-erd 這個 gem 可以分析 Rails 產生 ERD 圖表。

這個 gem 需要安裝 graphviz 工具,請執行 brew install graphviz 就可以安裝。


Copyright © 2010-2021 Wen-Tien Chang All Rights Reserved.