PHPとMySQLで検索システムー2つの要素から該当する記事を表示

Question

PHPとMySQLを一通り学んだのですが、まだまだデータベースの設計は勉強不足なのようで、アドバイスを頂きたく思います。

電車の「線」と「駅名」を選択すると該当する「記事(タイトルと記事名、線、駅名)」が表示される検索システムを作りたいと考えています。

ex. 「南武線」「川崎駅」 → 記事A、記事B、、、 （検索結果は一つは限りません。）

そこで
「線」と「駅名」で1テーブル、 「記事」で1テーブルとし、それらのテーブルを関連付ける？やり方がデータベースで重複を防ぐ最善策かと思ったのですが、その関連付ける方法がわかりません。。。

テーブルを分けた場合に両者を関連づける方法、またはもっと単純明快なテーブルの作り方などがあればお教えください。
よろしくお願いします。

※言葉足らずの部分があるかと思いますが適宜説明を加えさせていただきたく思います。

Answer 1

自分だったら、基本的には、以下のように設計します。インデックスとかは省略してます。

CREATE TABLE `ARTICLES` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `station_id` int(11) DEFAULT NULL,
  `content` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `LINES` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `line_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `LINK_LINE_STATION` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `line_id` int(11) DEFAULT NULL,
  `staion_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `STATIONS` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `staion_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SELECT 
	`ARTICLES`.`id`
	, `ARTICLES`.`content`
	, `STATIONS`.`station_name`
	, `LINES`.`line_name`
FROM 
	`ARTICLES`
INNER JOIN 
	`STATIONS` 
ON 
	`ARTICLES`.`station_id` = `STATIONS`.`id`
LEFT JOIN 
	`LINK_LINE_STATION`
ON 
	`STATIONS`.`id` = `LINK_LINE_STATION`.`staion_id`
LEFT JOIN
	`LINES`
ON 
	`LINK_LINE_STATION`.`line_id` = `LINES`.`id`
WHERE 
	`LINES`.`line_name` LIKE '%路線名%'

Comments

[twin_bird]

コードまでかいていただきありがとう御座います。
ばらばらに分けるのですね〜　参考にさせていただきます！

ところで、この場合、検索結果を表示させる場合はどのようなコードをかくのでしょうか？

[退会済みユーザー]

何えお検索条件にして、どの値を取り出したいかによって変わります。

Answer 2

「線」と「駅名」で1テーブル

これだと、「駅名」だけでぶら下げる記事が作りづらくなるし、
検索時も、「駅名」だけで引っ張ってきたいといった時も、対処しづらくなる。

あと、内容が「記事」であれば、作成日時（更新日時・投稿日時）が欲しい。
最新の記事がどれなのかわからなくなってしまう。

DB設計のの手順としては、

１．取り扱いたい項目を全て書き出して並べる。
「タイトル」「記事」「線」「駅名」「作成日時」

２．データを登録していくと重複する項目（またはグループ）で分ける。（第一正規化）
・「タイトル」「記事」「作成日時」
・「線」
・「駅名」

３．分けたグループごとに、データを一意に決定できる項目（以降、主キー）を選択、
なければ追加（第二正規化①）
・「タイトル」「記事」「作成日時」→「記事ID」（登録時自動採番）を追加
・「線」→「路線コード」（一意となる任意コード）を追加
・「駅名」→「駅コード」（一意となる任意コード）を追加

４．分けたグループを主キー関連付けさせる（第二正規化②）
・「記事ID」「タイトル」「記事」「作成日時」→「路線コード」「駅コード」追加
・「路線コード」「線」
・「駅コード」「駅名」

と言った感じ。多少我流が入ってるかもしれないので。
正規化については、ネットで知識を深めてください。

Comments

[twin_bird]

回答ありがとうございます。
どういう手順で設計していけばいいのか迷っていたので参考になります！