在這篇文章的第二章中，我們已經(jīng)建立了一個供我們使用的非常簡單的笑話數(shù)據(jù)庫，這個庫中只包括了一個名叫Jokes的數(shù)據(jù)表。這作為我們使用MySQL數(shù)據(jù)庫的入門已經(jīng)是足夠了，但是在關(guān)系型數(shù)據(jù)庫的設(shè)計中還有很多其它的東西。在這一章中，我們會對我們的例子進行擴充，學習一些有關(guān)MySQL的新知識，并試圖理解并掌握關(guān)系型數(shù)據(jù)庫所能提供的功能。
首先，我們得說明我們對許多問題的解決只是不正規(guī)的（也就是說非正式的）。正如你在許多計算機科學專業(yè)中了解的那樣，數(shù)據(jù)庫設(shè)計是一個嚴肅的領(lǐng)域，數(shù)據(jù)庫設(shè)計必須包括對它的測試并會涉及到一些數(shù)學的原理。但這些可能是超過我們這篇文章的范圍了。要得到更多的信息，你可以停下來到http://www.datamodel.org/去看看，在那兒你可以看到許多好的書籍，并得到一些關(guān)于這個問題的有用的資源。
給予應有的權(quán)限在開始之前，讓我們回憶一下我們的Jokes數(shù)據(jù)表的結(jié)構(gòu)，這個表包含三個列：ID、JokeText和 JokeDate。這些列可以使我們標識笑話（ID），明了他們的內(nèi)容（JokeText）以及他們被加入的時間（JokeDate）。
現(xiàn)在我們想要保存我們的笑話中的其它一些信息：提交者的姓名。這看上去很自然，我們需要在我們的Jokes數(shù)據(jù)表中添加一個新的列。SQL的ALTER命令（我們在之前沒看到過這個命令）可以幫助我們完成這件事。使用mysql命令行程序登錄到MySQL服務器，選擇你的數(shù)據(jù)庫（如果你使用我們在第二章中的命名，數(shù)據(jù)庫名應該是joke），然后輸入下面的命令：
mysql> ALTER TABLE Jokes ADD COLUMN
-> AuthorName VARCHAR(100);
|
這將會在我們的數(shù)據(jù)表中增加一個叫AuthorName的列。其數(shù)據(jù)類型是一個可變長度的字符串，其最大長度是100個字符（這對于最復雜的名字應該也是足夠了）。讓我們再添加一列用來保存作者的e-mail地址：
mysql> ALTER TABLE Jokes ADD COLUMN
-> AuthorEMail VARCHAR(100);
|
要得到更多的有關(guān)ALTER命令的信息，請參看MySQL參考手冊。要確認我們是不是正確地添加了兩列，你可以要求MySQL為我們對這個表進行描述：
看上去很不錯。明顯地，我們需要對我們在第四章中建立的添加新笑話的HTML以及PHP格式的代碼進行調(diào)整，但是我們會把這留給你作為一個練習。使用UPDATE查詢，你現(xiàn)在可以對表中的所有笑話添加作者的詳細資料。然而，在你開始接受這個數(shù)據(jù)結(jié)構(gòu)之前，我們必須考慮一下我們在這兒選擇的設(shè)計是否確當。在這種情況下，我們會發(fā)現(xiàn)一些我們還沒有做到的事情。
一個基本的規(guī)則：保持事物的分離在你建立數(shù)據(jù)庫驅(qū)動的網(wǎng)站的過程中，你已經(jīng)覺得僅僅是有一個笑話列表是不夠的。事實上，除了你自己的笑話以外，你開始接收其他人提交的笑話。你決定做一個讓全世界人都可以共享笑話的網(wǎng)站。你有沒有聽說過Internet電影數(shù)據(jù)庫（IMDB）？實際上你現(xiàn)在做的是Internet笑話數(shù)據(jù)庫（IJDB）！對每一個笑話添加作者的姓名和e-mail地址肯定是最容易想到的辦法，但是這種方法會導致一些潛在的問題：
如果一個經(jīng)常投稿的名叫Joan Smith的人改變了她的e-mail地址將會發(fā)生什么什么情況呢？她會開始使用新地址來提交新的笑話，但是對于所有的舊笑話，你所能看到的還是舊的地址。從你的數(shù)據(jù)庫來看，你也許只能認為有兩人名字都叫Joan Smith的人在向你的數(shù)據(jù)庫中提交笑話。如果她是特別體貼的，她也許會通知你改變地址，你可以將所有的舊笑話改成新的地址，但是如果你遺漏了一個，那就意味著你的數(shù)據(jù)庫中存儲了錯誤的信息。數(shù)據(jù)庫設(shè)計專家將這種類型的問題稱之為一個“更正異常”。
很自然地你會想到從你的數(shù)據(jù)庫中得到所有曾經(jīng)向你的站點提交過笑話的人的列表。實際上，你可以使用下面的查詢很容易地得到這樣的列表：
| mysql> SELECT DISTINCT AuthorName, AuthorEMail -> FROM Jokes; |
上面查詢中DISTINCT是告訴MySQL不輸出重復的結(jié)果行。例如，如果Joan Smith向我們的站點提交過20個笑話，如果我們使用了DISTINCT選項，她的名字和e-mail地址將會只在列表中出現(xiàn)一次，否則會出現(xiàn)20次。
如果因為某種原因，你決定要從數(shù)據(jù)庫中刪除某個特定的作者所提交的所有笑話，但是，與此同時，你將不能再通過e-mail與他們聯(lián)系！而你的e-mail清單可能是你的網(wǎng)站的收入的主要來源，所以你并不想只因為你不喜歡他們提交的笑話，就刪除他們的e-mail地址。數(shù)據(jù)庫設(shè)計專家將這稱之為“刪除異常”。
你并不能保證不會出現(xiàn)這樣的情況：Joan Smith輸入的姓名一會兒是“Joan Smith”，一會兒是“J. Smith”，一會兒又是“Smith, Joan”。這將使得你要確定一個特定的作者變得非常困難（特別是Joan Smith又經(jīng)常使用幾個不同的email地址的時候）。
這些問題的解決其實很簡單。只要你不再將作者的信息存儲到Jokes數(shù)據(jù)表中，而是建立一個新的數(shù)據(jù)表來存儲作者列表。因為我們在Jokes數(shù)據(jù)表中使用了一個叫ID的列來用一個數(shù)據(jù)標識每個笑話，所以我們在新的數(shù)據(jù)表中使用了同樣名字的列來標識我們的作者。我們可以在我們的Jokes表中使用“author ID's”來建立笑話和他的作者之間的關(guān)聯(lián)。全部的數(shù)據(jù)庫設(shè)計應該是這樣的：
上面的兩個表包含了三個笑話和兩個作者。Jokes表的AID列（“Author ID”的縮寫）提供了兩個表之間的關(guān)聯(lián)（指出Kevin Yank 提交了笑話1和笑話2，Joan Smith提交了笑話3）。在這里，你還需要注意到每一個作者只會在數(shù)據(jù)庫中出現(xiàn)一次，而且他們是獨立于他們提交的笑話而存在的，因此我們已經(jīng)解決了我們上面提出的那些問題。
這個數(shù)據(jù)庫設(shè)計的最重要的特征是，因為我們要存儲兩種類型的事物（笑話和作者），所以我們設(shè)計兩個表。這是我們在數(shù)據(jù)庫設(shè)計中要遵守的一個基本規(guī)則：對于每一個要存儲其信息的實體（或事物），我們都應該給他一個自己的表。
重新生成上面的數(shù)據(jù)是非常簡單的（只要使用兩個CREATE TABLE 查詢就行了），但是因為我們想要在做這些變動時不會有破壞性的效果（也就是說不會丟失我們已經(jīng)存入的笑話），所以我們需要再次使用ALTER命令。 首先，我們刪除Jokes表中有關(guān)作者的列：
mysql> ALTER TABLE Jokes DROP COLUMN AuthorName;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE Jokes DROP COLUMN AuthorEMail;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
現(xiàn)在我們建立我們的新的數(shù)據(jù)表：
mysql> CREATE TABLE Authors (
-> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(100),
-> EMail VARCHAR(100)
-> );
|
最后，我們在我們的Jokes表中添加AID列：
mysql> ALTER TABLE Jokes ADD COLUMN AID INT;
現(xiàn)在剩下來的就是向新的表中添加一些作者，并通過填充AID列來對數(shù)據(jù)庫中已經(jīng)存在的笑話指定作者。
處理多個表現(xiàn)在我們的數(shù)據(jù)被分布在兩個表當中，要從其中獲得數(shù)據(jù)看上去變得更加復雜了。例如，我們最初的目標是：顯示一個笑話的列表并在每一個笑話后面顯示作者的姓名和e-mail地址。在我們的單表結(jié)構(gòu)中，要獲得所有的信息，只需要在我們的PHP代碼中使用一個SELECT語句就行了：
$jokelist = mysql_query(
"SELECT JokeText, AuthorName, AuthorEMail ".
"FROM Jokes");
while ($joke = mysql_fetch_array($jokelist)) {
$joketext = $joke["JokeText"];
$name = $joke["AuthorName"];
$email = $joke["AuthorEMail"]; // Display the joke with author information
echo( "<P>$joketext<BR>" .
"(by ＜HREF='mailto:$email'＞$name)</P>" );
}
|
在我們的新系統(tǒng)中，這樣做初看起來是不可能了。因為有關(guān)每個笑話的作者的詳細資料不是存儲在Jokes表中，我們可能想到的一個解決方案是我們對于我們想要顯示的笑話單獨地獲得這些資料。代碼將是這樣的：
// Get the list of jokes
$jokelist = mysql_query(
"SELECT JokeText, AID FROM Jokes");
while ($joke = mysql_fetch_array($jokelist)) {
// Get the text and Author ID for the joke
$joketext = $joke["JokeText"];
$aid = $joke["AID"];
// Get the author details for the joke
$authordetails = mysql_query(
"SELECT Name, Email FROM Authors WHERE ID=$aid");
$author = mysql_fetch_array($authordetails);
$name = $author["Name"];
$email = $author["EMail"];
// Display the joke with author information
echo( "<P>$joketext<BR>" .
"(by ＜A HREF='mailto:$email'＞$name)</P>" );
}
|
很混亂，而且對于每一個顯示的笑話都包含了一個對數(shù)據(jù)庫的查詢，這將會我們的頁面的顯示非常緩慢。現(xiàn)在看來，“老方法”可能是更好的解決方案，盡管它有其自身的弱點。
幸運的是，關(guān)系型數(shù)據(jù)庫可以很容易地處理多個表中的數(shù)據(jù)！在SELECT語句中使用一個新的被稱之為“join”的格式，我們可以找到兩全其美的辦法。連接可以使我們象對存儲在單個表中的數(shù)據(jù)那樣對待多個表中的關(guān)聯(lián)數(shù)據(jù)。一個連接的格式應該是這樣的：
mysql> SELECT <columns> FROM <tables>
-> WHERE <condition(s) for data to be related>;
|
在我們目前的情況下，我們所需要的列是Jokes表中的JokeText列以及Authors表中的Name列和Email列。Jokes表和Authors表的關(guān)聯(lián)條件是Jokes表中的AID列的值等于Authors表中的ID列的值。下面是一個連接的例子（前兩個查詢只是用來顯示我們的兩個表中所包含的內(nèi)容）：
現(xiàn)在明白了嗎？第三個SELECT的結(jié)果就是一個連接，它將存儲在兩個表中的數(shù)據(jù)關(guān)聯(lián)數(shù)據(jù)顯示到了一個結(jié)果表中，盡管我們的數(shù)據(jù)是存儲在兩個表中的，我們?nèi)匀豢梢允褂靡粋€數(shù)據(jù)庫查詢就獲得我們的Web頁面所需要的笑話列表的全部信息。
在這里，要注意一個問題，因為在兩個表中都有一個叫ID的列，所以我們在用到Authors表中的ID列時我們必須指定表名（Authors.ID）。如果我們沒有指定表名，MySQL將無法知道我們指的是哪一個表中的ID，這會導致這樣的一個錯誤：
mysql> SELECT LEFT(JokeText,20), Name, Email
-> FROM Jokes, Authors WHERE AID = ID;
ERROR 1052: Column: 'ID' in where clause is ambiguous
|
現(xiàn)在我們知道如何有效率地從我們的兩個表中獲取信息了，我們可以利用連接來重新編寫我們的笑話列表的程序：
$jokelist = mysql_query(
"SELECT JokeText, Name, EMail " .
"FROM Jokes, Authors WHERE AID=Authors.ID");
while ($joke = mysql_fetch_array($jokelist)) {
$joketext = $joke["JokeText"];
$name = $joke["Name"];
$email = $joke["EMail"];
// Display the joke with author information
echo( "<P>$joketext<BR>" .
"(by ＜A HREF='mailto:$email'＞$name)</P>" );
}
|
隨著你對數(shù)據(jù)庫的使用，你會越來越發(fā)現(xiàn)連接的功能有多大的意義。例如，下面的查詢用來顯示所有由Joan Smith寫的笑話：
mysql> SELECT JokeText FROM Jokes, Authors WHERE
-> Name="Joan Smith" AND AID=Authors.ID;
|
上面的查詢的輸出結(jié)果僅僅來源于Jokes表，但是我們使用了一個連接來通過存儲在Authors表中的值搜索笑話。在我們的這篇文章中會有更多的這樣的精巧的查詢，在實際應用中，連接是經(jīng)常會被使用的，而且在絕大多數(shù)的情況下，這會很大程度地簡化我們的工作！
簡單的數(shù)據(jù)關(guān)系對于給定的情況的最好的數(shù)據(jù)模型往往決定于我們所工作的兩種數(shù)據(jù)之間的關(guān)系類型。我這篇文章中，我們將對典型的關(guān)系類型進行研究，并學會如何在一個關(guān)系型數(shù)據(jù)中用最好的方法描述它。
對于簡單的一對一的關(guān)系，只要用一個表就足夠了。一對一關(guān)系的一個例子就是我們在前面已經(jīng)看到的在笑話數(shù)據(jù)庫中的每一個作者的e-mail地址。因為對于每一個作者只有一個e-mail地址，而且對于一個e-mail地址對應的也只有一個作者，將它們分到兩個數(shù)據(jù)庫中是沒有道理的。
多對一的關(guān)系可能會稍微復雜一點，但是在之前其實我們也已經(jīng)解決了這個問題，我們的數(shù)據(jù)庫中的每一個笑話只會有一個作者，但是同一個作者可能寫了很多笑話。笑話和作者之間的關(guān)系就是一個多對一的關(guān)系。我們曾經(jīng)有過一個初步的解決方案，那就是將與這個笑話關(guān)聯(lián)的作者的信息也促成在同一個數(shù)據(jù)庫中。但是這樣做，對于同一個數(shù)據(jù)會有許多拷貝，這不僅會在同步上造成困難，而且會浪費空間。將數(shù)據(jù)分開到兩個數(shù)據(jù)表中并使用一個ID列來連接兩個表（象上面所說的那樣使用連接），所有的問題會得到很好的解決。
到目前為止，我們還沒接觸到一對多的關(guān)系，但是想象這樣的一個關(guān)系應該是不困難的。在我們之前建立的數(shù)據(jù)庫中，我們假定一個作者只有一個e-mail地址。事實上情況并不總是這樣的，作出這個限制的理由只是因為我們只需要一個e-mail地址來與作者聯(lián)系。我們簡單地假設(shè)了作者總會輸入他們常用的e-mail地址，或者至少是一個正常使用的e-mail地址。如果我們想要支持多個e-mail地址，我們將面對一個一對多的關(guān)系（一個作者會有幾個e-mail地址，但是一個e-mail地址只會與一個確定的作者對應）。
一個沒有經(jīng)驗的數(shù)據(jù)庫設(shè)計者面對一個一對多的關(guān)系時，他首先會想到的是試圖把多個數(shù)據(jù)存儲到一個數(shù)據(jù)庫域中，就象這樣：
這種結(jié)構(gòu)在投入使用后，要從數(shù)據(jù)庫中獲得一個單個的e-mail地址，將不得不通過搜索逗號（或者你所選擇的用來分隔的其他符號）來分割字符串，這樣做并不簡單，而且會很耗時。設(shè)想一下如果要用PHP來刪除某個作者的某個e-mail地址，那也將會是很困難的事。另外，對于EMail列我們需要很長的長度，這會導致磁盤空間的浪費，因為大多數(shù)的作者都只會有一個e-mail地址。
解決一對多的關(guān)系和我們上面解決多對一的關(guān)系是非常類似的。實際上兩者之前只是一個簡單的顛倒。我們可將Authors表分成兩個表，Authors和EMails，然后在EMails表中使用作者的ID（AID）這樣的一個列來實現(xiàn)兩個表之間的連接：
使用一個連接，顯示某個作者的所有E-mail地址將會是很簡單的：
多對多的關(guān)系Ok，現(xiàn)在你有了一個發(fā)布在你的網(wǎng)站上的穩(wěn)定增長的笑話數(shù)據(jù)庫。事實上，這種增長是非常迅速的，笑話的數(shù)量會變得難以管理！你的訪問者將面對一個龐大的頁面，在這個頁面上雜亂地排列了數(shù)以百計的笑話。現(xiàn)在，我們不得不考慮作一些變動了。
你決定將你的笑話放置到不同的目錄中，這些目錄可能是“Knock-Knock笑話”、“Crossing the Road笑話”、“Lawyer笑話”和“Political笑話”。記住我們之前的處理規(guī)則，因為我們的笑話目錄是一個不同類型的“事物”，所以我們要為它們建立一個新的數(shù)據(jù)表：
mysql> CREATE TABLE Categories (
-> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(100),
-> Description TEXT
-> );
Query OK, 0 rows affected (0.00 sec)
|
對你的笑話定義其所屬目錄將會是一個困難的任務。因為一個“political”笑話可能也是一個“crossing the road”笑話，同樣，一個“knock-knock”可能也是一個“l(fā)awyer”笑話。一個單個的笑話可能屬于許多目錄，每一個目錄也會包含許多笑話。這是一個多對多的關(guān)系。
許多沒有經(jīng)驗的設(shè)計者又會想到將幾個數(shù)據(jù)存儲到一個列中，最直接的解決方案是在Jokes表中增加Categories列，并在其中列舉笑話所屬的目錄的ID。現(xiàn)在適用我們的第二個處理規(guī)則了：如果你需要在一個列中存儲多個值，那證明你的設(shè)計可能是有缺陷的。
描述一個多對多關(guān)系的正確方法是使用一個“l(fā)ookup”表。這個表不包含任何實際的數(shù)據(jù)，只是用來定義關(guān)聯(lián)的事物。這兒是我們這部分的數(shù)據(jù)庫設(shè)計的示意圖：
JokeLookup 表將笑話的ID（JID）的目錄的ID（CID）進行了關(guān)聯(lián)。從上面的例子我們可以看出，以“How many lawyers...”開頭的笑話既屬于“Lawyer”目錄，又屬于“Light Bulb”目錄。
建立lookup表的方法和建立其他表的方法基本一樣。不同點在于選擇主鍵。我們之前所建立的每一個表都有一個名為ID的列，這一列被我們定義為PRIMARY KEY。將一個列定義為主鍵意味著這一列不會出現(xiàn)重復值。而且可以加快基于這一列的連接操作的速度。
對于我們的lookup表來說，沒有一個單個的列可以保證不出現(xiàn)重復值。每一個笑話可以屬于幾個目錄，所以一個joke ID可能會出現(xiàn)多次；同樣的，一個目錄可能包含多個笑話，所以一個category ID也可能會出現(xiàn)多次。我們所要求的只是相同的數(shù)據(jù)對不應重復出現(xiàn)。因為我們這個表的唯一作用就是用來實現(xiàn)連接，所以使用主鍵來提高連接操作的速度對我們肯定有價值。所以，我們通常會為lookup表建立一個多列的主鍵：
mysql> CREATE TABLE JokeLookup (
-> JID INT NOT NULL,
-> CID INT NOT NULL,
-> PRIMARY KEY(JID,CID)
-> ); |
現(xiàn)在我們的表中的JID和CID共同組成了這個表的主鍵。保持lookup表中數(shù)據(jù)的唯一性是有價值的（防止重復定義某一個笑話屬于某一個目錄），而且這會提高這個表用來連接時的速度。
使用我們的lookup表中包含的目錄分配，我們可以使用連接來建立幾個有趣而且非常實用的查詢。下面的查詢列出了“Knock-Knock”目錄下的所有笑話：
mysql> SELECT JokeText
-> FROM Jokes, Categories, JokeLookup
-> WHERE Name="Knock-Knock" AND
-> CID=Categories.ID AND JID=Jokes.ID;
|
下面這個查詢列舉了以“How many lawyers...”開頭的笑話所屬的所有目錄：
mysql> SELECT Categories.Name
-> FROM Jokes, Categories, JokeLookup
-> WHERE JokeText LIKE "How many lawyers%"
-> AND CID=Categories.ID AND JID=Jokes.ID;
|
下面的查詢，同時使用了我們的Authors表形成了一個四個表的連接（！！！），列舉了寫過 Knock-Knock笑話的所有作者的名字：
mysql> SELECT Authors.Name
-> FROM Jokes, Authors, Categories, JokeLookup
-> WHERE Categories.Name="Knock-Knock"
-> AND CID=Categories.ID AND JID=Jokes.ID
-> AND AID=Authors.ID;
|
結(jié)語這一章中，我們學習了正確的數(shù)據(jù)庫設(shè)計的基本原則，以及MySQL（實際上，對其他關(guān)系型數(shù)據(jù)庫同樣適用）如何對描述事件之間的不同類型的關(guān)系提供支持。我們不僅僅探討了一對一的關(guān)系，還詳細討論了多對一、一對多以及多對多的關(guān)系。
在這一過程中，我們還學習了一些有關(guān)SQL命令的新的東西。特別的，我們學習了如何使用一個SELECT去連接多個表中的數(shù)據(jù)并將其反映到一個結(jié)果集中。
在第六章中，我們將使用我們已經(jīng)獲得的知識，并加上很少的一些新知識，去用PHP構(gòu)建一個內(nèi)容管理系統(tǒng)。我們希望這個系統(tǒng)可以提供一個可定制的、安全的、基于Web的界面來管理數(shù)據(jù)庫的內(nèi)容，而不再是在MySQL命令行中來解決問題。
from:
http://www0.ccidnet.com/tech/web/2001/12/03/92_3846.html