對於設計和創建資料庫完全是個新手？沒關係，Joe Celko， 世界上讀者數量最多的SQL作者之一，會告訴你這些基礎。和往常一樣，即使是最專業的資料庫老手，也會給他們帶來驚喜。Joe是DMBS雜誌是多年來最受 讀者喜愛的作者。他在美國、英國，北歐，南美及非洲傳授SQL知識。他在ANSI / ISO SQL標準委員會工作了10年，為SQL-89和SQL-92標準做出了傑出貢獻。

 介紹完表，Joe Celko會談下如何把它們放一起作為資料庫，還有什麼是實體關係和視圖。

在第一篇，我們因它們是什麼並區分它們命名數據元。在第二篇，我們用SQL里給我們的數據類型和簡單的行或列約束來模型化數據元。在第三篇，我們把這些行放入表成為實體，關係和輔助數據。

現在我們有了基表，是時候把它們放一起作為資料庫，增加其它的架構對象混合一起。這需要我們從比一次一個表或多個表更高的層級來看。對這個一個有用的工具是實體關係圖（E-R (Entity-Relationship) diagram）。不好的消息是有很多風格的實體關係圖，其中一些變得非常複雜。這個工具的第一個版本應歸於Peter Chen在他1976年的論文里，它還是一個很好開始的地方。每個系統認同實體表表現為一個在它裡面有表名的矩形。但一些系統會放入所有列名，對於主鍵標上特殊符號作為不同等等。

Chen最先使用方塊牌（diamond）作為關係表。這是個很好的主意，在它裡面很容易畫n元關係，你可以快速看到交替的模式框和方塊牌。如果一個表同時使用，會有一些結論。例如，婚姻是丈夫和妻子之間的關係，但也有婚姻日期的數據，登記號，證婚人（presiding official）等等。

接下來的系統放棄了方塊牌，把關係表放入矩形，並使用只能顯示二元關係的線，但線的末端有可選或必選成員關係的標誌，0,1或更多成員關係級別，給它一些權利。這三個圖形是條形作為1，圓形作為0，“雞爪”作為多個。這個百聞不如一見。

我們可以認為講師（lecturer）傳授（teaches）課程（courses），因此課程是被講師傳授。

這很好理解，但我們應該表示更多的規則。例如，如果我們有一個策略，每個講師必須剛好只傳授一個課程？我們可以添加用最大1的條行標誌和第二個執行中間線的條行來表示傳授關係。這個邏輯適用於關係里涉及的課程。

現在，讓我們放寬一些規則。我們認為保持一個講師工作，即使他這次沒傳授任何東西，但換取這份工作安全，我們想讓他有時候可以傳授一個或更多的課程。圓形指向線中心，雞爪在課程框旁。

這一切都很好，直到我們覺得多對多的關係，這會看起來像這樣：

我們需要有一個明確的關係表，稱它“教學任務（Teaching Assignments）”，在講師和課程之間。實體關係圖更容易看懂，不需要看很多的SQL DDL語句。還有其它突出的模式，例如扇形。

我不能把部門和人員正確匹配我們。假設常見的組織架構，這應該2個1:n分部（Divisions）的關係模型。

你可以用多個工具從SQL DDL里獲得實體關係圖，在更高級查看其它問題模式。這裡我不會給ER模型和圖的詳細說明；現在我只想讓你知道它們。接下來，你可以自己學習使用它們。

一旦你的表設計已經確定，就可以考慮數據訪問了。這通常意味著你會加索引到表。有兩類索引：主和從。主索引必須在表上執行唯一性約束，像PRIMARY KEY和UNIQUE約束，從索引添加是為了性能提升。

SQL引擎會自動為你創建主索引，但這個假設並不對你有好處。在SQL Server里，在一個表上你只能有一個聚集索引，因此小心用它。例如，不用聚集索引在customer_id列作為客戶表的主鍵，你會使用它保持物理文件按部門編號排序，因為這是你的報表分組和彙總的樣子。同時使用非聚集索引作為查找客戶就可以了。

索引的樹結構由在CREATE INDEX語句里的列順序決定。這就是說：

1 CREATE INDEX Foobar ON Customers (state_code, city_name);

和

1 CREATE INDEX Barfoo ON Customers (city_name, state_code); 

邏輯上是一樣的，但功能不同。

選擇從索引是個非完全多項式（NP-Complete）問題，因此你不能用常規方法創建它們。最好你可以遵循一些簡單的啟髮式。第一個啟髮式不要重疊索引（over-index）。初學者喜歡增加很多索引讓它們的產尋更快。這並不都是對的：查詢優化器會忽略用不到的索引，因此事實上它們變成了“無用代碼”。但當基表修改的時候，每個插入，更新和刪除語句會修改這些無用的索引。這會是很大的負擔。

第二個啟髮式如果一列從不在查詢條件里使用（意思是說在WHERE、ON或HAVING子句里），那它不應該在索引里出現。

第三個啟髮式你不應該有常見列首碼列表的索引。

這就是說如果你有個像這樣的索引：

1 CREATE INDEX Floob ON ExampleTable (a, b, c, d); 

那實際上，下列這些索引是贈送的：

1 CREATE INDEX Floob_3 ON ExampleTable (a, b, c);
2 CREATE INDEX Floob_2 ON ExampleTable (a, b);
3 CREATE INDEX Floob_1 ON ExampleTable (a); 

