前提
phpとMySQLでユーザーがタグを付けてコメントを投稿するシステムを作りました。
今回は 「指定タグの関連タグを取得する目的」 で、「タグ同士の関連性」テーブルを作ろうとしています。
実現したいこと
「タグ同士の関連性」テーブルにおいて、適切なプランを知りたいです。
発生している問題
下記プランA、Bを考えていますが、経験が浅くどちらを採用すべきか判断ができません。
どういうケースでどういうメリット・デメリットがあるのか、アドバイスを頂きたく思っております。
「コメントとタグ」テーブル(決定)
まず「コメントとタグ」は3つのテーブルが担い、次のstepでそれぞれへINSERTされます。
step1. ユーザーがコメントにタグをつけてPOSTする。
step2. コメントが tbl_comments
へINSERTされる。
step3. タグは tbl_tags
へINSERTされる。
step4. タグとコメントの紐付けは tbl_tag_holders
へINSERTされる。
SQL
1-- コメント 2CREATE TABLE tbl_comments ( 3 `ID` int AUTO_INCREMENT, 4 `comment` varchar(100) not null, 5 PRIMARY KEY(`ID`), 6 INDEX idx_comments_01 (`comment`)); 7 8INSERT INTO tbl_comments (`ID`,`comment`) 9VALUES 10(1, 'こんにちは'), 11(2, 'ありがとう'), 12(3, 'さようなら'); 13 14-- タグ 15CREATE TABLE tbl_tags ( 16 `ID` int AUTO_INCREMENT, 17 `tag_kind_id` int, 18 `tag_name` varchar(100), 19 PRIMARY KEY(`ID`), 20 UNIQUE u_tags_01 (`tag_kind_id`, `tag_name`)); 21 22INSERT INTO tbl_tags (`ID`, `tag_kind_id`, `tag_name`) 23VALUES -- tag_kind_id は「本、著者、ジャンル」の3種類があります。 24(1, 1, '吾輩は猫である'), 25(2, 2, '夏目漱石'), 26(3, 3, '恋'), 27(4, 3, '人生'), 28(5, 1, '人間失格'), 29(6, 2, '太宰治'), 30(7, 3, '罪'); 31 32-- リレーション 33CREATE TABLE tbl_tag_holders ( 34 `comments_ID` int, 35 `tags_ID` int, 36 PRIMARY KEY (`comments_ID`, `tags_ID`), 37 INDEX idx_tag_holders_01 (`tags_ID`, `comments_ID`), 38 CONSTRAINT fk_tag_holders_01 FOREIGN KEY (`comments_ID`) REFERENCES tbl_comments(`ID`), 39 CONSTRAINT fk_tag_holders_02 FOREIGN KEY (`tags_ID`) REFERENCES tbl_tags(`ID`) ); 40 41INSERT INTO tbl_tag_holders 42(`comments_ID`, `tags_ID`) 43VALUES 44(1, 1),(1, 2),(1, 3),(1, 4), 45(2, 5),(2, 6),(2, 7), 46(3, 1),(3, 2),(3, 4);
そしてこれに続くstepとして
step5. タグ同士に「タグ同士の関連性」を持たせるべく、tbl_tag_relations へINSERTされる。
があるわで、ここにおいて以下プランで悩んでいる次第です。
蛇足
質問と直接関係ありませんが、例えば 'さようなら' のコメントを次のように検索します。
SQL
1SELECT C.ID comment_id, 2 GROUP_CONCAT( T.tag_name ) tag_names 3FROM tbl_comments C 4 LEFT JOIN tbl_tag_holders TH ON TH.comments_ID = C.ID 5 LEFT JOIN tbl_tags T ON T.ID = TH.tags_ID 6WHERE C.comment = 'さようなら' 7GROUP BY comment_id;
閑話休題。
「タグ同士の関連性」テーブル(プランA)
まず一方向に関連性を持たせ、tag_unique
カラムでユニーク性を持たせるプランです。
SQL
1-- タグ同士の関連性 2CREATE TABLE tbl_tag_relations ( 3 `tags_ID1` int, 4 `tags_ID2` int, 5 `tag_unique` varchar(100) as (concat(least(`tags_ID1`,`tags_ID2`),',',greatest(`tags_ID1`,`tags_ID2`))), 6 `count_relations` int not null default 1, 7 CONSTRAINT fk_tag_relations_01 FOREIGN KEY (`tags_ID1`) REFERENCES tbl_tags(`ID`), 8 CONSTRAINT fk_tag_relations_02 FOREIGN KEY (`tags_ID2`) REFERENCES tbl_tags(`ID`) );
このプランAですと、例えばタグを3つ持っている 'さようなら' のコメントは、次の3レコードで済みます。
SQL
1INSERT INTO tbl_tag_relations 2(`tags_ID1`, `tags_ID2`) 3VALUES 4(1, 3),(2, 3),(3, 4);
「タグ同士の関連性」テーブル(プランB)
続いて双方向の関連性を持たせるプランです。
SQL
1-- タグ同士の関連性 2CREATE TABLE tbl_tag_relations ( 3 `tags_ID1` int, 4 `tags_ID2` int, 5 `count_relations` int not null default 1, 6 CONSTRAINT fk_tag_relations_01 FOREIGN KEY (`tags_ID1`) REFERENCES tbl_tags(`ID`), 7 CONSTRAINT fk_tag_relations_02 FOREIGN KEY (`tags_ID2`) REFERENCES tbl_tags(`ID`) );
このプランBですと、'さようなら' のコメントは次の6レコードが必要となります。
SQL
1INSERT INTO tbl_tag_relations 2(`tags_ID1`, `tags_ID2`) 3VALUES 4(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
1-- 指定タグの関連タグを取得する(プランA) 2SELECT * 3FROM ( 4 SELECT * 5 FROM tbl_tag_relations TR 6 LEFT JOIN tbl_tags T 7 ON T.ID = TR.tags_ID1 8 9 -- tags_ID1 にWHERE 10 WHERE TR.tags_ID1 = $target_tag_id 11 12 GROUP BY TR.count_relations 13 ORDER BY TR.count_relations DESC 14 LIMIT 10 15) UNION ( 16 SELECT * 17 FROM tbl_tag_relations TR 18 LEFT JOIN tbl_tags T 19 ON T.ID = TR.tags_ID2 20 21 -- tags_ID2 にWHERE 22 WHERE TR.tags_ID2 = $target_tag_id 23 24 GROUP BY TR.count_relations 25 ORDER BY TR.count_relations DESC 26 LIMIT 10 27) 28LIMIT 10
SQL
1-- 指定タグの関連タグを取得する(プランB) 2SELECT * 3FROM tbl_tag_relations TR 4 LEFT JOIN tbl_tags T 5 ON T.ID = TR.tags_ID1 6 7-- tags_ID1 に対してだけで済みます 8WHERE TR.tags_ID1 = $target_tag_id 9 10GROUP BY TR.count_relations 11ORDER BY TR.count_relations DESC 12LIMIT 10
このようにメリット・デメリットがよく掴み切れず、どちらが良いのかイマイチ整理がつきません。
ツールのバージョンなど
それぞれ次のバージョンです。
PHP 8.0
MySQL 5.7.2
宜しくお願い致します。

回答2件
あなたの回答
tips
プレビュー