前言

接續上一篇的資料庫實作，我們來討論資料庫設計準則，包括【正規化】(Normalization)、反正規化 (Denormalization)、主鍵(Primary key)/索引(Index)/外來鍵(Foreign key)/代理鍵(Surrogate Key)...等。

資料庫設計準則(Schema design)

線上交易系統(Online transaction processing, OLTP)的資料庫通常採用【正規化】(Normalization)設計準則，線上分析處理系統(Online analytical processing, OLAP)的資料庫則採用【星狀結構】(Star schema)設計準則，而資料倉儲(Data warehouse)又是採用另一種設計準則，主要是因應使用目的的不同而有所調整。例如：

1. 一般商業交易系統會採用【正規化】，要確保資料一致性與完整性。
   
   圖一. 【正規化】(Normalization)

2. 多維度分析(Multi-dimensional analysis)會希望所有維度(Dimension)階層(國家/縣市/鄉鎮、年/月/日)集中在一起，以利多個維度展現，故會合併資料表，形成【星狀結構】，中央為儲存衡量指標的事實表(Fact table)。
   
   圖二. 【星狀結構】(Star schema)

正規化 (Normalization)

上一篇討論的投票系統屬於OLTP，因此會遵循【正規化】(Normalization)設計，根據學理研究，正規化分為很多等級，可參閱【Normal Forms -- Simple Talk】一文：

1. 第一正規化(First Normal Form, 1NF)
2. 第二正規化(Second Normal Form, 2NF)
3. 第三正規化(Third Normal Form, 3NF)
4. EKNF: Elementary Key Normal Form
5. BCNF: Boyce–Codd Normal Form
6. 第四正規化(Fourth Normal Form, 4NF)
7. ETNF: Essential Tuple Normal Form
8. 第五正規化(Fifth Normal Form, 5NF)
9. DKNF: Domain-Key Normal Form
10. 第六正規化(Sixth Normal Form, 6NF)

嚇死人了，怎記的了那麼多，還好，實務上只要遵守前3個正規化即可。

1. 1NF：欄位不要重覆，例如上一篇的Question及Choice資料表可合併為一個資料表，如下：

question_text, choice_1, choice_2, choice_3, choice_4, choice_5

以上假設一個問題最多只有5個答案選項，這種方式查詢很方便，但是有一天需求變更，變成5個以上的答案選項，資料表就要重整了。

2. 2NF：符合1NF外，主鍵以外的欄位須完全依賴主鍵(Primary key, PK)，主鍵是指【以主鍵欄位查詢資料表只會查詢到一筆資料】(To identifiy each record in a table UNIQUELY)。

假設有一個表格如下：

圖三. 每日股價

這是從交易所下載的股價，主鍵為【股票代碼】+【日期】，因為每支股票一天只有一筆資料，開/高/低/收依賴主鍵沒有問題，但【簡稱】則只依賴【股票代碼】，故違反2NF，會有以下副作用：

• 若有一天公司變更【簡稱】，則一支股票會有2個簡稱，造成資料不一致。
• 若刪除所有交易資料，則基本資料也不見了，即【股票代碼】對應【簡稱】資訊也被刪除了，造成資料完整性被破壞。
  故應該設計成兩個表：

股票基本資料表：股票代碼、簡稱。
股票交易表：股票代碼、日期、開/高/低/收

3. 3NF：符合1NF、2NF外，主鍵以外的其他欄位不可互相依賴，例如下表：
   
   【參賽者】+【日期】+【項次】是主鍵，而總分 = 分數 * 加權 / 100，【總分】依賴【分數】及【加權】欄位，是計算衍生而來，這種欄位在製作統計報表時很方便，不需每一份報表都重新計算，但是，如果有天某一筆的【分數】或【加權】數值遭更改，【總分】沒有重新計算，就會造成資料不一致。

反正規化 (Denormalization)

依照正規化會將資料拆分成許多表，可以保證資料的一致性與完整性，但是有時候還是抵擋不了誘惑，會進行反正規化，加入冗餘(Redundent, 或稱重複)及計算欄位，以利資料查詢/報表製作的方便性及效率提升，例如

1. 額外將公司名稱與代碼一起放在交易表內。
2. 每月營收以12個欄位存放，查詢單一資料表即可得到全年的營收。
3. 在新增訂單時，計算總價並存至訂單表內，便於分析。

這些都違反正規化，但是，為了效率不擇手段，真的需要這樣作時，要如何降低副作用呢? 筆者建議以下方法：