直接創建隱含的索引是多餘的。

下一個你經常會用的添加到架構的東西是視圖。很多程式員認為視圖可以幫助用戶減少重覆代碼的編寫。那是對的，但視圖的最大優點是它每次用同樣的方式做同樣的事，對每個人。人總不會一致的。不抱怨的話，相比另一個程式員，程式員不會實現不同的業務規則。Fred讀到的規格是（shipping_qty > 100)）和Sam讀到的規格（shipping_qty >= 100）；如果他們使用視圖的話，業務規則適用一個且只有一個方式。

通常來講，視同扮演2個方式。或者他們是本地的語句（通常一個SELECT）和擴展為內嵌的文本，它們的定義保持在架構里。另一個做法是從它們的定義以物理表實現它們的定義。一般而言，當多個會話同時使用它們的時候，一個好的SQL引擎會實現視圖，這樣的話虛擬表可以在主存里共用，一個會話可以多次使用同樣的視圖。在SQL Server，你可以在視圖上創建索引提高性能。

即使有經驗的SQL人員也不知道視圖的另一部分； WITH CHECK OPTION子句。如果指定了WITH CHECK OPTION，視圖表必須可更新。這個做法是阻止通過WHERE子句的違反。我們用例子解釋下：

1 CREATE VIEW NYC_Personnel
2 AS
3 SELECT *
4 FROM Personnel
5 WHERE city_name = 'New York'; 

現在我們用下列語句UPDATE：

1 UPDATE NYC_Personnel
2 SET city_name = 'Birmingham'; –- everyone moved!! 

UPDATE會執行，沒有任何問題，但我們再次使用NYC_Personnel時，剛纔看到的記錄現在都消失了。這已不再符合WHERE子句的條件！同樣，一個有（col1 = 'B'）的INSERT INTO語句沒有問題，但在這個視圖裡從不會看到。

WITH CHECK OPTION會讓系統會在INSERT或UPDATE上檢查WHERE子句。如果新的或修改的行測試失敗，修改會被拒絕，視圖還是一樣。那麼，剛纔的UPDATE語句會收到錯誤信息，你不能在特定方式里修改特定列。

WITH CHECK OPTION可以作為架構級別的CHECK()子句。例如，假設有規則的酒店預定，你不能添加客人到另一個客人已或會占用的房間。不用直接寫約束，像這樣：

 1 CREATE TABLE Hotel
 2 (room_nbr INTEGER NOT NULL,
 3 arrival_date DATE NOT NULL,
 4 departure_date DATE NOT NULL,
 5 guest_name CHAR(30) NOT NULL,
 6 CONSTRAINT schedule_right
 7 CHECK (H1.arrival_date <= H1.departure_date),
 8 –- valid Standard SQL, but going to to work!!
 9 CONSTRAINT no_overlaps
10 CHECK (NOT EXISTS
11 (SELECT *
12 FROM Hotel AS H1, Hotel AS H2
13 WHERE H1.room_nbr = H2.room_nbr
14 AND H2.arrival_date < H1.arrival_date
15 AND H1.arrival_date < H2.departure_date))); 

schedule_right約束沒有問題，因為它沒有子查詢，但很多產品會檢查overlaps約束。我們可以不用表上的no_overlaps約束，我們可以在Hotel表上所有行列上構建一個視圖，並增加執行WITH CHECK OPTION的WHERE子句。

 1 CREATE VIEW Valid_Hotel_Stays (room_nbr, arrival_date, departure_date, guest_name)
 2 AS
 3 SELECT H1.room_nbr, H1.arrival_date, H1.departure_date, H1.guest_name
 4 FROM Hotel AS H1
 5 WHERE NOT EXISTS
 6 (SELECT *
 7 FROM Hotel AS H2
 8 WHERE H1.room_nbr = H2.room_nbr
 9 AND H2.arrival_date < H1.arrival_date
10 AND H1.arrival_date < H2.departure_date)
11 AND H1.arrival_date <= H1.departure_date
12 WITH CHECK OPTION; 

例如：

1 INSERT INTO Valid_Hotel_Stays
2 VALUES (1, '2011-01-01', '2011-01-03', 'Ron Coe'); 

隨後：

1 INSERT INTO Valid_Hotel_Stays
2 VALUES (1, '2011-01-03', '2011-01-05', 'John Doe'); 

在第2個INSERT INTO語句上，會給我們想要的違反了WITH CHECK OPTION子句。

真正的好處是，這讓約束在聲明代碼（declarative code）里了，且查詢優化器可以使用。

視圖可以通過隔離讓用戶看不到未授權或不需要的數據。理想地，你想為每個用戶創建一系列的視圖，讓他們覺得資料庫就像專門為它們設計的一樣。這會花點時間，你需要知道如何使用SQL的第三（最被忽略的）子語言——數據控制語言（the DCL (Data Control Language)）。

DCL不是個安全系統；它是SQL資料庫的簡單的控制工具。它讓數據不在安全級別外暴露。

在一個安全的系統里，在最小的安全級別，我們被告知超人是來自外星球的奇怪訪客，有常人不及的能力和技能。但我們需要更高的級別來知道他是偽裝的Clark Kent，偉大都市報紙的溫順記者~~~

原文鏈接：

http://www.sqlservercentral.com/articles/Database+Design/Building+a+Database+Schema/70793/