正規化與非正規化的資料庫

資料庫設計

Lis Sulmont

Curriculum Manager

回到書店範例

非正規化：星型綱要

正規化：雪花綱要

非正規化查詢

目標： 取得 2018 年第 4 季在 Vancouver 賣出的所有 Octavia E. Butler 書籍數量

  SELECT SUM(quantity) FROM fact_booksales
    -- Join to get city
    INNER JOIN dim_store_star on fact_booksales.store_id = dim_store_star.store_id
    -- Join to get author
    INNER JOIN dim_book_star on fact_booksales.book_id = dim_book_star.book_id
    -- Join to get year and quarter
    INNER JOIN dim_time_star on fact_booksales.time_id = dim_time_star.time_id
  WHERE 
    dim_store_star.city = 'Vancouver' AND dim_book_star.author = 'Octavia E. Butler' AND
    dim_time_star.year = 2018 AND dim_time_star.quarter = 4;

共 3 次 join

正規化查詢

SELECT
  SUM(fact_booksales.quantity)
FROM
  fact_booksales
  -- Join to get city
  INNER JOIN dim_store_sf ON fact_booksales.store_id = dim_store_sf.store_id
  INNER JOIN dim_city_sf ON dim_store_sf.city_id = dim_city_sf.city_id
  -- Join to get author
  INNER JOIN dim_book_sf ON fact_booksales.book_id = dim_book_sf.book_id
  INNER JOIN dim_author_sf ON dim_book_sf.author_id = dim_author_sf.author_id
  -- Join to get year and quarter
  INNER JOIN dim_time_sf ON fact_booksales.time_id = dim_time_sf.time_id
  INNER JOIN dim_month_sf ON dim_time_sf.month_id = dim_month_sf.month_id
  INNER JOIN dim_quarter_sf ON dim_month_sf.quarter_id =  dim_quarter_sf.quarter_id
  INNER JOIN dim_year_sf ON dim_quarter_sf.year_id = dim_year_sf.year_id

正規化查詢（續）

WHERE
  dim_city_sf.city = `Vancouver`
  AND 
  dim_author_sf.author = `Octavia E. Butler`
  AND
  dim_year_sf.year = 2018 AND dim_quarter_sf.quarter = 4;

sum
7600

共 8 次 join

那麼，為什麼要讓資料庫正規化呢？

正規化可節省空間

非正規化會造成資料冗餘

正規化可節省空間

正規化可消除資料冗餘

正規化可提升資料完整性

1. 強制資料一致性

因參照完整性，必須遵守命名慣例，例如「California」，不能是「CA」或「california」

2. 更新、刪除、插入更安全

資料冗餘少＝需更動的記錄少

3. 更易擴充設計

小表比大表更容易擴充

資料庫正規化

優點

正規化消除資料冗餘：節省儲存空間
更佳的資料完整性：資料更正確且一致

缺點

複雜查詢需更多 CPU

還記得 OLTP 與 OLAP 嗎？

OLTP

例如作業型資料庫

通常高度正規化

寫入為主
優先更快且更安全的資料插入

OLAP

例如資料倉儲

通常較少正規化

讀取為主
優先更快的分析查詢

一起來練習吧！

資料庫設計