淺談資料庫設計（关于树的设计等实例）http://www.omama.org/mt-archives/000007.php

說到資料庫，我認為不能不先談資料結構。在我初入大學學習電腦編程時，當時的老師就告訴我們說：電腦程式＝資料結構＋演算法。儘管現在的程式開發已由面向過程為主逐步過渡到面向物件為主，但我還是深深贊同8年前老師的告訴我們的公式：電腦程式＝資料結構＋演算法。面向物件的程式開發，要做的第一件事就是，先分析整個程式中需處理的資料，從中提取出抽象範本，以這個抽象範本設計類，再在其中逐步添加處理其資料的函數(即演算法)，最後，再給類中的資料成員 和函數劃分訪問許可權，從而實現封裝。
　　資料庫的最初雛形據說源自美國一個奶牛場的記賬薄(紙質的，由此可見，資料庫並不一定是存儲在電腦裏的資料^_^)，裏面記錄的是該奶牛場的收支賬目，程式師在將其整理、錄入到電腦中時從中受到啟發。當按照規定好的資料結構所採集到的資料量大到一定程度後，出於程式執行效率的考慮，程式師將其中的檢索、更新維護等功能分離出來，做成單獨調用的模組，這個模組後來就慢慢發展、演變成現在我們所接觸到的資料庫管理系統(DBMS)——程式開發中的一個重要分支。

