PHP と MySQL を使用してデータベース主導の Web サイトを構築する (8)

mysql|データ|データベース

概要

この章では、例を拡張し、MySQL に関するいくつかの新しい知識を学び、リレーショナル データベースが提供できる機能を理解して習得しようとします。

(2002-08-29 14:11:39)

--------------------------------- --------------------------------------------------
Wing 著、出典: Linuxaid


第 5 章: リレーショナル データベースの設計

この記事の第 2 章では、使用する非常に単純なジョーク データベースを確立しました。このライブラリには、Jokes データシートという名前のデータベースのみが含まれています。 MySQL データベースの使用の入門としてはこれで十分ですが、リレーショナル データベースの設計には他にも多くのことがあります。この章では、例を拡張し、MySQL に関するいくつかの新しい知識を学び、リレーショナル データベースが提供できる機能を理解して習得しようとします。

まず第一に、多くの問題に対する私たちの解決策は非公式（つまり非公式）にすぎないことを説明しなければなりません。多くのコンピューター サイエンスを専攻している方はご存知のとおり、データベース設計は重要な分野であり、データベース設計にはデータベースのテストといくつかの数学的原理が含まれる必要があります。ただし、これらはこの記事の範囲を超えている可能性があります。詳細については、http://www.datamodel.org/ にアクセスしてください。そこでは、多くの優れた書籍が参照でき、このテーマに関する有用なリソースが入手できます。

適切な権限を与えてください

始める前に、Jokes データ テーブルの構造を思い出してください。このテーブルには、ID、JokeText、JokeDate の 3 つの列が含まれています。これらの列を使用すると、ジョーク (ID)、その内容 (JokeText)、追加された時刻 (JokeDate) を識別できます。

ここで、ジョークに関するその他の情報、送信者の名前を保存したいと思います。 Jokes データ テーブルに新しい列を追加する必要があるのは自然なことのように思えます。 SQL の ALTER コマンド (このコマンドはこれまで見たことがありません) は、これを達成するのに役立ちます。 mysql コマンド ライン プログラムを使用して MySQL サーバーにログインし、データベースを選択し (第 2 章で使用した名前を使用した場合、データベース名はジョークにする必要があります)、次のコマンドを入力します。 ADD COLUMN
—> AuthorName VARCHAR(100);



これにより、AuthorName という列がデータテーブルに追加されます。そのデータ型は、最大長が 100 文字の可変長文字列です (最も複雑な名前にはこれで十分です)。作成者の電子メール アドレスを保持する別の列を追加しましょう:


mysql> ALTER TABLE Jokes ADD COLUMN



ALTER コマンドの詳細については、MySQL を参照してください。参考マニュアル。 2 つの列が正しく追加されたことを確認するには、MySQL にテーブルの説明を依頼できます。 --------+-----+-----+-- -
キー | ------+--------------+------+-----+-- -
| | ...
| ジョーク日付 | varchar (100) |はい | ...
+----------+--------------+-----+-- ---+ -- - -
5行セット(0.01秒)



良さそうです。明らかに、新しいジョークを追加するには、第 4 章で作成した HTML と PHP の書式設定コードを調整する必要がありますが、それは演習として残しておきます。 UPDATE クエリを使用して、テーブル内のすべてのジョークに著者の詳細を追加できるようになりました。ただし、このデータ構造を受け入れる前に、ここで選択した設計が正しいかどうかを検討する必要があります。この場合、まだやっていないことを発見します。

基本的なルール: 物事を分けておく

データベース主導の Web サイトを構築する際に、ジョークのリストを作成するだけでは十分ではないことに気づいたかもしれません。実際、自分自身だけでなく、他の人からもジョークの投稿を受け取り始めます。あなたは、世界中の人々がジョークを共有できる Web サイトを作成することにしました。インターネット ムービー データベース (IMDB) について聞いたことがありますか?実際、あなたが取り組んでいるのは Internet Joke Database (IJDB) です。すべてのジョークに作者の名前と電子メール アドレスを追加することは確かに最も簡単に考えられることですが、このアプローチはいくつかの潜在的な問題を引き起こします:

Joan Smith という名前の頻繁な寄稿者が電子メール アドレスを変更したらどうなるでしょうか?彼女は新しいアドレスを使用して新しいジョークの送信を開始しますが、古いジョークはすべて古いアドレスだけが表示されます。データベースから判断すると、Joan Smith という名前の 2 人がジョークをデータベースに送信していると考えることができます。彼女が特に思いやりを持っている場合は、アドレス変更を通知してくれるかもしれません。古いジョークをすべて新しいアドレスに変更することもできますが、1 つでも変更しなければ、データベースに間違った情報が保存されていることになります。データベース設計の専門家は、この種の問題を「修正例外」と呼んでいます。

これまでにサイトにジョークを投稿したデータベース内のすべての人のリストを取得したいと思うのは自然なことです。実際、次のクエリを使用すると、そのようなリストを簡単に取得できます:

mysql> SELECT DISTINCT AuthorName, AuthorEMail -> FROM Jokes;


; 上記のクエリの DISTINCT は、重複した結果行を出力しないように MySQL に指示します。たとえば、Joan Smith がサイトに 20 件のジョークを送信した場合、彼女の名前と電子メール アドレスは、DISTINCT オプションを使用すると 1 回だけリストに表示され、それ以外の場合は 20 回表示されます。

