質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

解決済

MySQLで「タグ同士の関連性」をデータベースに持たせる方法

nikuatsu
nikuatsu

総合スコア172

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

2回答

0リアクション

0クリップ

322閲覧

投稿2022/08/18 12:21

編集2022/08/19 22:03

前提

phpとMySQLでユーザーがタグを付けてコメントを投稿するシステムを作りました。

今回は 「指定タグの関連タグを取得する目的」 で、「タグ同士の関連性」テーブルを作ろうとしています。

実現したいこと

「タグ同士の関連性」テーブルにおいて、適切なプランを知りたいです。

発生している問題

下記プランA、Bを考えていますが、経験が浅くどちらを採用すべきか判断ができません。
どういうケースでどういうメリット・デメリットがあるのか、アドバイスを頂きたく思っております。

「コメントとタグ」テーブル(決定)

まず「コメントとタグ」は3つのテーブルが担い、次のstepでそれぞれへINSERTされます。

step1. ユーザーがコメントにタグをつけてPOSTする。
step2. コメントが tbl_comments へINSERTされる。
step3. タグは tbl_tags へINSERTされる。
step4. タグとコメントの紐付けは tbl_tag_holders へINSERTされる。

SQL

-- コメント CREATE TABLE tbl_comments ( `ID` int AUTO_INCREMENT, `comment` varchar(100) not null, PRIMARY KEY(`ID`), INDEX idx_comments_01 (`comment`)); INSERT INTO tbl_comments (`ID`,`comment`) VALUES (1, 'こんにちは'), (2, 'ありがとう'), (3, 'さようなら'); -- タグ CREATE TABLE tbl_tags ( `ID` int AUTO_INCREMENT, `tag_kind_id` int, `tag_name` varchar(100), PRIMARY KEY(`ID`), UNIQUE u_tags_01 (`tag_kind_id`, `tag_name`)); INSERT INTO tbl_tags (`ID`, `tag_kind_id`, `tag_name`) VALUES -- tag_kind_id は「本、著者、ジャンル」の3種類があります。 (1, 1, '吾輩は猫である'), (2, 2, '夏目漱石'), (3, 3, '恋'), (4, 3, '人生'), (5, 1, '人間失格'), (6, 2, '太宰治'), (7, 3, '罪'); -- リレーション CREATE TABLE tbl_tag_holders ( `comments_ID` int, `tags_ID` int, PRIMARY KEY (`comments_ID`, `tags_ID`), INDEX idx_tag_holders_01 (`tags_ID`, `comments_ID`), CONSTRAINT fk_tag_holders_01 FOREIGN KEY (`comments_ID`) REFERENCES tbl_comments(`ID`), CONSTRAINT fk_tag_holders_02 FOREIGN KEY (`tags_ID`) REFERENCES tbl_tags(`ID`) ); INSERT INTO tbl_tag_holders (`comments_ID`, `tags_ID`) VALUES (1, 1),(1, 2),(1, 3),(1, 4), (2, 5),(2, 6),(2, 7), (3, 1),(3, 2),(3, 4);

そしてこれに続くstepとして

step5. タグ同士に「タグ同士の関連性」を持たせるべく、tbl_tag_relations へINSERTされる。

があるわで、ここにおいて以下プランで悩んでいる次第です。

蛇足

質問と直接関係ありませんが、例えば 'さようなら' のコメントを次のように検索します。

SQL

SELECT C.ID comment_id, GROUP_CONCAT( T.tag_name ) tag_names FROM tbl_comments C LEFT JOIN tbl_tag_holders TH ON TH.comments_ID = C.ID LEFT JOIN tbl_tags T ON T.ID = TH.tags_ID WHERE C.comment = 'さようなら' GROUP BY comment_id;

閑話休題。

「タグ同士の関連性」テーブル(プランA)

まず一方向に関連性を持たせ、tag_uniqueカラムでユニーク性を持たせるプランです。

SQL

-- タグ同士の関連性 CREATE TABLE tbl_tag_relations ( `tags_ID1` int, `tags_ID2` int, `tag_unique` varchar(100) as (concat(least(`tags_ID1`,`tags_ID2`),',',greatest(`tags_ID1`,`tags_ID2`))), `count_relations` int not null default 1, CONSTRAINT fk_tag_relations_01 FOREIGN KEY (`tags_ID1`) REFERENCES tbl_tags(`ID`), CONSTRAINT fk_tag_relations_02 FOREIGN KEY (`tags_ID2`) REFERENCES tbl_tags(`ID`) );

このプランAですと、例えばタグを3つ持っている 'さようなら' のコメントは、次の3レコードで済みます。

SQL

