どうも、コウイチです。

今回は、MySQLでタグ付け機能を実装してみます。

今回タグ機能の実装を初めてやることになり、色々調査していたところ、なんとか法という名前がついた設計が色々あるみたいなので、それを有り難く使わせてもらいました。

例として、ブログ記事に色々なタグをつけて、それをSQL文でごにょごにょやるというのを考えてみます。

まずは仕様をご覧ください。

タグ付け機能の仕様

記事一覧(3つの記事を用意しました)

タイトル 内容
PHP記事 これはPHPに関する記事です。
C#記事 これはC#に関する記事です。
PHP&MySQL記事 これはPHPとMySQLに関する記事です。
雑記 これは雑記です。

タグ一覧(5つのタグを用意しました)

タグ名
Java
PHP
MySQL
JavaScript
C#

3つの記事それぞれに、用意したタグをセットしてみると、以下のようになりますね。

記事タイトル 記事内容 タグ
PHP記事 これはPHPに関する記事です。 PHP
C#記事 これはC#に関する記事です。 C#
PHP&MySQL記事 これはPHPとMySQLに関する記事です。 PHP, MySQL
雑記 これは雑記です。
  • 一つもタグが紐づいていない記事があってもよい。
  • 一つの記事に対して、複数のタグを紐づけることができる。

という仕様です。

テーブル設計①「一個のテーブルでやる」MySQLicious法

まず最初に思いつくのはこんな感じでしょうか。entry(記事)テーブルを作って、そこにタグの情報も含めてしまいます。

entryテーブル

id title detail tag
1 PHP記事 これはPHPに関する記事です。 PHP
2 C#記事 これはC#に関する記事です。 C#
3 PHP&MySQL記事 これはPHPとMySQLに関する記事です。 PHP
MySQL
4 雑記 これは雑記です。

このようにすると・・・

メリット

  • テーブルが一つで済むからなんとなく楽な気がする。

デメリット

  • 3番目の記事ではタグを2つ紐づけたいわけですが、改行をするなどして複数のタグをtagカラムに詰め込まなくてはならないので、後からタグを追加したり削除したり(その他にもいろいろと)手間がかかる。
  • タグ名を変えたくなったときに、tagカラムの中身を一個一個書き換えなければならない。

この設計には名前がついているようで、「MySQLicious法」というそうです。

テーブルは一つで済むけど、後で管理するのが結構大変です。

ようするに正規化できていない設計というわけです。

そこで、テーブルをもう一つ追加したのが次です。

テーブル設計②「2つのテーブルでやる」Scuttle法

先ほどの例から発展して、テーブルをもう一つ追加して、設計してみるとこうなります。

entryテーブル

id title detail
1 PHP記事 これはPHPに関する記事です。
2 C#記事 これはC#に関する記事です。
3 PHP&MySQL記事 これはPHPとMySQLに関する記事です。
4 雑記 これは雑記です。

tagテーブル

id entry_id tag (説明)
1 1 PHP entry_id=1(PHP記事)に対してPHPというタグを紐づけます。
2 2 C# entry_id=2(C#記事)に対してC#というタグを紐づけます。
3 3 PHP entry_id=3(PHP&MySQL記事)に対してPHPというタグを紐づけます。
4 3 MySQL entry_id=3(PHP&MySQL記事)に対してMySQLというタグを紐づけます。

このようにすると・・・

  • 複数のタグをつけたいときにも一つのカラムに詰め込む必要がなくなったため、使いやすくなった。
  • タグのマスタとなるテーブルが無いので、タグ名を変えたくなったときに、tagカラムの中身を一個一個書き換えなければならないという問題は残っている。

この手法は「Scuttle法」というそうですが、問題点は残りますね。

そこで、タグのマスタを追加し、計3つのテーブルになったのが次の、「TOXI法」というやつです。

テーブル設計③「3つのテーブルでやる」TOXI法(オススメ)

一番しっくりきた設計がコレです。

TOXI法では、タグを紐づけたいテーブルとタグマスタを用意し、間に、タグマップテーブルというテーブルを中間テーブルとして作ります。

entryテーブル

id title detail
1 PHP記事 これはPHPに関する記事です。
2 C#記事 これはC#に関する記事です。
3 PHP&MySQL記事 これはPHPとMySQLに関する記事です。
4 雑記 これは雑記です。

tagテーブル(タグのマスタ)

id name
1 Java
2 PHP
3 MySQL
4 JavaScript
5 C#

tag_mapテーブル(どの記事にどのタグが紐づいているかを繋ぐテーブル)

id entry_id tag_id (説明)
1 1 2 entry_id=1(PHP記事)に対してtag_id=2(PHP)のタグを紐づけ
2 2 5 entry_id=2(C#記事)に対してtag_id=5(C#)のタグを紐づけ
3 3 2 entry_id=3(PHP&MySQL記事)に対してtag_id=2(PHP)のタグを紐づけ
4 3 3 entry_id=3(PHP&MySQL記事)に対してtag_id=3(MySQL)のタグを紐づけ

このようにすると・・・

  • タグ名を変えたくなったらtagテーブルのnameカラムをいじればいい。

テーブル数は3つになったけど、ぱっと思いついたデメリットはすべて消えましたね。

最後に

次回から、このTOXI法を使ってごにょごにょと記事データを抽出してみたいと思います。

【MySQL】タグ付け機能の実装にオススメなテーブル設計(TOXI法)その2

それではまた。