關於數據庫的邏輯設計是一個很廣泛的問題本文主要針對開發應用中遇到在MS SQL Server上進行表設計時對表的主鍵設計應注意的問題以及相應的解決辦法
主鍵設計現狀和問題
關於數據庫表的主鍵設計一般而言是根據業務需求情況以業務邏輯為基礎形成主鍵
比如銷售時要記錄銷售情況一般需要兩個表一個是銷售單的概要描述記錄諸如銷售單號總金額一類的情況另外一個表記錄每種商品的數量和金額對於第一個表(主表)通常我們以單據號為主鍵;對於商品銷售的明細表(從表)我們就需要將主表的單據號也放入到商品的明細表中使其關聯起來形成主從關系同時該單據號與商品的編碼一起形成明細表的聯合主鍵這只是一般情況我們稍微將這個問題延伸一下假如在明細中我們每種商品又可能以不同的價格方式銷售有部分按折扣價格銷售有部分按正常價格銷售要記錄這些情況那麼我們就需要第三個表而這第三個表的主鍵就需要第一個表的單據號以及第二個表的商品號再加上自身需要的信息一起構成聯合主鍵;又或者其他情況在第一個主表中本身就是以聯合方式構成聯合主鍵那麼也需要在從表中將主表的多個字段添加進來聯合在一起形成自己的主鍵
數據冗余存儲隨著這種主從關系的延伸數據庫中需要重復存儲的數據將變得越來越龐大或者當主表本身就是聯合主鍵時就必須在從表中將所有的字段重新存儲一次
SQL復雜度增加當存在多個字段的聯合主鍵時我們需要將主表的多個字段與子表的多個字段關聯以獲取滿足某些條件的所有詳細情況記錄
程序復雜度增加可能需要傳遞多個參數
效率降低數據庫系統需要判斷更多的條件SQL語句長度增加同時聯合主鍵自動生成聯合索引
WEB分頁困難由於是聯合主鍵方式(對於多數的子表)那麼在WEB頁面上要進行分頁處理時在自關聯時難於處理
解決方案
從上面我們已經看到現有結構存在著相當多的弊端主要是導致程序復雜效率降低並且不利於分頁
為解決上述問題本文提出當應用系統後台數據庫表間存在主從關系時數據庫表額外增加一非業務字段作為主鍵該字段為數值型;或者當該表需要在應用中進行分頁查詢時也應考慮如此設計一般地我們也可以幾乎為任何表增加一個與業務邏輯無關的字段作為該表的主鍵字段
由於該字段要作為表的主鍵那麼其首要條件是要保證在該表中要具有唯一性同時結合SQL Server數據庫自身的特性可以為其建立一個自增列
create TABLE T_PK_DEMO
(
U_ID BIGINT NOT NULL IDENTITY()
–唯一標識記錄的ID
COL_OTHER VARchar() NOT NULL
–其他列
CONSTRAINT PK_T_PK_DEMO PRIMARY KEY NONCLUSTERED
(U_ID)–定義為主鍵
)
但是SQL Server中的自增列卻存在一個比較尴尬的事實那就是該字段一旦定義和使用用戶無法直接干預該字段的值完全由數據庫系統自身控制
完全數據庫系統控制用戶無法修改值
在數據庫的發布和訂閱時使用自增列會比較麻煩
恢復部分數據時使用自增列會比較麻煩
該列的值必須在插入數據後才能獲取
鑒於此建議不以自增列的方式來定義而是參考Oracle數據庫系統中序列在SQL Server系統中實現類似Oracle數據庫系統序列功能這個具體在下面的小節中介紹我們只需要按照普通字段的定義方式修改表定義為
create TABLE T_PK_DEMO
(
U_ID BIGINT NOT NULL
–唯一標識記錄的ID
COL_OTHER VARchar(
) NOT NULL
–其他列
CONSTRAINT PK_T_PK_DEMO PRIMARY KEY NONCLUSTERED (U_ID)–定義為主鍵
)
參照Oracle序列的功能我們需要在SQL Server數據庫中創建一個新表以管理序列值
create TABLE T_DB_SEQ
(
SEQ_NAMEVARchar(
) NOT NULL
–序列名稱
SEQ_OWNER VARchar(
) NOT NULL DEFAULT ’DBO’
–序列所有者(SYSTEM_USER)
SEQ_CURRENT BIGINT NOT NULL DEFAULT
–序列當前值
SEQ_MIN BIGINT NOT NULL DEFAULT
–序列最小值
SEQ_MAX BIGINT NOT NULL DEFAULT
–序列最小值
SEQ_MAX BIGINT NOT NULL DEFAULT
–序列最大值
SEQ_STEPINT NOT NULL DEFAULT
–序列增長步長
IF_CYCLEINT NOT NULL DEFAULT
–是否循環(
不循環;
循環)
CONSTRAINT T_DB_SEQ PRIMARY KEY CLUSTERED
(SEQ_NAME
SEQ_OWNER)–主鍵
)
應用系統為需要創建自增列的表創建一個序列名稱在表“T_DB_SEQ”中反映為數據庫中的一行
第一需要為需要建立序列的表創建一個序列采用方法F_create_SEQ(序列名)該函數傳入序列的名稱在表“T_DB_SEQ”插入一行序列的所有者采用系統變量SYSTEM_USER
第二獲取下一個值采用方法F_GET_NEXT_SEQ_VAL(序列名)該函數根據序列名獲取該序列的下一個值根據當前值與增長步長得到同時該函數保證在同時獲取同一個序列時應保證並發一致性
第三將返回值返回到應用使用
此外為保證應用的完整性可能還需要提供一些方法的重載方法同時提供一些其他方法
獲取序列當前值F_GET_SEQ_CUR_VAL(序列名)
設置序列值F_SET_SEQ_VAL(序列名)
刪除序列F_DEL_SEQ(序列名)
判斷序列是否存在F_SEQ_exists(序列名)
在主從關系的表設計中子表也使用序列字段作為唯一主鍵將父表的序列字段作為外鍵關聯
create TABLE T_PK_DEMO_C
(
U_ID BIGINT NOT NULL
–唯一標識記錄的ID
COL_OTHER VARchar(
) NOT NULL
–其他列
P_ID INT NOT NULL
–父表ID
CONSTRAINT PK_T_PK_DEMO_C PRIMARY KEY
NONCLUSTERED (U_ID)–定義為主鍵
CONSTRAINT FK_T_PK_DEMO_C FOREIGN KEY (P_ID)
REFERENCES T_PK_DEMO(U_ID) ON delete CASCADE
)
使用序列的問題及解決辦法
由於系統使用一個額外增加一個字段作為主鍵因此沒有為業務邏輯建立主鍵約束比如在企業用戶信息表中要求企業中用戶登錄名必須唯一一般在創建表時以登錄名作為主鍵這個時候在數據庫層自然的創建另一個主鍵唯一性約束而現在沒有使用登錄名作為主鍵那麼就沒有這個約束解決辦法
一是在數據庫層解決可以為該表創建一個唯一(UNIQUE)約束或者唯一索引如
alter TABLE T_PK_DEMO ADD CONSTRAINT C_T_PK_DEMO UNIQUE NONCLUSTERED(COL_OTHER)唯一約束
create UNIQUE INDEX IX_T_PK_DEMO ON T_PK_DEMO(COL_OTHER) – 唯一索引
二是在應用端解決也就是在應用中判斷該列是否有重復值然後根據判斷結果來保證唯一性
我們注意到在之前的例子中主鍵采用了NONCLUSTERED(非聚蔟)的索引方式關於如何設計索引不是本文的重點在這裡僅提供一個建立索引時采用聚蔟方式還是非聚蔟方式的一個一般原則
作為非業務字段的主鍵列是一個沒有重復值的基本不進行更新操作的列並且在SQL Server數據庫中聚蔟索引在一個表中只能有一個因此聚蔟索引非常重要需要留給更重要的字段來使用因此對照上表和根據聚蔟索引的重要程度在此處采用非聚蔟方式創建其索引
具體應用
采用這種主鍵設計方式有諸多好處這已經在前文說明現在就以一個具體的應用來說明如何使用這個主鍵
當前的應用系統基本上都已經采用B/S方式盡管現在的網絡速度已經有大幅度的提高但是由於在WEB應用上用戶數量眾多同時基本上所有的運算都集中在WEB應用服務器上所以在WEB設計上更要考慮到性能的優化以減少網絡流量和對服務器的壓力最常見的一個應用就是列表方式展現時的分頁方式一般的在數據量小的情況下一般不會怎麼注意這個問題通常采用將數據完全取出然後在WEB服務器上進行分頁但是當數據量龐大時這種方式就會導致速度降低甚至根本不可用所以一般采用存儲過程在數據庫端進行分頁
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22342.html
