收集常用數(shù)據(jù)庫設(shè)計技巧

1、收集常用數(shù)據(jù)庫設(shè)計技巧一、樹型關(guān)系的數(shù)據(jù)表不少程序員在進(jìn)行數(shù)據(jù)庫設(shè)計的時候都遇到過樹型關(guān)系的數(shù)據(jù)，例如常見 的類別表，即一個大類，下面有若干個子類，某些子類又有子類這樣的情況。當(dāng)類別不確定，用戶希望可以在任意類別下添加新的子類，或者刪除某個類別 和其下的所有子類，而且預(yù)計以后其數(shù)量會逐步增長，此時我們就會考慮用一個數(shù)據(jù)表來保存這些數(shù)據(jù)。按照教科書上的教導(dǎo)，第二類程序員大概會設(shè)計出 類似這樣的數(shù)據(jù)表結(jié)構(gòu)：類別表 _1(Type_table_1)名稱類型約束條件說明type_idinttype_ namechar(50)type_father int無重復(fù)類別標(biāo)識，主鍵不允許為空類型名稱，不允許重

2、復(fù)不允許為空該類別的父類別標(biāo)識,如果是頂節(jié)點(diǎn)的話設(shè)定為某個唯一值這樣的設(shè)計短小精悍，完全滿足3NF,而且可以滿足用戶的所有要求是不是這樣就行呢？答案是NQ Why我們來估計一下用戶希望如何羅列出這個表的數(shù)據(jù)的。對用戶而言，他當(dāng)然期望按他所設(shè)定的層次關(guān)系一次羅列出所有的類別，例如這樣： 總類別類別1類別1.1類別1.1.1類別1.2類別2類別2.1類別3類別3.1看看為了實(shí)現(xiàn)這樣的列表顯示（樹的先序遍歷），要對上面的表進(jìn)行多 少次檢索？注意，盡管類別1.1.1可能是在類別3.2之后添加的記錄，答案 仍 然是N次。這樣的效率對于少量的數(shù)據(jù)沒什么影響，但是日后類型擴(kuò)充到數(shù)十 條甚至上百條記錄后，單單

3、列一次類型就要檢索數(shù)十次該表，整個程序的運(yùn)行 效率就 不敢恭維了。或許第二類程序員會說，那我再建一個臨時數(shù)組或臨時表， 專門保存類型表的先序遍歷結(jié)果，這樣只在第一次運(yùn)行時檢索數(shù)十次，再次羅 列所有的類型 關(guān)系時就直接讀那個臨時數(shù)組或臨時表就行了。其實(shí)，用不著再 去分配一塊新的內(nèi)存來保存這些數(shù)據(jù)，只要對數(shù)據(jù)表進(jìn)行一定的擴(kuò)充，再對添 加類型的數(shù)量進(jìn)行一下約束就行了，要完成上面的列表只需一次檢索就行了。 下面是擴(kuò)充后的數(shù)據(jù)表結(jié)構(gòu)：類別表 _2(Type_table_2)名稱類型件type_idint復(fù)type_ namechar(50)空type_father int空點(diǎn)的話設(shè)定為某個唯一值type

4、_layerchar(6)約束條說明無重類別標(biāo)識，主鍵不允許為類型名稱，不允許重復(fù)不允許為該類別的父類別標(biāo)識，如果是頂節(jié)限定3層，初始值為000000類別的先序遍歷，主要為減少檢索數(shù)據(jù)庫的次數(shù)type_idtype_ nametype_father按照這樣的表結(jié)構(gòu)，我們來看看上面例子記錄在表中的數(shù)據(jù)是怎樣的:type_layer總類0000現(xiàn)在按 type_layer的大小來檢索一下:SELECT * FROM Type_table_2002類別110100003類別1.120101004類別1.220102005類別210200006類別2.150201007類別310300008類別3.1

5、70301009類別3.2703020010類別1.1.13010101ORDER BY type_layer列出記錄集如下:type_layer總類0 0000類別1 010類別2 01010類別3 010101類別2 01020類別1 020類別502010類別1030類別7030101別002100031.1005200062.107300083.1093.20類別現(xiàn)在列出的記錄順序正好是先序遍歷的結(jié)果。在控制顯示類別的層次 時，只要對type_layer字段中的數(shù)值進(jìn)行判斷，每2位一組，如大于0則向 右移2個空格。當(dāng)然，我這個例子中設(shè)定的限制條件是最多3層，每層最

6、多可設(shè)99個子類別，只要按用戶的需求情況修改一下type_layer的長度和位 數(shù)，即可更改限制層數(shù)和子類別數(shù)。其實(shí)，上面的設(shè)計不單單只在類別表中用到， 網(wǎng)上某些可按樹型列表顯示的論壇程序大多采用類似的設(shè)計。或許有人認(rèn)為，Type_table_2中的type_father字段是冗余數(shù)據(jù)，可 以除去。如果這樣，在插入、刪除某個類別的時候，就得對 type_layer 的內(nèi) 容進(jìn)行比較繁瑣的判定，所以我并沒有消去type_father字段，這也正符合數(shù)據(jù)庫設(shè)計中適當(dāng)保留冗余數(shù)據(jù)的來降低程序復(fù)雜度的原則，后面我會舉一個故意增加數(shù)據(jù)冗余的案例。二、商品信息表的設(shè)計假設(shè)你是一家百貨公司電腦部的開發(fā)人員，

7、某天老板要求你為公司開發(fā)一套網(wǎng)上電子商務(wù)平臺，該百貨公司有數(shù)千種商品出售，不過目前僅打算先在網(wǎng)上銷售數(shù)十種方便運(yùn)輸?shù)纳唐?，?dāng)然，以后可能會陸續(xù)在該電子商務(wù)平臺上增 加新的商品出售?，F(xiàn)在開始進(jìn)行該平臺數(shù)據(jù)庫的商品信息表的設(shè)計。每種出售的商品都會有相同的屬性，如商品編號，商品名稱，商品所屬類別，相關(guān)信息， 供貨廠商，內(nèi)含件數(shù)，庫存，進(jìn)貨價，銷售價，優(yōu)惠價。你很快就設(shè)計出4個表：商品類型表(Wares_type)，供貨廠商表(Wares_provider)，商品信息表 (Waresnfo):商品類型表(Wares_type)名稱類型約束條件說明type_idint無重復(fù)類別標(biāo)識，主鍵type_ na

8、mechar(50)不允許為空類型名稱，不允許重復(fù)type_fatherint不允許為空該類別的父類別標(biāo)識，如果是頂節(jié)點(diǎn)的話設(shè)定為某個唯一值type_layer000000char限定3層，初始值為類別的先序遍歷，主要為減少檢索數(shù)據(jù)庫的次數(shù)約束條說明無重供貨商標(biāo)識，主鍵 不允許為供貨商名稱供貨廠商表(Wares_provider)名稱類型件provider_idint復(fù)provider, name char(100)空商品信息表(Waresnfo)名稱類型約束條件說明wares_idint無重復(fù)商品標(biāo)識,主鍵wares_ namechar(100)不允許為空商品名稱wares_typeint不

9、允許為空商品類型標(biāo)識，和Wares_type.type_id關(guān)聯(lián)waresnfochar(200)允許為空相關(guān)信息providerint不允許為空供貨廠商標(biāo)識，和Wares_vider_id 關(guān)聯(lián)set numint初始值為1內(nèi)含件數(shù)，默認(rèn)為1stockint初始值為0庫存，默認(rèn)為0buy_pricemoney不允許為空進(jìn)貨價sell_pricemoney不允許為空銷售價優(yōu)惠價你拿著這3個表給老板檢查，老板希望能夠再添加一個商品圖片的字段，不過只有一部分商品有圖片。0K你在商品信息表(Waresnfo)中增加了一個haspic的B00型字段，然后再建了一個新表商品圖片表

10、 (Wares_pic):商品圖片表(Wares_pic)名稱類型約束條件說明pic_idint無重復(fù)商品圖片標(biāo)識，主鍵wares_idint不允許為空所屬商品標(biāo)識，和Waresnfo.wares_id 關(guān)聯(lián)pic_addresschar(200)不允許為空圖片存放路徑程序開發(fā)完成后，完全滿足老板目前的要求，于是正式啟用。一段時 間后，老板打算在這套平臺上推出新的商品銷售，其中，某類商品全部都需添 加“長 度”的屬性。第一輪折騰來了當(dāng)然，你按照添加商品圖片表的老方 法，在商品信息表(Waresnfo)中增加了一個haslength的BOO型字段，又 建了一個新表商品長度表(Wares_leng

11、th):商品長度表(Wares_length)名稱類型約束條件說明len gthdint無重復(fù)商品圖片標(biāo)識，主鍵wares_idint不允許為空所屬商品標(biāo)識，和Wares info.wares id 關(guān)聯(lián)len gth長度說明char(20)不允許為空商品剛剛改完沒多久，老板又打算上一批新的商品，這次某類商品全部需要添加“寬度”的屬性。你咬了咬牙，又照方抓藥，添加了商品寬度表 (Wares_width)。又過了一段時間，老板新上的商品中有一些需要添加“高度” 的屬性，你是不是開始覺得你所設(shè)計的數(shù)據(jù)庫按照這種方式增長下去，很快就能變成一個迷宮呢？那么，有沒有什么辦法遏制這種不可預(yù)見性，但卻類似重

12、 復(fù)的數(shù)據(jù)庫膨脹呢？我在閱讀敏捷軟件開發(fā)：原則、模式與實(shí)踐中發(fā)現(xiàn)作 者舉過類似的例子：7.3“Copy”程序。其中，我非常贊同敏捷軟件開發(fā)這個觀點(diǎn)：在最初幾乎不進(jìn)行預(yù)先設(shè)計，但是一旦需求發(fā)生變化，此時作為一名 追求卓越的程序員，應(yīng)該從頭審查整個架構(gòu)設(shè)計，在此次修改中設(shè)計出能夠滿 足日后類似修改的系統(tǒng)架構(gòu)。下面是我在需要添加“長度”的屬性時所提供的 修改方案：去掉商品信息表(Waresnfo)中的haspic字段，添加商品額外屬性表 (Wares_ex_property)和商品額外信息表(Wares_ex_info)2個表來完成添加新 屬性的功能。商品額外屬性表(Wares_ex_propert

13、y)名稱類型約束條件說明ex_pidint無重復(fù)商品額外屬性標(biāo)識，主鍵p_n amechar(20)不允許為空額外屬性名稱商品額外信息表(Wares_ex_info)名稱類型約束條件說明ex_iidint無重復(fù)商品額外信息標(biāo)識，主鍵wares_idint不允許為空所屬商品標(biāo)識，和Waresnfo.wares_id 關(guān)聯(lián)property_idint不允許為空商品額外屬性標(biāo)識，和 Wares_ex_property.ex_pid 關(guān)聯(lián) property_value char(200)不允許為空商品額外屬性值在商品額外屬性表(Wares_ex_property)中添加2條記錄：ex_pidp_n a

14、me1 商品圖片2 商品長度再在整個電子商務(wù)平臺的后臺管理功能中追加一項(xiàng)商品額外屬性管理 的功能，以后添加新的商品時出現(xiàn)新的屬性，只需利用該功能往商品額外屬性 表(Wares_ex_property)中添加一條記錄即可。不要害怕變化，被第一顆子彈 擊中并不是壞事，壞的是被相同軌道飛來的第二顆、第三顆子彈擊中。第一顆子彈來得越早，所受的傷越重，之后的抵抗力也越強(qiáng) 8)(待續(xù))三、多用戶及其權(quán)限管理的設(shè)計開發(fā)數(shù)據(jù)庫管理類的軟件，不可能不考慮多用戶和用戶權(quán)限設(shè)置的問題。 盡管目前市面上的大、中型的后臺數(shù)據(jù)庫系統(tǒng)軟件都提供了多用戶，以及細(xì)至 某個數(shù)據(jù)庫內(nèi)某張表的權(quán)限設(shè)置的功能，我個人建議：一套成熟的數(shù)

15、據(jù)庫管理 軟件，還是應(yīng)該自行設(shè)計用戶管理這塊功能，原因有二：1. 那些大、中型后臺數(shù)據(jù)庫系統(tǒng)軟件所提供的多用戶及其權(quán)限設(shè)置都是針 對數(shù)據(jù)庫的共有屬性，并不一定能完全滿足某些特例的需求；2. 不要過多的依賴后臺數(shù)據(jù)庫系統(tǒng)軟件的某些特殊功能，多種大、中型后 臺數(shù)據(jù)庫系統(tǒng)軟件之間并不完全兼容。否則一旦日后需要轉(zhuǎn)換數(shù)據(jù)庫平臺或后 臺數(shù)據(jù)庫系統(tǒng)軟件版本升級，之前的架構(gòu)設(shè)計很可能無法重用。下面看看如何自行設(shè)計一套比較靈活的多用戶管理模塊，即該數(shù)據(jù)庫 管理軟件的系統(tǒng)管理員可以自行添加新用戶，修改已有用戶的權(quán)限，刪除已有 用戶。首先，分析用戶需求，列出該數(shù)據(jù)庫管理軟件所有需要實(shí)現(xiàn)的功能；然 后，根據(jù)一定的聯(lián)系

16、對這些功能進(jìn)行分類，即把某類用戶需使用的功能歸為一類；最后開始建表:名稱類型約束條件說明f_idint無重復(fù)功能標(biāo)識，主鍵f_n amechar(20)不允許為空功能名稱，不允許重復(fù)f_descchar(50)允許為空功能描述功能表(Fu nction_table)用戶組表(User_group)名稱類型group_idint識，主鍵group_ namechar(20)group_powerchar(100)能表f_id的集合用戶表(User_table)名稱類型user_idint主鍵約束條件說明無重復(fù)不允許為空不允許為空約束條件說明無重復(fù)用戶組標(biāo)用戶組名稱用戶組權(quán)限表，內(nèi)容為功user_

17、 namechar(20)無重復(fù)user_pwdchar(20)不允許為空user_typeint不允許為空用戶標(biāo)識,用戶名用戶密碼所屬用戶組標(biāo)識，和 User_group.group_id 關(guān)聯(lián)采用這種用戶組的架構(gòu)設(shè)計，當(dāng)需要添加新用戶時，只需指定新用戶 所屬的用戶組；當(dāng)以后系統(tǒng)需要添加新功能或?qū)εf有功能權(quán)限進(jìn)行修改時，只 用操作 功能表和用戶組表的記錄，原有用戶的功能即可相應(yīng)隨之變化。當(dāng)然， 這種架構(gòu)設(shè)計把數(shù)據(jù)庫管理軟件的功能判定移到了前臺，使得前臺開發(fā)相對復(fù) 雜一些。但是， 當(dāng)用戶數(shù)較大（10人以上），或日后軟件升級的概率較大時, 這個代價是值得的。四、簡潔的批量m:n設(shè)計碰到m:n的關(guān)

18、系，一般都是建立 3個表，m 個，n個，m:n 個。但是, m:n有時會遇到批 量處理的情況，例如到圖書館借書，一般都是允許用戶同時 借閱n本書，如果要求按批查詢借閱記錄，即列出某個用戶某次借閱的所有書 籍，該如何設(shè)計呢？讓我們 建好必須的3個表先：書籍表(Book_table)名稱類型約束條件說明book_idint無重復(fù)書籍標(biāo)識,主鍵book_ nochar(20)無重復(fù)書籍編號book_ namechar(100)不允許為空書籍名稱借閱用戶表(Renter_table)名稱類型約束條件說明ren ter_idint無重復(fù)用戶標(biāo)識,主鍵ren ter_name char(20)不允許為空用

19、戶姓名借閱記錄表(Rent_log)名稱類型約束條件說明rent_idint無重復(fù)借閱記錄標(biāo)識，主鍵r_idint不允許為空用戶標(biāo)識,和 Renter_table.renter_id關(guān)聯(lián)b_idint不允許為空書籍標(biāo)識,和 Book_table.book_id關(guān)聯(lián)ren t_datedatetime不允許為空借閱時間為了實(shí)現(xiàn)按批查詢借閱記錄，我們可以再建一個表來保存批量借閱的 信息，例如：批量借閱表(Batch_rent)名稱類型約束條件說明batch idint無重復(fù)批量借閱標(biāo)識，主鍵batch noint不允許為空批量借閱編號,同一批借閱的batch no相同rent idint不允許為空

20、借閱記錄標(biāo)識,和 Rent_log.rent_id關(guān)聯(lián)batch datedatetime不允許為空批量借閱時間這樣的設(shè)計好嗎？我們來看看為了列出某個用戶某次借閱的所有書籍， 需要如何查詢？首先檢索批量借閱表(Batch_re nt)，把符合條件的的所有記錄 的rent_id字段的數(shù)據(jù)保存起來，再用這些數(shù)據(jù)作為查詢條件帶入到借閱記錄 表(Rent_log)中去查詢。那么，有沒有什么辦法改進(jìn)呢？下面給出一種簡潔的批量設(shè)計方案，不需添加新表，只需修改一下借閱記錄表(Rent_log)即可。修改后的記錄表(Rent_log)如下：借閱記錄表(Rent_log)名稱類型約束條件說明rent_idint

21、無重復(fù)借閱記錄標(biāo)識，主鍵r_idint不允許為空用戶標(biāo)識,和 Renter_table.renter_id關(guān)聯(lián)b_idint不允許為空書籍標(biāo)識,和 Book_table.book_id關(guān)聯(lián)batch_ noint不允許為空批量借閱編號,同一批借閱的batch_no相同ren t_datedatetime不允許為空借閱時間其中，同一次借閱的batch_no和該批第一條入庫的rent_id相同。舉 例：假設(shè)當(dāng)前最大rent_id是64,接著某用戶一次借閱了 3本書，則批量插入 的3條借閱記錄的batch_no都是65。之后另外一個用戶租了一套碟，再插入 出租記錄的rent_id是68。采用這種設(shè)計

22、，查詢批 量借閱的信息時，只需使 用一條標(biāo)準(zhǔn)T_SQL的嵌套查詢即可。當(dāng)然，這種設(shè)計不符合3NF,但是和上面標(biāo)準(zhǔn)的3NF設(shè)計比起來，哪一種更好呢？答案就不用 我說了吧。五、冗余數(shù)據(jù)的取舍上篇的“樹型關(guān)系的數(shù)據(jù)表”中保留了一個冗余字段，這里的例子更進(jìn)一 步一一添加了一個冗余表。先看看例子：我原先所在的公司為了解決員工的工作餐，和附近的一家小餐館聯(lián)系，每天吃飯記賬，費(fèi)用按人數(shù)平攤，月底由公 司現(xiàn)金結(jié)算，每個人每個月的工作餐費(fèi)從工資中扣除。當(dāng)然，每天吃飯的人員和人數(shù)都不是固定的，而且，由于每頓工作餐的所點(diǎn)的菜色不同，每頓的花費(fèi) 也不相同。例如，星期一中餐 5人花費(fèi)40元，晚餐2人花費(fèi)20，星期二中餐

23、 6人花費(fèi)36元，晚餐3人花費(fèi)18元。為了方便計算每個人每個月的工作餐費(fèi)， 我寫了一個簡陋的就餐記賬管理程序，數(shù)據(jù)庫里有3個表：員工表(Clerk_table)名稱類型約束條件說明clerk_idint無重復(fù)員工標(biāo)識,主鍵clerk_ namechar(10)不允許為空員工姓名每餐總表(Eatdata1)名稱類型約束條件說明totle_idint無重復(fù)每餐總表標(biāo)識，主鍵pers onschar(100)不允許為空就餐員工的員工標(biāo)識集合eat_datedatetime不允許為空就餐日期eat_typechar(1)不允許為空就餐類型，用來區(qū)分中、晚餐totle_pricepers ons_num

24、intmoney不允許為空每餐總花費(fèi)不允許為空就餐人數(shù)就餐計費(fèi)細(xì)表(Eatdata2)名稱類型約束條件說明idint無重復(fù)就餐計費(fèi)細(xì)表標(biāo)識，主鍵t_idint不允許為空每餐總表標(biāo)識，和 Eatdata1.totle_id關(guān)聯(lián)c_idint不允許為空員工標(biāo)識標(biāo)識，和 Clerk_table.clerk_id關(guān)聯(lián)pricemoney不允許為空每人每餐花費(fèi)其中，就餐計費(fèi)細(xì)表(Eatdata2)的記錄就是把每餐總表(Eatdatal)的 一條記錄按就餐員工平攤拆開，是個不折不扣的冗余表。當(dāng)然，也可以把每餐總表(Eatdatal)的部分字段合并到就餐計費(fèi)細(xì)表(Eatdata2)中，這樣每餐總表 (Eatdatal)就成了冗余表，不過這 樣所設(shè)計出來的就餐計費(fèi)細(xì)表重復(fù)數(shù)據(jù)更多， 相比來說還是上面的方案好些。但是，就是就餐計費(fèi)細(xì)表(Eatdata2)這個冗余表，在做每月每人餐費(fèi)統(tǒng)計的時候，大大簡化了編程的復(fù)雜度，只用類似這么一條查詢語句即可統(tǒng)計出