淺談資料庫設計技巧

說到資料庫，我認為不能不先談資料結構。1996年，在我初入大學學習計算機程式設計時，當時的老師就告訴我們說：計算機程式＝資料結構＋演算法。儘管現在的程式開發已由程式導向為主逐步過渡到物件導向為主，但我還是深深贊同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)中新增一條記錄即可。不要害怕變化，被第一顆子彈擊中並不是壞事，壞的是被相同軌道飛來的第二顆、第三顆子彈擊中。第一顆子彈來得越早，所受的傷越重，之後的抵抗力也越強8)(待續)

　　三、多使用者及其許可權管理的設計
　　開發資料庫管理類的軟體，不可能不考慮多使用者和使用者許可權設定的問題。儘管目前市面上的大、中型的後臺資料庫系統軟體都提供了多使用者，以及細至某個資料庫內某張表的許可權設定的功能，我個人建議：一套成熟的資料庫管理軟體，還是應該自行設計使用者管理這塊功能，原因有二：
　　1.那些大、中型後臺資料庫系統軟體所提供的多使用者及其許可權設定都是針對資料庫的共有屬性，並不一定能完全滿足某些特例的需求；
　　2.不要過多的依賴後臺資料庫系統軟體的某些特殊功能，多種大、中型後臺資料庫系統軟體之間並不完全相容。否則一旦日後需要轉換資料庫平臺或後臺資料庫系統軟體版本升級，之前的架構設計很可能無法重用。

　 　下面看看如何自行設計一套比較靈活的多使用者管理模組，即該資料庫管理軟體的系統管理員可以自行新增新使用者，修改已有使用者的許可權，刪除已有使用者。首先，分 析使用者需求，列出該資料庫管理軟體所有需要實現的功能；然後，根據一定的聯絡對這些功能進行分類，即把某類使用者需使用的功能歸為一類；最後開始建表：
　　
功能表(Function_table)
名稱　　　　　型別　　　　約束條件　　　說明
f_id          int    　   無重複　　   功能標識，主鍵
f_name        char(20)    不允許為空   功能名稱，不允許重複
f_desc        char(50)    允許為空     功能描述

使用者組表(User_group)
名稱　　　　　型別　　　　約束條件　　　說明
group_id      int         無重複        使用者組標識，主鍵
group_name    char(20)    不允許為空    使用者組名稱
group_power   char(100)   不允許為空    使用者組許可權表，內容為功能表f_id的集合

使用者表(User_table)
名稱　　　　　型別　　　　約束條件　　　說明
user_id       int         無重複        使用者標識，主鍵
user_name     char(20)    無重複        使用者名稱
user_pwd      char(20)    不允許為空    使用者密碼
user_type     int         不允許為空    所屬使用者組標識，和User_group.group_id關聯

　 　採用這種使用者組的架構設計，當需要新增新使用者時，只需指定新使用者所屬的使用者組；當以後系統需要新增新功能或對舊有功能許可權進行修改時，只用操作功能表和 使用者組表的記錄，原有使用者的功能即可相應隨之變化。當然，這種架構設計把資料庫管理軟體的功能判定移到了前臺，使得前臺開發相對複雜一些。但是，當使用者數 較大(10人以上)，或日後軟體升級的概率較大時，這個代價是值得的。

　　四、簡潔的批量m:n設計
　　碰到m:n的關係，一般都是建立3個表，m一個，n一個，m:n一個。但是，m:n有時會遇到批量處理的情況，例如到圖書館借書，一般都是允許使用者同時借閱n本書，如果要求按批查詢借閱記錄，即列出某個使用者某次借閱的所有書籍，該如何設計呢？讓我們建好必須的3個表先：

書籍表(Book_table)
名稱　　　　　型別　　　　約束條件　　　說明
book_id       int         無重複        書籍標識，主鍵
book_no       char(20)    無重複        書籍編號
book_name     char(100)   不允許為空    書籍名稱
……

借閱使用者表(Renter_table)
名稱　　　　　型別　　　　約束條件　　　說明
renter_id     int         無重複        使用者標識，主鍵
renter_name   char(20)    不允許為空    使用者姓名
……

借閱記錄表(Rent_log)
名稱　　　　　型別　　　　約束條件　　　說明
rent_id       int         無重複        借閱記錄標識，主鍵
r_id          int         不允許為空    使用者標識，和Renter_table.renter_id關聯
b_id          int         不允許為空    書籍標識，和Book_table.book_id關聯
rent_date     datetime    不允許為空    借閱時間
……

　　為了實現按批查詢借閱記錄，我們可以再建一個表來儲存批量借閱的資訊，例如：

批量借閱表(Batch_rent)
名稱　　　　　型別　　　　約束條件　　　說明
batch_id      int         無重複        批量借閱標識，主鍵
batch_no      int         不允許為空    批量借閱編號，同一批借閱的batch_no相同
rent_id       int         不允許為空    借閱記錄標識，和Rent_log.rent_id關聯
batch_date    datetime    不允許為空    批量借閱時間

　　這樣的設計好嗎？我們來看看為了列出某個使用者某次借閱的所有書籍，需要如何查詢？首先檢索批量借閱表(Batch_rent)，把符合條件的的所有記錄的rent_id欄位的資料儲存起來，再用這些資料作為查詢條件帶入到借閱記錄表(Rent_log)中去查詢。那麼，有沒有什麼辦法改進呢？下面給出一種簡潔的批量設計方案，不需新增新表，只需修改一下借閱記錄表(Rent_log)即可。修改後的記錄表(Rent_log)如下：

