MySQL,MariaDBでタグ機能（TAG)を実現する(TOXI法）①タグ関連のスキーマ設計

MySQLでタグのしくみを作る？

MySQLでタグの仕組みを作る場合は、どのような設計をすればいいのでしょうか？ タグの設計にはいくつかの種類があり

• Licious：コンテンツ系のデーブル1つの中にタグフィールドを持たせる方法
• Scuttle: コンテンツ系とタグ系のテーブル２つでまかなうお不法
• TOXI : コンテンツテーブル - 中間テーブル、タグテーブルの三種類からなる、TOXI等があります。

色々と調べた結果、TOXI方式が一番、納得感がありましたので、以降TOXI方を中心に説明していきます。

TOXI法では、

• コンテンツ（日記とか、画像とか動画でもいいや）等のメインのテーブル
• コンテンツと対象のタグのid
• タグ名

の３つのテーブルを使ってタグ管理していきます。

ニューバランス（newbalance） トレーニングシューズ JSVRCTWT WHITE/TOXI 23.0cm

Logicool ロジクール ワイヤレス ミニマウス ホワイト M187WH

テーブル構成

例えば、以下のようなテーブル構成があったとします。

三菱電機 アルカリ乾電池(シュリンクパック) 単4形 10本パック LR03N/10S

• 日記テーブル

CREATE TABLE `diaries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) not null default '0',
  `title` varchar(128) NOT NULL DEFAULT '',
  `detail` text ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

• タグマップテーブル

CREATE TABLE `tag_maps` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `diary_id` bigint(20) unsigned NOT NULL,
  `tag_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

• タグテーブル

CREATE TABLE `tags` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix01_tags` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

データを投入してみる

ちょっと、定義だけですとわからないので、データを投入してみます。

• diary

> SELECT * FROM diary \G
*************************** 1. row ***************************
        id: 1
created_at: 2016-10-25 19:58:11
updated_at: 2016-10-25 19:58:11
   user_id: 0
     title: 今日の天気
    detail: 今日はいい天気だ、ポカポカ気持ちいい
1 row in set (0.00 sec)

• tags

> SELECT * FROM tags \G
*************************** 1. row ***************************
        id: 1
created_at: 2016-10-25 19:59:53
updated_at: 2016-10-25 19:59:53
      name: 天気
*************************** 2. row ***************************
        id: 2
created_at: 2016-10-25 19:59:53
updated_at: 2016-10-25 19:59:53
      name: 晴れ
2 rows in set (0.17 sec)

• tag_maps

> SELECT * FROM tag_maps \G
*************************** 1. row ***************************
        id: 1
created_at: 2016-10-25 20:02:02
updated_at: 2016-10-25 20:02:02
  diary_id: 1
    tag_id: 1
*************************** 2. row ***************************
        id: 2
created_at: 2016-10-25 20:02:02
updated_at: 2016-10-25 20:02:02
  diary_id: 1
    tag_id: 2
2 rows in set (0.00 sec)

もうデータ見るとわかっちゃうかもしれませんが、ここでのポイントは、tag_maps テーブルになります。tag_maps は、diary テーブルのidを把握し、それに関連する、tag名称と紐付けています。 これをSQLで結合して出しますと、この日記には、どのタグが入っているか確認することができます。

SQL

SELECT
  d.id AS diary_id,
  d.title AS title ,
  d.detail AS detail,
  GROUP_CONCAT(tag.name SEPARATOR ',') AS tag
FROM
  diary d
INNER JOIN
  tag_maps map
ON
  d.id = map.diary_id
INNER JOIN
  tags tag
ON
  map.tag_id = tag.id
GROUP BY
  d.id \G
*************************** 1. row ***************************
diary_id: 1
   title: 今日の天気
  detail: 今日はいい天気だ、ポカポカ気持ちいい
     tag: 天気,晴れ
1 row in set (0.00 sec)

特に注目いただきたいのが、tagというカラムですね。この日記には、天気と晴れというタグが設定できていると表現できるわけです。 こちらの例は、日記というコンテンツを起点としたSQLになりますが、角度を変えれば、

• タグに"晴れ"を設定している日記の一覧を出すSQL

とか出すこともTOXI法であれば、柔軟にできますよね。

後ほど、細かく書きますので、気長にお待ち下さい。