INSERT INTO tbl_tag_relations (`tags_ID1`, `tags_ID2`) VALUES (1, 3),(2, 3),(3, 4);

「タグ同士の関連性」テーブル(プランB)

続いて双方向の関連性を持たせるプランです。

SQL

-- タグ同士の関連性 CREATE TABLE tbl_tag_relations ( `tags_ID1` int, `tags_ID2` int, `count_relations` int not null default 1, CONSTRAINT fk_tag_relations_01 FOREIGN KEY (`tags_ID1`) REFERENCES tbl_tags(`ID`), CONSTRAINT fk_tag_relations_02 FOREIGN KEY (`tags_ID2`) REFERENCES tbl_tags(`ID`) );

このプランBですと、'さようなら' のコメントは次の6レコードが必要となります。

SQL

INSERT INTO tbl_tag_relations (`tags_ID1`, `tags_ID2`) VALUES (1, 3),(3, 1),(2, 3),(3, 2),(3, 4),(4, 3);

メリット・デメリット

上記プランA、Bについて 「指定タグの関連タグを取得する目的」 を基に愚見を申しますと、

まず 'さようなら' のコメントの例で述べたように、プランAの方がtbl_tag_relationsのレコード数が少なくて済む点で良いのかなと思います。

他方SELECTに目を向けてみますと、例えば $target_tag_id=4 と関連するタグを取得するにあたっては、次のコードの違いがあるように思われます。

すなわち、プランAは tags_ID1 と tags_ID2 のそれぞれにWHEREをかけないといけませんが、プランBは tags_ID1 に対してだけで済みますので、この点ではBが良いいのかなと思います。

SQL

-- 指定タグの関連タグを取得する(プランA) SELECT * FROM ( SELECT * FROM tbl_tag_relations TR LEFT JOIN tbl_tags T ON T.ID = TR.tags_ID1 -- tags_ID1 にWHERE WHERE TR.tags_ID1 = $target_tag_id GROUP BY TR.count_relations ORDER BY TR.count_relations DESC LIMIT 10 ) UNION ( SELECT * FROM tbl_tag_relations TR LEFT JOIN tbl_tags T ON T.ID = TR.tags_ID2 -- tags_ID2 にWHERE WHERE TR.tags_ID2 = $target_tag_id GROUP BY TR.count_relations ORDER BY TR.count_relations DESC LIMIT 10 ) LIMIT 10

SQL

-- 指定タグの関連タグを取得する(プランB) SELECT * FROM tbl_tag_relations TR LEFT JOIN tbl_tags T ON T.ID = TR.tags_ID1 -- tags_ID1 に対してだけで済みます WHERE TR.tags_ID1 = $target_tag_id GROUP BY TR.count_relations ORDER BY TR.count_relations DESC LIMIT 10

このようにメリット・デメリットがよく掴み切れず、どちらが良いのかイマイチ整理がつきません。

ツールのバージョンなど

それぞれ次のバージョンです。

PHP 8.0
MySQL 5.7.2

宜しくお願い致します。

以下のような質問にはリアクションをつけましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

リアクションが多い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

下記のような質問は推奨されていません。

  • 間違っている
  • 質問になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

適切な質問に修正を依頼しましょう。

yambejp

2022/08/19 00:32 編集

いまいち理解ができてないのですが tbl_tag_relationsのA案は単純にtbl_tag_holders のことではないでしょうか? あ、ちがうか。コメントを無視して任意に特定タグ同士を関連付けるのでしょうか その場合、1と3、2と3が関連付けられているのに1と2は関連しなくてよいということ?
nikuatsu

2022/08/19 22:03

> tbl_tag_relationsのA案は単純にtbl_tag_holders のことではないでしょうか たしかに tbl_tag_holders から導くことができる値だとは思います。しかし例えば「tag_id=4と関連性の高いタグを順で(count_relations DESC 順で)取得しよう」となったとき tbl_tag_holders に対しどうSELECTをかけたらいいのか…かなり大変そうに思えます。 あるタグページを閲覧した際に「そのタグと関連性の高いタグはこちら」と表示したいと思っていて、その際のSELECTをかけるのがやりやすいかと思い、tbl_tag_relations を作りました。 > コメントを無視して任意に特定タグ同士を関連付けるのでしょうか いえ。その機能は考えておらず、(というか)タグ同士を関連付ける適当な方法が思いつかず、ひとまず次のルールにしてみました。 「step1でコメントに付けられたタグが、その場合のみタグ同士の関連性を持つ」です。 > その場合、1と3、2と3が関連付けられているのに1と2は関連しなくてよいということ? はい、上述のルールに従い、1と2は関連させません。あるコメントに1と2が付けられた場合のみ、1と2は関連させるつもりです。

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。