何らかの理由で、特定の作者が投稿したすべてのジョークをデータベースから削除することにした場合、それまでの間、電子メールで連絡を取ることはできなくなります。また、電子メール リストはおそらく Web サイトの主な収益源であるため、送信されたジョークが気に入らないからといって、その電子メール アドレスを削除することは望ましくありません。データベース設計の専門家は、これを「削除異常」と呼んでいます。

この状況が起こらないという保証はありません。Joan Smith が入力した名前は、しばらくの間は「Joan Smith」、しばらくの間は「J. Smith」、そしてしばらくの間は「Smith, Joan」になります。これにより、特定の作成者を特定することが非常に困難になります (特に、Joan Smith は複数の異なる電子メール アドレスを使用することが多いため)。

これらの問題の解決策は実はとても簡単です。ただし、著者の情報を Jokes データ テーブルに保存するのではなく、著者リストを保存する新しいデータ テーブルを作成する必要があります。 Jokes データ テーブルの ID という列を使用して各ジョークを番号で識別したため、新しいデータ テーブルでは同じ名前の列を使用して作成者を識別します。ジョーク テーブルで「著者 ID」を使用して、ジョークとその著者との関連付けを確立できます。データベース全体の設計は次のようになります:

上の 2 つのテーブルには 3 つのジョークと 2 人の著者が含まれています。 Jokes テーブルの AID 列 (「著者 ID」の略) は、2 つのテーブル間の関連付けを提供します (Kevin Yank がジョーク 1 と 2 を提出し、Joan Smith がジョーク 3 を提出したことに注意してください)。ここで、各著者はデータベースに 1 回だけ表示され、投稿したジョークとは無関係に存在するため、上で挙げた問題はすでに解決済みであることにも注意する必要があります。

このデータベース設計の最も重要な特徴は、2 種類のもの (ジョークと著者) を保存したいため、2 つのテーブルを設計していることです。これはデータベース設計で従わなければならない基本的なルールです。情報を保存したいすべてのエンティティ (または物) に対して、独自のテーブルを与える必要があります。

上記のデータの再生成は非常に簡単です (CREATE TABLE クエリを 2 つ使用するだけです) が、破壊的な影響を与えずに (つまり、既存のデータを失わずに) 冗談のようにこれらの変更を加えたいため、ALTER コマンドを使用する必要があります。また。 まず、Jokes テーブルの author 列を削除します:


mysql> ALTER TABLE Jokes DROP COLUMN AuthorName;
Query OK、影響を受ける行は 0 (0.00 秒)
Records: 0 Duplicates: 0 Warnings: 0
mysql>ジョーク DROP COLUMN AuthorEMail;
クエリ OK、影響を受ける行は 0 (0.00 秒)
レコード: 0 重複: 0 警告: 0
次に、新しいデータ テーブルを作成します:
mysql> CREATE TABLE Authors (
-> ID INT NOT NULL) AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(100),
-> EMail VARCHAR(100)
-> );
最後に、AID 列を Jokes テーブルに追加します。 AID INT;



あとは、新しいテーブルに著者を追加し、AID 列に値を入力して、データベースにすでに存在するジョークに著者を割り当てるだけです。

複数のテーブルの処理

データが 2 つのテーブルに分散されているため、それらのテーブルからデータを取得することはより複雑になるようです。たとえば、私たちの当初の目標は、ジョークのリストを表示し、各ジョークの後に作成者の名前と電子メール アドレスを表示することでした。単一のテーブル構造ですべての情報を取得するには、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"];

// 著者情報付きジョークを表示
echo( "

$joketext
" .
" "(by $name)

" );
}



私たちの新しいシステムでは、これは一見不可能に見えるかもしれません。各ジョークの作者に関する詳細は Jokes テーブルに保存されないため、考えられる解決策の 1 つは、表示したいジョークについてこの情報を個別に取得することです。コードは次のようになります:


// ジョークのリストを取得します
$jokelist = mysql_query(
"SELECT JokeText, AID FROM Jokes");

while ($joke = mysql_fetch_array($jokelist)) {

// ジョークのテキストと著者 ID を取得します
$joketext = $joke["JokeText"];
$aid = $joke["AID"];
// ジョークの著者詳細を取得します
$authordetails = mysql_query (
"SELECT Name, Email FROM Authors WHERE ID=$aid");
$author = mysql_fetch_array($authordetails);
$name = $author["Name"];
$email = $author["EMail"] ;

// ジョークを作者情報とともに表示します
echo( "

$joketext
" .
"(by $name)

" ) ;
}



これは混乱を招き、表示されるすべてのジョークに対してデータベースへのクエリが含まれるため、ページの表示が非常に遅くなります。現在では、「古い方法」がより良い解決策である可能性があるように見えますが、独自の弱点もあります。

幸いなことに、リレーショナル データベースを使用すると、複数のテーブルのデータを簡単に処理できます。 SELECT ステートメントで「結合」と呼ばれる新しい形式を使用すると、両方の長所を見つけることができます。結合を使用すると、複数のテーブル内の関連データを、あたかも 1 つのテーブルに格納されているかのように扱うことができます。接続の形式は次のようになります:



SELECT FROM
-> WHERE