資料正規化

Visual Studio .NET 2003

資料庫設計者的工作就是要以消除非必要重複的方式建構資料並提供對所有必要資料的快速搜尋路徑。改善資料表、索引鍵、資料行和關聯性以建立高效率資料庫的程序就稱為正規化。正規化不僅限於關聯式檔案,也是索引檔常見的設計方式。

正規化是一項複雜的程序,涉及許多特定的規則和不同程度。以其完整定義而言,正規化就是捨棄重複群組、減少多餘資料、消除部分相依的複合索引鍵,以及分隔非索引鍵屬性的程序。用簡單的用語來說,正規化的規則可以簡化成一個句子:「每個屬性 (資料行) 都必須關聯至索引鍵」。每一資料表應該僅描述一種實體 (Entity) 類型 (例如人、地、客戶訂單或產品項目)。

正規化的優點包括:

  • 資料完整性 (因為沒有多餘、棄置的資料)
  • 最佳化的查詢 (因為正規化的資料表可產生快速、高效率的聯結)
  • 更快速地建立和儲存索引 (因為資料表所含資料行較少)
  • 快速的更新效能 (因為每一資料表具有的索引較少)
  • 改善併行作業 (因為資料表鎖定影響的資料較少)

您可以遵循簡單的基本原則將大部分資料庫正規化:含有重複資訊的資料表應該區分成不同的資料表以消除重複。

舉例來講,您的新應用程式是為一家書店開發的,這家書店必須追蹤每本書的資訊,包括下列資料:

  • 作者名稱
  • 作者地址
  • 作者電話
  • 書名
  • ISBN (國際標準書籍編號)
  • 發行年份
  • 發行者名稱
  • 發行者地址
  • 發行者電話

您可以只建立一個單一資料表,讓上面列示的每一資料項目都有一個對應的欄位。不過,如果仔細看看這些資料,很明顯這張資料表會包含許多多餘項目。例如,許多作者都不止寫過一本書,因此,每一書名的作者和發行者資訊可能會重複許多次。如果把所有這些欄位都放在單一資料表中,就會產生許多混淆而重複的項目。

運用正規化的原則,您可以將資料分成四個群組:作者、作者及書名、書名及發行者,如下表所示。

作者資料表 作者及書名資料表 書名資料表 發行者資料表
au_id (索引鍵) au_id (外部索引鍵) ti_isbn (索引鍵) pu_id (索引鍵)
au_name ti_isbn (外部索引鍵) ti_title pu_name
au_address   ti_yearpublished pu_address
au_phone   pu_id (外部索引鍵) pu_phone

索引鍵可提供建立資料表關聯性的方法。例如,作者及書名資料表建立了作者資料表和書名資料表之間多對多的關聯性 (因為一位作者可以寫許多本書,而一本書也可以由幾位作者合著)。使用作者及書名資料表,您可以查詢某位作者所撰寫的每一本書的編號 (使用 au_id),也可以判斷哪位作者撰寫了哪一本書 (使用 ti_isbn)。

另一種可能的替代方式是在書名資料表加上一個 au_id 屬性,來取代另外建立一個作者及書名資料表,不過前題是每一書名只能有一位作者,否則這種方式便一無是處。再仔細考量一下另一項假設:將 pu_id 屬性放在書名資料表中,是意味著每一書名只能由一位發行者發行。如果同一書名由不止一家公司發行的話,就必須針對每個不同的發行者在書名資料表中額外插入一列書名資料列,這樣就會造成許多重複的資料,而這個資料表也就不能算是已經正規化了。這種設計方式必須仔細評估商務資料的含意為何、應用程式可能採用的查詢類型為何、可能發生的多使用者併行作業問題,以及在同一資料表上有多個索引所可能帶來的效能問題。

評估各項正規化設計方法時您也應當瞭解,您也可以用許多方來刻意取消資料庫的正規化。為什麼要這樣做呢?因為您可能會由於效能問題或希望簡化 ad-hoc 報表,而刻意取消資料正規化。效能問題的成因,是由於某些產品查詢需要許多非常耗時而且使用大量磁碟空間的資料表聯結。而 ad-hoc 報表則是關係到讓使用者執行未結構化的查詢;不熟練的使用者可能不清楚如何從多個關聯的資料表擷取資訊。

反正規化 (Denormalization) 的技術包括複製資料、提供摘要資料、將資料表分割成水平或垂直的部分,以及建立反正規化的檢視來簡化報表 (這是比較聰明的做法,可以讓已正規化的資料庫不受影響)。

至於反正規化的範例,請看一下客戶地址的例子。客戶資料表通常包括以下屬性:姓名、街名、城市、州及郵遞區號。雖然城市和州可以直接從郵遞區號判斷出來,而且您也可以從每一客戶的資料中移除城市和州而讓客戶資料表正規化,但是一般的做法還是讓地址部分反正規化。

為何要讓地址反正規化的原因如下:

  • 在許多地方都會用到地址 (查詢、報表、信封、客戶支援畫面),而反正規化可以避免在整個應用程式中加入許多重建地址的程式碼。
  • 以地址作為架構的查詢可以使用更為簡捷的 SQL 語法。
  • 地址錯誤時只會影響單一客戶。

如需有關正規化的詳細資訊,請參閱您的資料庫伺服器文件。如果您使用的是 Microsoft SQL Server,請參閱《SQL Server 線上叢書》的<正規化>。

請參閱

資料完整性 | 邏輯資料庫設計

顯示: