數(shù)據(jù)庫設(shè)計規(guī)范

設(shè)計標(biāo)準(zhǔn)文檔設(shè)計標(biāo)準(zhǔn)文檔文檔ID：XIU-DB-01數(shù)據(jù)庫設(shè)計標(biāo)準(zhǔn)版本號1.0

修訂歷史記錄日期版本說明作者

目錄TOC\o"1-3"\h\z修訂歷史記錄 2目錄 3數(shù)據(jù)庫設(shè)計指南 41.概述 4目的 4范圍 42.設(shè)計標(biāo)準(zhǔn) 4表 4字段設(shè)計原那么 4域 6鍵設(shè)計原那么 7索引設(shè)計原那么 7什么是索引 7使用索引的目的 8何時使用索引 8索引的種類 8數(shù)據(jù)完整性 8實體完整性 8域完整性 8引用完整性 8用戶定義完整性 9關(guān)系設(shè)計原那么 92一對一關(guān)系 9一對多關(guān)系 9多對多關(guān)系 93.命名標(biāo)準(zhǔn) 10表 10視圖 10域〔Domains〕 10字段 10索引 11存儲過程 11觸發(fā)器 11主鍵命名 11業(yè)務(wù)規(guī)那么 11約束 11其他 11

數(shù)據(jù)庫設(shè)計指南概述目的本文檔用于指導(dǎo)系統(tǒng)設(shè)計員進行數(shù)據(jù)庫設(shè)計，標(biāo)準(zhǔn)數(shù)據(jù)庫設(shè)計文檔，提出工件的評審標(biāo)準(zhǔn)。范圍適用于產(chǎn)品與技術(shù)中心所有工程/產(chǎn)品的數(shù)據(jù)庫設(shè)計。設(shè)計標(biāo)準(zhǔn)表為在性能、擴展性和數(shù)據(jù)完整性方面到達了最好平衡，表的設(shè)計應(yīng)該滿足第三范式〔3NF〕，即：表內(nèi)的每一個值都只能被表達一次。表內(nèi)的每一行都應(yīng)該被唯一的標(biāo)識〔有唯一鍵〕。表內(nèi)不應(yīng)該存儲傳遞依賴于主鍵的非鍵信息。實際使用中考慮到編碼效率、執(zhí)行效率等問題，往往需要對表的設(shè)計進行反標(biāo)準(zhǔn)化，通常是對表進行合并，以下是幾種常見的合并情況：根底代碼表的合并。當(dāng)根底代碼表的數(shù)據(jù)行數(shù)不多時，可考慮把多個表進行合并，使用標(biāo)記項區(qū)分?jǐn)?shù)據(jù)，并對標(biāo)記項進行聚集索引，在物理上把數(shù)據(jù)放在一起，可以提高執(zhí)行效率，例如：職稱、文化程度、職務(wù)等就可以合并在一起。一對一表的合并。增加數(shù)據(jù)冗余，可以提高執(zhí)行效率。數(shù)據(jù)庫查詢的瓶頸是多表聯(lián)合查詢，如果數(shù)目增大，運行性能就顯著下降。把所以列放在一個表，只要有適當(dāng)?shù)淖侄嗡饕?，其速度就很快。?dāng)然表格的嚴(yán)謹(jǐn)性那么下降。普遍和個別〔類似于面向?qū)ο笾械母割惡妥宇悺车暮喜?，如采購合同、銷售合同可以合并在一起放到根本的合同表中，這樣可以大大提高數(shù)據(jù)處理性能。不把數(shù)據(jù)表的非標(biāo)準(zhǔn)化當(dāng)作當(dāng)然的設(shè)計理念。而具體的操作不過是一種派生。所以如果表出了問題重新產(chǎn)生非標(biāo)準(zhǔn)化的表是完全可能的。字段設(shè)計原那么字段應(yīng)該保持全局一致性，即無論一個字段在哪個表出現(xiàn)，其名稱、類型等都應(yīng)該保持不變，如定單表Order中有一個OrderID，那么定單細目表OrderItem中在引用時應(yīng)該還命名為OrderID并保持類型不變字段的數(shù)據(jù)類型應(yīng)使用標(biāo)準(zhǔn)〔抽象〕數(shù)據(jù)類型來定義，以使其含義不受目標(biāo)數(shù)據(jù)庫數(shù)據(jù)類型差異的影響，便于溝通理解，盡量使用域〔Domains〕來定義字段的類型，以便于保持一致性和提高可維護性。在選擇數(shù)據(jù)類型時應(yīng)盡量使用標(biāo)準(zhǔn)數(shù)據(jù)類型，而不使用個別數(shù)據(jù)庫系統(tǒng)提供的個性化類型，以保持?jǐn)?shù)據(jù)庫定義具有良好的可移植性。考慮到目前物理數(shù)據(jù)模型的設(shè)計都采用PD，為了保持?jǐn)?shù)據(jù)類型命名的獨立性，在PD環(huán)境下定義了一個抽象的目標(biāo)數(shù)據(jù)庫AbstractDB，并定義了標(biāo)準(zhǔn)〔抽象〕的數(shù)據(jù)類型，物理數(shù)據(jù)模型應(yīng)在AbstractDB下進行設(shè)計，然后再生成實際的目標(biāo)數(shù)據(jù)庫〔Sqlserver、Oracle等〕的創(chuàng)立腳本。標(biāo)準(zhǔn)數(shù)據(jù)類型定義表2-1：表2-1標(biāo)準(zhǔn)數(shù)據(jù)類型定義表類型說明使用建議Char(n)固定長度的非Unicode字符數(shù)據(jù)，在MSSqlServer中，最大長度為8,000個字符。當(dāng)字段長度差異不是很明顯時使用，如郵政編碼、姓名等varchar(n)類似Char(n)，但長度可變當(dāng)字段長度較大〔>50〕，表的記錄數(shù)很多，且長短差異較大時，由于可以顯著降低磁盤空間占用，所以推薦使用，如備注，多數(shù)情況少于200字節(jié)，個別情況下可能會到達1000字節(jié)。int1616位帶符號整數(shù)從-2^15(-32,768)到2^15-1(32,767)的整數(shù)數(shù)據(jù)。int3232位帶符號整數(shù)從-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型數(shù)據(jù)〔所有數(shù)字〕。某些情況可作為主鍵Decimal十進制數(shù),默認(rèn)長度最大位數(shù)38位，有效值從-10^38+1到10^38-1。精度可調(diào)，當(dāng)對準(zhǔn)確度要求較高時使用，如金額、單價等財務(wù)數(shù)據(jù)。decimal(n)總長度為n的十進制數(shù)decimal(p,s)總長度為p，小數(shù)位數(shù)為s的十進制數(shù)Single單精度浮點數(shù)，長度4字節(jié)有效數(shù)字7位，從-3.40E+38到3.40E+38之間的浮點數(shù)字。存儲大小為4字節(jié)。在SQLServer中，其他見doubleDouble雙精度浮點數(shù)，長度8字節(jié)15位有效數(shù)字，從-1.79E+308到1.79E+308之間的浮點數(shù)字。當(dāng)對準(zhǔn)確度要求不是很高時可以使用，如工程計算、圖形計算等Datetime日期時間型，長度8字節(jié)從1753年1月1日到9999年12月31日的日期和時間數(shù)據(jù)，精確度為百分之三秒〔等于3.33毫秒或0.00333秒〕。Timestamp時間戳，長度為8字節(jié)的二進制數(shù)數(shù)據(jù)庫范圍的唯一數(shù)字，一個表只能有一個timestamp列，每次更新行時自動對該類型字段進行更新，所以在多用戶環(huán)境下該類型的字段可以用于檢查更新沖突。Boolean布爾型，不同數(shù)據(jù)庫存儲策略不同，在MSSqlServer中會將每8個布爾型字段保存到一個字節(jié)中，每個字段占1個Bit。存儲真/假，是/否等邏輯值，不能對該類型的字段使用索引longbinary長二進制字符串，可變長度二進制數(shù)據(jù)，介于0與231-1(2,147,483,647)字節(jié)之間?？杀４鎴D象、視頻、音頻、OLE對象等大的二進制數(shù)據(jù)longtext長文本字符串，可變長度非Unicode數(shù)據(jù)的最大長度為231-1(2,147,483,647)個字節(jié)。可保存大的純文本數(shù)據(jù)。表2-2數(shù)據(jù)類型對照表標(biāo)準(zhǔn)類型MSSql7/2000Oracle8Access2000Char(n)Char(8000)Char(2000)Char(255)Text(255)varchar(n)Varchar(8000)Varchar(4000)Varchar(4000)longtextint16SmallintSmallintsmallintSmallintint32IntIntegerintegerIntegerDecimaldecimalnumericNumberDecimalnumericdecimal(n)DecimalnumericNumber(m)Decimal(n)Numericdecimal(p,s)decimalnumericNumber(m,n)Decimal(p,s)NumericSingleRealFloatRealSingleDoubleFloatFloatDoubleprecisionDoubleDatetimeDatetimeDateDateDatetimeTimestampTimestampTimestampTimestampBooleanBitSmallintSmallintYesnobinary(n)binary(8000)binaryvarbinary(n)varbinary(8000)Row(2000)binarylongbinaryImageLongLongrowLONGVARGRAPHICLONGBINARYGENERALlongtextTextLongrowLONGVARCHARlongtext域域可以幫助識別模型中信息的類型，它定義了列的一組有效值，使用域來定義列可以使得不同表中列的數(shù)據(jù)特征更容易標(biāo)準(zhǔn)化及便于修改。域分為標(biāo)準(zhǔn)域和方案域兩種，標(biāo)準(zhǔn)域由域名前加“_”構(gòu)成，各方案工程通用，方案域由各方案工程根據(jù)實際情況自行定義。標(biāo)準(zhǔn)域的定義見下表：〔可擴展〕名稱編碼說明類型長度精度addressAddressvarchar(100)100eMailEMail電子郵件varchar(30)30memomemo備注varchar(1000)1000PathNamepathName路徑名varchar(250)250Percentpercent百分比decimal(10,2)102moneyMoney金額decimal(18,2)182pricePrice單價intSexSex性別char(1)1telTelephone號碼char(16)16unitunitOfMeasure計量單位char(16)16wwwwww網(wǎng)址varchar(30)30鍵設(shè)計原那么鍵〔主鍵〕標(biāo)注字段的唯一并保證表之間〔外鍵〕的完整性，以下方法有助于進行鍵的設(shè)計：盡量防止使用復(fù)合鍵作為主鍵；為關(guān)聯(lián)字段創(chuàng)立外鍵；所有的鍵都必須唯一；一個外鍵總是關(guān)聯(lián)唯一的鍵字段；防止選擇用戶可編輯的字段作為鍵，因為一旦輸入錯誤，唯一能做的就是刪除整個記錄然后從頭開始；使用流水號表〔?〕主鍵定義方案：〔供討論〕主鍵生成算法1：根據(jù)公司現(xiàn)行狀況Int類型，采用序列,長度為11位；Varchar類型，采用java生成，長度為32位；索引設(shè)計原那么什么是索引索引是建立在表的一列或多個列上的輔助對象，目的是加快訪問表中的數(shù)據(jù)；Oracle存儲索引的數(shù)據(jù)結(jié)構(gòu)是B*樹，位圖索引也是如此，只不過是葉子節(jié)點不同B*數(shù)索引；索引由根節(jié)點、分支節(jié)點和葉子節(jié)點組成，上級索引塊包含下級索引塊的索引數(shù)據(jù)，葉節(jié)點包含索引數(shù)據(jù)和確定行實際位置的rowid。使用索引的目的加快查詢速度減少I/O操作消除磁盤排序何時使用索引查詢返回的記錄數(shù)排序表<40%非排序表<7%表的碎片較多〔頻繁增加、刪除〕索引的種類非唯一索引〔最常用〕唯一索引位圖索引局部有前綴分區(qū)索引局部無前綴分區(qū)索引全局有前綴分區(qū)索引散列分區(qū)索引基于函數(shù)的索引數(shù)據(jù)完整性數(shù)據(jù)完整性有四種類型：實體完整性、域完整性、引用完整性和用戶定義完整性等幾種。大型數(shù)據(jù)庫一般都提供豐富的約束機制，以保證數(shù)據(jù)的完整性，通常采用的有鍵、規(guī)那么、約束和觸發(fā)器等幾種。實體完整性實體完整性將行定義為特定表的唯一實體。實體完整性強制表的標(biāo)識符列或主鍵的完整性〔通過索引、UNIQUE約束、PRIMARYKEY約束或IDENTITY屬性〕。域完整性域完整性是指給定列的輸入有效性。強制域有效性的方法有：限制類型〔通過數(shù)據(jù)類型〕、格式〔通過CHECK約束和規(guī)那么〕或可能值的范圍〔通過FOREIGNKEY約束、CHECK約束、DEFAULT定義、NOTNULL定義和規(guī)那么〕。引用完整性在輸入或刪除記錄時，引用完整性保持表之間已定義的關(guān)系。引用完整性基于外鍵與主鍵之間或外鍵與唯一鍵之間的關(guān)系〔通過FOREIGNKEY和CHECK約束〕。引用完整性確保鍵值在所有表中一致。這樣的一致性要求不能引用不存在的值，如果鍵值更改了，那么在整個數(shù)據(jù)庫中，對該鍵值的所有引用要進行一致的更改。如果定義了強制引用完整性時，那么禁止用戶進行以下操作：當(dāng)主表中沒有關(guān)聯(lián)的記錄時，將記錄添加到相關(guān)表中。更改主表中的值并導(dǎo)致相關(guān)表中的記錄孤立。從主表中刪除記錄，但仍存在與該記錄匹配的相關(guān)記錄。用戶定義完整性用戶定義完整性使您得以定義不屬于其它任何完整性分類的特定業(yè)務(wù)規(guī)那么。所有的完整性類型都支持用戶定義完整性〔包括CREATETABLE中的所有列級和表級約束、存儲過程和觸發(fā)器〕。關(guān)系設(shè)計原那么在關(guān)系數(shù)據(jù)庫中，關(guān)系能防止冗余的數(shù)據(jù)。關(guān)系是通過匹配鍵列中的數(shù)據(jù)而工作的，而鍵列通常是兩個表中具有相同名稱的列。在大多數(shù)情況下，關(guān)系將一個表中為每個行提供唯一標(biāo)識符的主鍵與另一個表中外鍵內(nèi)的項相匹配。表與表之間存在三種類型的關(guān)系，即一對一關(guān)系、一對多關(guān)系和多對多關(guān)系。一對一關(guān)系在一對一關(guān)系中，表A中的一行最多只能與表B中的一行相匹配，反之亦然。如果兩個相關(guān)列都是主鍵或具有唯一約束，那么創(chuàng)立的是一對一關(guān)系。存在一對一關(guān)系時通常會將列保存在同一個表中，但出于以下目的，可以將列保存在一個以上的表中：分割一個含有許多列的表。出于平安考慮而隔離表的某一局部。存儲可以很容易刪除的臨時數(shù)據(jù)，只需刪除表即可刪除這些數(shù)據(jù)。存儲只應(yīng)用于主表子集的信息。一對多關(guān)系一對多關(guān)系是最常見的關(guān)系類型，俗稱主從表。在這種關(guān)系類型中，表A中的行可以在表B中有許多匹配行，但是表B中的行只能在表A中有一個匹配行。多對多關(guān)系在多對多關(guān)系中，表A中的一行可與表B中的多行相匹配，反之亦然。通過定義稱為連接表的第三方表創(chuàng)立這樣的關(guān)系，該連接表的主鍵包括表A和表B中的外鍵。如果兩個實體之間存在一對多關(guān)系，而且有可能會轉(zhuǎn)化為多對多關(guān)系，那么最好一開始就設(shè)置成多對多關(guān)系。這可以防止日后的很多麻煩。命名標(biāo)準(zhǔn)數(shù)據(jù)庫對象的命名應(yīng)遵循統(tǒng)一的標(biāo)準(zhǔn)，以便于管理和系統(tǒng)之間的集成。表如果表名是一個和兩個單詞，名字就取全名；如果表的名字由3個單詞組成，就各取單詞縮寫。對關(guān)聯(lián)表來說，表名加上RELATE_作為中綴，后面加代表此關(guān)聯(lián)的實意單詞。對工作用表(查詢、報表)來說，表名加上TMP_、QRY_或者REP_等中綴加以區(qū)別。表名長度不能超過20，ORACLE默認(rèn)最大長度30.對全局用表，表名前綴使用GBL_。例如：數(shù)據(jù)表T_ACCOUNT臨時表T_TEMP_ACCOUNT關(guān)聯(lián)表 T_ACCOUNT_RELATE_Company視圖命名標(biāo)準(zhǔn)和表類似，前綴為VIEW。例如：T_VIEW_ACCOUNT。域〔Domains〕域名定義使用有具體意義英文單詞，如果是一個或兩個單詞，名字就取全名；如果名字由3個單詞組成，就各取單詞縮寫。數(shù)據(jù)庫系統(tǒng)保存字不能用作域名。例如：金額money numeric(18,2)日期date datetime地址address varchar(100)字段字段定義參照“表”名定義，無須加子系統(tǒng)前綴，