用PHP和MySQL構建一個數據庫驅動的網站(八)
2024-07-24 12:56:18
供稿：網友
摘要
在這一章中，我們會對我們的例子進行擴充，學習一些有關mysql的新知識，并試圖理解并掌握關系型數據庫所能提供的功能。
(2002-08-29 14:11:39)
--------------------------------------------------------------------------------
by wing, 出處：linuxaid
第五章：關系型數據庫設計
在這篇文章的第二章中，我們已經建立了一個供我們使用的非常簡單的笑話數據庫，這個庫中只包括了一個名叫jokes的數據表。這作為我們使用mysql數據庫的入門已經是足夠了，但是在關系型數據庫的設計中還有很多其它的東西。在這一章中，我們會對我們的例子進行擴充，學習一些有關mysql的新知識，并試圖理解并掌握關系型數據庫所能提供的功能。
首先，我們得說明我們對許多問題的解決只是不正規的（也就是說非正式的）。正如你在許多計算機科學專業中了解的那樣，數據庫設計是一個嚴肅的領域，數據庫設計必須包括對它的測試并會涉及到一些數學的原理。但這些可能是超過我們這篇文章的范圍了。要得到更多的信息，你可以停下來到http://www.datamodel.org/去看看，在那兒你可以看到許多好的書籍，并得到一些關于這個問題的有用的資源。
給予應有的權限
在開始之前，讓我們回憶一下我們的jokes數據表的結構，這個表包含三個列：id、joketext和 jokedate。這些列可以使我們標識笑話（id），明了他們的內容（joketext）以及他們被加入的時間（jokedate）。
現在我們想要保存我們的笑話中的其它一些信息：提交者的姓名。這看上去很自然，我們需要在我們的jokes數據表中添加一個新的列。sql的alter命令（我們在之前沒看到過這個命令）可以幫助我們完成這件事。使用mysql命令行程序登錄到mysql服務器，選擇你的數據庫（如果你使用我們在第二章中的命名，數據庫名應該是joke），然后輸入下面的命令：
mysql>
alter table jokes add column
-> authorname varchar(100);
這將會在我們的數據表中增加一個叫authorname的列。其數據類型是一個可變長度的字符串，其最大長度是100個字符（這對于最復雜的名字應該也是足夠了）。讓我們再添加一列用來保存作者的e-mail地址：
mysql> alter table jokes add column
-> authoremail varchar(100);
要得到更多的有關alter命令的信息，請參看mysql參考手冊。要確認我們是不是正確地添加了兩列，你可以要求mysql為我們對這個表進行描述：
mysql> describe jokes;
+-------------+--------------+------+-----+-- - -
| field | type | null | key | def...
+-------------+--------------+------+-----+-- - -
| id | int(11) | | pri | ...
| joketext | text | yes | | ...
| jokedate | date | | | ...
| authorname | varchar(100) | yes | | ...
| authoremail | varchar(100) | yes | | ...
+-------------+--------------+------+-----+-- - -
5 rows in set (0.01 sec)
看上去很不錯。明顯地，我們需要對我們在第四章中建立的添加新笑話的html以及php格式的代碼進行調整，但是我們會把這留給你作為一個練習。使用update查詢，你現在可以對表中的所有笑話添加作者的詳細資料。然而，在你開始接受這個數據結構之前，我們必須考慮一下我們在這兒選擇的設計是否確當。在這種情況下，我們會發現一些我們還沒有做到的事情。
一個基本的規則：保持事物的分離
在你建立數據庫驅動的網站的過程中，你已經覺得僅僅是有一個笑話列表是不夠的。事實上，除了你自己的笑話以外，你開始接收其他人提交的笑話。你決定做一個讓全世界人都可以共享笑話的網站。你有沒有聽說過internet電影數據庫（imdb）？實際上你現在做的是internet笑話數據庫（ijdb）！對每一個笑話添加作者的姓名和e-mail地址肯定是最容易想到的辦法，但是這種方法會導致一些潛在的問題：
如果一個經常投稿的名叫joan smith的人改變了她的e-mail地址將會發生什么什么情況呢？她會開始使用新地址來提交新的笑話，但是對于所有的舊笑話，你所能看到的還是舊的地址。從你的數據庫來看，你也許只能認為有兩人名字都叫joan smith的人在向你的數據庫中提交笑話。如果她是特別體貼的，她也許會通知你改變地址，你可以將所有的舊笑話改成新的地址，但是如果你遺漏了一個，那就意味著你的數據庫中存儲了錯誤的信息。數據庫設計專家將這種類型的問題稱之為一個“更正異?！?。
很自然地你會想到從你的數據庫中得到所有曾經向你的站點提交過笑話的人的列表。實際上，你可以使用下面的查詢很容易地得到這樣的列表：
mysql> select distinct authorname, authoremail -> from jokes;
上面查詢中distinct是告訴mysql不輸出重復的結果行。例如，如果joan smith向我們的站點提交過20個笑話，如果我們使用了distinct選項，她的名字和e-mail地址將會只在列表中出現一次，否則會出現20次。
如果因為某種原因，你決定要從數據庫中刪除某個特定的作者所提交的所有笑話，但是，與此同時，你將不能再通過e-mail與他們聯系！而你的e-mail清單可能是你的網站的收入的主要來源，所以你并不想只因為你不喜歡他們提交的笑話，就刪除他們的e-mail地址。數據庫設計專家將這稱之為“刪除異?！?。
你并不能保證不會出現這樣的情況：joan smith輸入的姓名一會兒是“joan smith”，一會兒是“j. smith”，一會兒又是“smith, joan”。這將使得你要確定一個特定的作者變得非常困難（特別是joan smith又經常使用幾個不同的email地址的時候）。
這些問題的解決其實很簡單。只要你不再將作者的信息存儲到jokes數據表中，而是建立一個新的數據表來存儲作者列表。因為我們在jokes數據表中使用了一個叫id的列來用一個數據標識每個笑話，所以我們在新的數據表中使用了同樣名字的列來標識我們的作者。我們可以在我們的jokes表中使用“author id's”來建立笑話和他的作者之間的關聯。全部的數據庫設計應該是這樣的：
上面的兩個表包含了三個笑話和兩個作者。jokes表的aid列（“author id”的縮寫）提供了兩個表之間的關聯（指出kevin yank 提交了笑話1和笑話2，joan smith提交了笑話3）。在這里，你還需要注意到每一個作者只會在數據庫中出現一次，而且他們是獨立于他們提交的笑話而存在的，因此我們已經解決了我們上面提出的那些問題。
這個數據庫設計的最重要的特征是，因為我們要存儲兩種類型的事物（笑話和作者），所以我們設計兩個表。這是我們在數據庫設計中要遵守的一個基本規則：對于每一個要存儲其信息的實體（或事物），我們都應該給他一個自己的表。
重新生成上面的數據是非常簡單的（只要使用兩個create table 查詢就行了），但是因為我們想要在做這些變動時不會有破壞性的效果（也就是說不會丟失我們已經存入的笑話），所以我們需要再次使用alter命令。 首先，我們刪除jokes表中有關作者的列：
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
現在我們建立我們的新的數據表：
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;
現在剩下來的就是向新的表中添加一些作者，并通過填充aid列來對數據庫中已經存在的笑話指定作者。
處理多個表
現在我們的數據被分布在兩個表當中，要從其中獲得數據看上去變得更加復雜了。例如，我們最初的目標是：顯示一個笑話的列表并在每一個笑話后面顯示作者的姓名和e-mail地址。在我們的單表結構中，要獲得所有的信息，只需要在我們的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>" );
}
在我們的新系統中，這樣做初看起來是不可能了。因為有關每個笑話的作者的詳細資料不是存儲在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>" );
}
很混亂，而且對于每一個顯示的笑話都包含了一個對數據庫的查詢，這將會我們的頁面的顯示非常緩慢?，F在看來，“老方法”可能是更好的解決方案，盡管它有其自身的弱點。
幸運的是，關系型數據庫可以很容易地處理多個表中的數據！在select語句中使用一個新的被稱之為“join”的格式，我們可以找到兩全其美的辦法。連接可以使我們象對存儲在單個表中的數據那樣對待多個表中的關聯數據。一個連接的格式應該是這樣的：
mysql> select <columns> from <tables>
-> where <condition(s) for data to be related>