借閱記錄表(Rent_log)
名稱　　　　　型別　　　　約束條件　　　說明
rent_id       int         無重複        借閱記錄標識，主鍵
r_id          int         不允許為空    使用者標識，和Renter_table.renter_id關聯
b_id          int         不允許為空    書籍標識，和Book_table.book_id關聯
batch_no      int         不允許為空    批量借閱編號，同一批借閱的batch_no相同
rent_date     datetime    不允許為空    借閱時間
……

　　其中，同一次借閱的batch_no和該批第一條入庫的rent_id相同。舉例：假設當前最大rent_id是64，接著某使用者一次借閱了3本書，則批量插入的3條借閱記錄的batch_no都是65。之後另外一個使用者租了一套碟，再插入出租記錄的rent_id是68。採用這種設計，查詢批量借閱的資訊時，只需使用一條標準T_SQL的巢狀查詢即可。當然，這種設計不符合3NF，但是和上面標準的3NF設計比起來，哪一種更好呢？答案就不用我說了吧。

　　五、冗餘資料的取捨
　　上篇的“樹型關係的資料表”中保留了一個冗餘欄位，這裡的例子更進一步——添 加了一個冗餘表。先看看例子：我原先所在的公司為了解決員工的工作餐，和附近的一家小餐館聯絡，每天吃飯記賬，費用按人數平攤，月底由公司現金結算，每個 人每個月的工作餐費從工資中扣除。當然，每天吃飯的人員和人數都不是固定的，而且，由於每頓工作餐的所點的菜色不同，每頓的花費也不相同。例如，星期一中 餐5人花費40元，晚餐2人花費20，星期二中餐6人花費36元，晚餐3人花費18元。為了方便計算每個人每個月的工作餐費，我寫了一個簡陋的就餐記賬管理程式，資料庫裡有3個表：

員工表(Clerk_table)
名稱　　　　　型別　　　　約束條件　　　說明
clerk_id      int         無重複        員工標識，主鍵
clerk_name    char(10)    不允許為空    員工姓名

每餐總表(Eatdata1)
名稱　　　　　型別　　　　約束條件　　　說明
totle_id      int         無重複        每餐總表標識，主鍵
persons       char(100)   不允許為空    就餐員工的員工標識集合
eat_date      datetime    不允許為空    就餐日期
eat_type      char(1)     不允許為空    就餐型別，用來區分中、晚餐
totle_price   money       不允許為空    每餐總花費
persons_num   int         不允許為空    就餐人數

就餐計費細表(Eatdata2)
名稱　　　　　型別　　　　約束條件　　　說明
id            int         無重複        就餐計費細表標識，主鍵
t_id          int         不允許為空    每餐總表標識，和Eatdata1.totle_id關聯
c_id          int         不允許為空    員工標識標識，和Clerk_table.clerk_id關聯
price         money       不允許為空    每人每餐花費

　　其中，就餐計費細表(Eatdata2)的記錄就是把每餐總表(Eatdata1)的一條記錄按就餐員工平攤拆開，是個不折不扣的冗餘表。當然，也可以把每餐總表(Eatdata1)的部分欄位合併到就餐計費細表(Eatdata2)中，這樣每餐總表(Eatdata1)就成了冗餘表，不過這樣所設計出來的就餐計費細表重複資料更多，相比來說還是上面的方案好些。但是，就是就餐計費細表(Eatdata2)這個冗餘表，在做每月每人餐費統計的時候，大大簡化了程式設計的複雜度，只用類似這麼一條查詢語句即可統計出每人每月的寄餐次數和餐費總帳：

SELECT clerk_name AS personname,COUNT(c_id) as eattimes,SUM(price) AS ptprice FROM Eatdata2 JOIN Clerk_tabsle ON (c_id=clerk_id) JOIN eatdata1 ON (totleid=tid) WHERE eat_date>=CONVERT(datetime,'"&the_date&"') AND eat_date<DATEADD(month,1,CONVERT(datetime,'"&the_date&"')) GROUP BY c_id

　　想象一下，如果不用這個冗餘表，每次統計每人每月的餐費總帳時會多麻煩，程式效率也夠嗆。那麼，到底什麼時候可以增加一定的冗餘資料呢？我認為有2個原則：

　 　１、使用者的整體需求。當使用者更多的關注於，對資料庫的規範記錄按一定的演算法進行處理後，再列出的資料。如果該演算法可以直接利用後臺資料庫系統的內嵌函式 來完成，此時可以適當的增加冗餘欄位，甚至冗餘表來儲存這些經過演算法處理後的資料。要知道，對於大批量資料的查詢，修改或刪除，後臺資料庫系統的效率遠遠 高於我們自己編寫的程式碼。
　 　２、簡化開發的複雜度。現代軟體開發，實現同樣的功能，方法有很多。儘管不必要求程式設計師精通絕大部分的開發工具和平臺，但是還是需要了解哪種方法搭配哪 種開發工具的程式更簡潔，效率更高一些。冗餘資料的本質就是用空間換時間，尤其是目前硬體的發展遠遠高於軟體，所以適當的冗餘是可以接受的。不過我還是在 最後再強調一下：不要過多的依賴平臺和開發工具的特性來簡化開發，這個度要是沒把握好的話，後期維護升級會栽大跟頭的。