下面進入正題，首先按我個人所接觸過的程式給資料庫設計人員的功底分一下類：
　　１、沒有系統學習過資料結構的程式師。這類程式師的作品往往只是他們的即興玩具，他們往往習慣只設計有限的幾個表，實現某類功能的資料全部塞在一個表中，各表之間幾乎毫無關聯。網上不少的免費管理軟體都是這樣的東西，當程式功能有限，資料量不多的時候，其程式運行起來沒有什麼問題，但是如果用其管理比較重要的資料，風險性非常大。
　　２、系統學習過資料結構，但是還沒有開發過對程式效率要求比較高的管理軟體的程式師。這類人多半剛從學校畢業不久，他們在設計資料庫表結構時，嚴格按照教科書上的規定，死扣E-R圖和3NF(別灰心，所有的資料庫設計高手都是從這一步開始的)。他們的作品，對於一般的access型羽量級的管理軟體，已經夠用。但是一旦該系統需要添加新功能，原有的資料庫表差不多得進行大換血。
　　３、第二類程式師，在經歷過數次程式效率的提升，以及功能升級的折騰後，終於升級成為資料庫設計的老鳥，第一類程式師眼中的高人。這類程式師可以勝任二十個表以上的中型商業資料管理系統的開發工作。他們知道該在什麼樣的情況下保留一定的冗餘數據來提高程式效率，而且其設計的資料庫可拓展性較好，當用戶需要添加新功能時，原有資料庫表只需做少量修改即可。
　　４、在經歷過上十個類似資料庫管理軟體的重複設計後，第三類程式師中堅持下來沒有轉行，而是希望從中找出“偷懶”竅門的有心人會慢慢覺悟，從而完成量變到質變的轉換。他們所設計的資料庫表結構有一定的遠見，能夠預測到未來功能升級所需要的資料，從而預先留下伏筆。這類程式師目前大多晉級成資料挖掘方面的高級軟體開發人員。
　　５、第三類程式師或第四類程式師，在對現有的各家資料庫管理系統的原理和開發都有一定的鑽研後，要麼在其基礎上進行二次開發，要麼自行開發一套有自主版權的通用資料庫管理系統。
　　我個人正處於第三類的末期，所以下面所列出的一些設計技巧只適合第二類和部分第三類資料庫設計人員。同時，由於我很少碰到有興趣在這方面深鑽下去的同行，所以文中難免出現錯誤和遺漏，在此先行聲明，歡迎大家指正，不要藏私哦8)
　　一、樹型關係的資料表
　　不少程式師在進行資料庫設計的時候都遇到過樹型關係的資料，例如常見的類別表，即一個大類，下面有若干個子類，某些子類又有子類這樣的情況。當類別不確定，用戶希望可以在任意類別下添加新的子類，或者刪除某個類別和其下的所有子類，而且預計以後其數量會逐步增長，此時我們就會考慮用一個資料表來保存這些資料。按照教科書上的教導，第二類程式師大概會設計出類似這樣的資料表結構：
類別表_1(Type_table_1)
名稱　　　　　類型　　　　約束條件　　　說明
type_id 　 int 　 無重複　　 類別標識，主鍵
type_name　　 char(50) 不允許為空 類型名稱，不允許重複
type_father int 不允許為空 該類別的父類別標識，如果是頂節點的話設定為某個唯一值
　　這樣的設計短小精悍，完全滿足3NF，而且可以滿足用戶的所有要求。是不是這樣就行呢？答案是NO！Why？
　　我們來估計一下用戶希望如何羅列出這個表的資料的。對用戶而言，他當然期望按他所設定的層次關係一次羅列出所有的類別，例如這樣：
總類別
　　類別1
　　　　類別1.1
　　　　　　類別1.1.1
　　　　類別1.2
　　類別2
　　　　類別2.1
　　類別3
　　　　類別3.1
　　　　類別3.2
　　……
　　看看為了實現這樣的列表顯示(樹的先序遍曆)，要對上面的表進行多少次檢索？注意，儘管類別1.1.1可能是在類別3.2之後添加的記錄，答案仍然是N次。這樣的效率對於少量的資料沒什麼影響，但是日後類型擴充到數十條甚至上百條記錄後，單單列一次類型就要檢索數十次該表，整個程式的運行效率就不敢恭維了。或許第二類程式師會說，那我再建一個臨時陣列或臨時表，專門保存類型表的先序遍曆結果，這樣只在第一次運行時檢索數十次，再次羅列所有的類型關係時就直接讀那個臨時陣列或臨時表就行了。其實，用不著再去分配一塊新的記憶體來保存這些資料，只要對資料表進行一定的擴充，再對添加類型的數量進行一下 約束就行了，要完成上面的列表只需一次檢索就行了。下面是擴充後的資料表結構：
類別表_2(Type_table_2)
名稱　　　　　類型　　　　約束條件　　　 說明
type_id 　 int 　 無重複　　 類別標識，主鍵
type_name　　 char(50) 不允許為空 類型名稱，不允許重複
type_father int 不允許為空 該類別的父類別標識，如果是頂節點的話設定為某個唯一值
type_layer char(6) 限定3層,初始值為000000 類別的先序遍曆，主要為減少檢索資料庫的次數
　　按照這樣的表結構，我們來看看上面例子記錄在表中的資料是怎樣的：
type_id type_name type_father type_layer
1 總類別 0 000000
2 類別1 1 010000
3 類別1.1 2 010100
4 類別1.2 2 010200
5 類別2 1 020000
6 類別2.1 5 020100
7 類別3 1 030000
8 類別3.1 7 030100
9 類別3.2 7 030200
10 類別1.1.1 3 010101
……
　　現在按type_layer的大小來檢索一下：SELECT * FROM Type_table_2 ORDER BY type_layer
列出記錄集如下：
type_id type_name type_father type_layer
1 總類別 0 000000
2 類別1 1 010000
3 類別1.1 2 010100
10 類別1.1.1 3 010101
4 類別1.2 2 010200
5 類別2 1 020000
6 類別2.1 5 020100
7 類別3 1 030000
8 類別3.1 7 030100
9 類別3.2 7 030200
……
　　現在列出的記錄順序正好是先序遍曆的結果。在控制顯示類別的層次時，只要對type_layer欄位中的數值進行判斷，每2位一組，如大於0則向右移2個空格。當然，我這個例子中設定的限制條件是最多3層，每層最多可設99個子類別，只要按用戶的需求情況修改一下type_layer的長度和位數，即可更改限制層數和子類別數。其實，上面的設計不單單只在類別表中用到，網上某些可按樹型列表顯示的論壇程式大多採用類似的設計。
　　或許有人認為，Type_table_2中的type_father欄位是冗餘數據，可以除去。如果這樣，在插入、刪除某個類別的時候，就得對 type_layer 的內容進行比較繁瑣的判定，所以我並沒有消去type_father欄位，這也正符合資料庫設計中適當保留冗餘數據的來降低程式複雜度的原則，後面我會舉一個故意增加資料冗餘的案例。
　　
　　二、商品資訊表的設計
　　假設你是一家百貨公司電腦部的開發人員，某天老闆要求你為公司開發一套網上電子商務平臺，該百貨公司有數千種商品出售，不過目前僅打算先在網上銷售數十種方便運輸的商品，當然，以後可能會陸續在該電子商務平臺上增加新的商品出售。現在開始進行該平臺資料庫的商品資訊表的設計。每種出售的商品都會有相同的屬性，如商品編號，商品名稱，商品所屬類別，相關資訊，供貨廠商，內含件數，庫存，進貨價，銷售價，優惠價。你很快就設計出4個表：商品類型表(Wares_type)，供貨廠商表(Wares_provider)，商品資訊表(Wares_info)：
商品類型表(Wares_type)
名稱　　　　　類型　　　　約束條件　　　 說明
type_id 　 int 　 無重複　　 類別標識，主鍵
type_name　　 char(50) 不允許為空 類型名稱，不允許重複
type_father int 不允許為空 該類別的父類別標識，如果是頂節點的話設定為某個唯一值
type_layer char(6) 限定3層,初始值為000000 類別的先序遍曆，主要為減少檢索資料庫的次數
供貨廠商表(Wares_provider)
名稱　　　　　類型　　　　約束條件　　　 說明
provider_id int 　 無重複　　 供應商標識，主鍵
provider_name char(100) 不允許為空 供應商名稱
商品資訊表(Wares_info)
名稱　　　　 類型　　　　約束條件　　　 說明
wares_id int 　 無重複　　 商品標識，主鍵
wares_name char(100) 不允許為空 商品名稱
wares_type　　 int 不允許為空　　　　　　　　　　 商品類型標識，和Wares_type.type_id關聯
wares_info char(200) 允許為空 相關資訊
provider int 不允許為空 供貨廠商標識，和Wares_provider.provider_id關聯
setnum int 初始值為1 內含件數，默認為1
stock int 初始值為0 庫存，默認為0
buy_price money 不允許為空 進貨價
sell_price money 不允許為空 銷售價
discount money 不允許為空 優惠價
　　你拿著這3個表給老闆檢查，老闆希望能夠再添加一個商品圖片的欄位，不過只有一部分商品有圖片。OK，你在商品資訊表(Wares_info)中增加了一個haspic的BOOL型欄位，然後再建了一個新表——商品圖片表(Wares_pic)：
商品圖片表(Wares_pic)
名稱　　　　 類型　　　　約束條件　　　 說明
pic_id int 　 無重複　　 商品圖片標識，主鍵
wares_id int 不允許為空 所屬商品標識，和Wares_info.wares_id關聯
pic_address　 char(200) 不允許為空　　　　　　　　　　 圖片存放路徑
　　程式開發完成後，完全滿足老闆目前的要求，於是正式啟用。一段時間後，老闆打算在這套平臺上推出新的商品銷售，其中，某類商品全部都需添加“長度”的屬性。第一輪折騰來了……當然，你按照添加商品圖片表的老方法，在商品資訊表(Wares_info)中增加了一個haslength的BOOL型欄位，又建了一個新表——商品長度表(Wares_length)：
商品長度表(Wares_length)
名稱　　　　 類型　　　　約束條件　　　 說明
length_id int 　 無重複　　 商品圖片標識，主鍵
wares_id int 不允許為空 所屬商品標識，和Wares_info.wares_id關聯
length　 char(20) 不允許為空　　　　　　　　　　 商品長度說明
　　剛剛改完沒多久，老闆又打算上一批新的商品，這次某類商品全部需要添加“寬度”的屬性。你咬了咬牙，又照方抓藥，添加了商品寬度表 (Wares_width)。又過了一段時間，老闆新上的商品中有一些需要添加“高度”的屬性，你是不是開始覺得你所設計的資料庫按照這種方式增長下去，很快就能變成一個迷宮呢？那麼，有沒有什麼辦法遏制這種不可預見性，但卻類似重複的資料庫膨脹呢？我在閱讀《敏捷軟體開發：原則、模式與實踐》中發現作者舉過類似的例子：7.3　“Copy”程式。其中，我非常贊同敏捷軟體開發這個觀點：在最初幾乎不進行預先設計，但是一旦需求發生變化，此時作為一名追求卓越的程式師，應該從頭審查整個架構設計，在此次修改中設計出能夠滿足日後類似修改的系統架構。下面是我在需要添加“長度”的屬性時所提供的修改方案：
　　去掉商品資訊表(Wares_info)中的haspic欄位，添加商品額外屬性表(Wares_ex_property)和商品額外資訊表(Wares_ex_info)2個表來完成添加新屬性的功能。
商品額外屬性表(Wares_ex_property)
名稱　　　　 類型　　　　約束條件　　　 說明
ex_pid int 　 無重複　　 商品額外屬性標識，主鍵
p_name char(20) 不允許為空 額外屬性名稱
商品額外資訊表(Wares_ex_info)
名稱　　　　 類型　　　　約束條件　　　 說明
ex_iid int 　 無重複　　 商品額外資訊標識，主鍵
wares_id int 不允許為空 所屬商品標識，和Wares_info.wares_id關聯
property_id　 int 不允許為空　　　　　　　　　　 商品額外屬性標識，和Wares_ex_property.ex_pid關聯
property_value char(200) 不允許為空 商品額外屬性值
　　在商品額外屬性表(Wares_ex_property)中添加2條記錄：
ex_pid p_name
1 商品圖片
2 商品長度
　　再在整個電子商務平 台的後臺管理功能中追加一項商品額外屬性管理的功能，以後添加新的商品時出現新的屬性，只需利用該功能往商品額外屬性表 (Wares_ex_property)中添加一條記錄即可。不要害怕變化，被第一顆子彈擊中並不是壞事，壞的是被相同軌道飛來的第二顆、第三顆子彈擊中。第一顆子彈來得越早，所受的傷越重，之後的抵抗力也越強