1. 使用【預存程序】(Stored procedure)：在新增/更正/刪除時重新計算衍生的欄位或重新查詢並更正冗餘欄位。
2. 使用【鏡射】(Mirroring)批次複製資料：定時將單一或多個資料表整理至其他server或資料庫。

提升查詢效率

正規化將資料拆分的很零散，查詢時必須連結(join)許多資料表，效率就會不彰，因此，必須建立【索引】(Index)加快查詢速度，資料庫會內建資料結構以利搜尋，使用索引的效能會加快數倍，主鍵(Primary key)/外來鍵(Foreign key)都特殊的索引，主鍵前面已介紹過，外來鍵是子資料表(Child table)的特定欄位是另一資料表(父資料表)的主鍵，它對於ORM非常重要，可以引導ORM套件如何生成SQL，同時，對於資料庫執行計劃(Execution plan)也是重要的參考因素。

另外，ORM通常會採用【代理鍵】(Surrogate Key)，以自動給號(Auto generation)的欄位作為主鍵，取代【自然鍵】(Natural Key)或稱【商業鍵】(Business Key)，如公司代碼、商品編號...等，優點如下：

1. 查詢效率較佳：代理鍵通常使用長整數，比通比使用字串的自然鍵來的有效率。
2. 允許修改自然鍵：因為主鍵無法被修改，若使用代理鍵為主鍵，自然鍵就可以被修改。
3. 適用於分散式資料庫：使用多個分散式資料庫，若使用自然鍵須至每一個分割(Partition)確認是否重複，相對的採用代理鍵，可統一由自動給號的server管理。
   代理鍵也有一些缺點：
4. 代理鍵需佔額外儲存空間。
5. 代理鍵是一串無意義的數字，查詢時還是會以自然鍵為主，需要額外建立索引，查詢子資料表可能需要先連結主表。

相關討論可參閱【What are the benefits and drawbacks of using surrogate keys in a data warehouse?】及【Surrogate Key in DBMS】。

檢視ORM模型與資料表設計

上篇的模型(13\django\mysite\polls\models.py)如下：

# 問題
class Question(models.Model):
    question_text = models.CharField(max_length=200)
    pub_date = models.DateTimeField("date published")

# 答案選項
class Choice(models.Model):
    question = models.ForeignKey(Question, on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    votes = models.IntegerField(default=0)

1. 依照1NF，將問題及答案選項分拆成Question及Choice資料表。
2. 設定外來鍵(Foreign key)連結Question及Choice資料表，models.CASCADE表示刪除問題時，也會一併刪除所屬的答案選項。

    question = models.ForeignKey(Question, on_delete=models.CASCADE)

3. Django預設會採用代理鍵(Surrogate Key)，如下圖的id。
   

4. 自動生成的Create table SQL如下：

CREATE TABLE "polls_question" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
    "question_text" varchar(200) NOT NULL, 
    "pub_date" datetime NOT NULL
    );

NoSQL資料庫

正規化是關聯式資料庫設計的準則，缺點是資料會拆分的很零散，因此有NoSQL資料庫的誕生，它分為多種類型：

1. 文件式資料庫(Document-based databases)：父/子資料表合併為單一文件。
2. Json格式資料庫(Key-value stores)：以Json格式表達父/子資料階層。
3. 行導向資料庫(Column-oriented databases)：以欄位(Column)為儲存單位，取代以列(Row)儲存的傳統資料庫，有利於比對及搜尋。
4. 圖資料庫(Graph-based databases)：以節點(Node)及邊現或箭頭表示資料的關聯，適用網狀結構的資料關聯。
   相關討論可參閱【Types of NoSQL Database】。

NoSQL資料庫優點是可以很迅速的比對及搜尋到單一筆資料，但要依條件篩選多筆資料，效率就較差，為改善此缺點，可以設計索引或採取分散式儲存(Sharding)，NoSQL資料庫也需要良好的資料庫設計準則，主流的資料庫有MongoDB、Neo4j...等，設計準則可參照他們各自的文件。

結語

資料庫設計是開發應用程式必備的技能，另外，交易(Transaction)控制的機制也是撰寫程式要特別注意的事項，遇到效能不彰時，系統要如何調校(Tuning)，也都是程式設計師能力提升的重點。

下一篇我們討論網頁開發架構，並實作一個完整的網頁應用程式，包括路由(Routing)、視圖(View)...等尚未討論的部份。