タグ機能を実現するための便利なデータベース設計を3つ紹介

Empire Avenue Tag Cloud
Empire Avenue Tag Cloud / Rubber Dragon

新しいプロジェクトでタグ機能を実装することになり情報を収集していたところ、Philipp Kellerさんの「Tags: Database schemas」という面白い記事を発見。

この記事の中で単純な設計からやや複雑な設計まで3種類の事例を挙げて、それぞれのメリットやデメリットを紹介してあります。 英語が苦手な人向けに日本語で紹介しましょう。

1.MySQLicious法

この名前はソーシャルブックマークサービスの草分け的存在である「del.icio.us」の設計方法をMySQLで実現することから名付けられたようです。

deliciousテーブル

このテーブル設計方法は、1つのフィールド内(tags)にタグ情報を改行で詰め込んでいく方法です。

id url tags
1 http://www.colo-ri.jp/develop/2008/03/css.html CSS
フォントサイズ
2 http://www.colo-ri.jp/develop/2010/03/html5html5api.html HTML5
書籍
API
3 http://www.colo-ri.jp/develop/2008/04/seleniumjavascript.html Selenium
JavaScript

AND検索

「CSS+HTML+JavaScript」で検索する場合は以下のようにします。

SELECT *
FROM `delicious`
WHERE tags LIKE "%CSS%"
AND tags LIKE "%HTML%"
AND tags LIKE "%JavaScript%"

OR検索

「CSS|HTML|JavaScript」で検索する場合は以下のようにします。

SELECT *
FROM `delicious`
WHERE tags LIKE "%CSS%"
OR tags LIKE "%HTML%"
OR tags LIKE "%JavaScript%"

引き算検索

「CSS+HTML-JavaScript」で検索する場合は以下のようにします。

SELECT *
FROM `delicious`
WHERE tags LIKE "%CSS%"
AND tags LIKE "%HTML%"
AND tags NOT LIKE "%JavaScript%"

MySQLicious法のまとめ

メリット

  • テーブルが1つで済む。
  • 検索クエリーが単純。

デメリット

  • 256バイトのVARCHAR型のようなものにしてしまうと、1つのフィールドに付けるタグの数が制限されてしまう。 それを避けるためにTEXT型のようなものにすると、(おそらく)速度が落ちてしまう。
  • 後方一致で「%HTML」のようなクエリにすると「XHTML」なども引っかかってしまうので「%HTML%」にして、タグの保存時には行頭に半角スペースを挿入する。
  • tagsフィールドに全文検索を付けないとクエリーが遅くなる。

2.Scuttle法

Scuttle法では、2つのテーブルを使います。 「scCategories」テーブルがいわゆるタグ用テーブルで、ブックマーク用テーブルである「scBookmarks」の外部キー(bId)を持っています。

scuttle_database_schema.png

AND検索

「CSS+HTML+JavaScript」で検索する場合は以下のようにします。

SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('CSS', 'HTML', 'JavaScript'))
GROUP BY b.bId
HAVING COUNT( b.bId )=3

「c.category IN (‘CSS’, ‘HTML’, ‘JavaScript’)」を使って全てをOR検索し、「HAVING COUNT( b.bId )=3」によって3つ全てを含むものだけに制限します。

OR検索

AND検索からHAVING句を除くとそのままOR検索になります。

SELECT b.*
FROM scBookmarks b, scCategories c
WHERE c.bId = b.bId
AND (c.category IN ('CSS', 'HTML', 'JavaScript'))
GROUP BY b.bId

引き算検索

「CSS+HTML-JavaScript」で検索する場合は以下のようにします。

SELECT b. *
FROM scBookmarks b, scCategories c
WHERE b.bId = c.bId
AND (c.category IN ('CSS', 'HTML'))
AND b.bId NOT
IN (SELECT b.bId FROM scBookmarks b, scCategories c WHERE b.bId = c.bId AND c.category = 'JavaScript')
GROUP BY b.bId
HAVING COUNT( b.bId ) =2

ここのHAVING句を除くとそのまま「CSS|HTML-JavaScript」の検索クエリになります。

Scuttle法まとめ

Scuttle法の特徴は、最初のMySQLlisious法よりもデータベースがより正規化されている点。 また、タグ付けできる数にも制限がなくなりました。

3.Toxi法

Toxiは3つのテーブルから成ります。 中間の「tagmap」テーブルでbookmarkテーブルとtagテーブルが関連付けられ、bookmarkデータとtagデータはn-to-mの関係になります。

toxi_database_schema.png

n-to-mとはこの場合、1つのtagに複数のbookmarkが関連付けられる場合があり、また1つのbookmarkに複数のtagが関連付けられるという関係になるということです。 実際によくある事例ですよね。

WordPressにもこの方式が用いられています。

検索クエリはScuttle法とよく似ています。

AND検索

「CSS+HTML+JavaScript」で検索する場合は以下のようにします。

SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('CSS', 'HTML', 'JavaScript'))
AND b.id = bt.bookmark_id
GROUP BY b.id
HAVING COUNT( b.id )=3

OR検索

「CSS|HTML|JavaScript」で検索する場合は以下のようにします。

SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('CSS', 'HTML', 'JavaScript'))
AND b.id = bt.bookmark_id
GROUP BY b.id

引き算検索

「CSS+HTML-JavaScript」で検索する場合は以下のようにします。

SELECT b. *
FROM bookmark b, tagmap bt, tag t
WHERE b.id = bt.bookmark_id
AND bt.tag_id = t.tag_id
AND (t.name IN ('CSS', 'HTML'))
AND b.id NOT IN (SELECT b.id FROM bookmark b, tagmap bt, tag t WHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_id AND t.name = 'JavaScript')
GROUP BY b.id
HAVING COUNT( b.id ) =2

ここのHAVING句を除くとそのまま「CSS|HTML-JavaScript」の検索クエリになります。

Toxi法まとめ

メリット

  • それぞれのタグに他の情報(階層構造や詳細説明など)を付加することが可能。
  • これが最も正規化された方法です。

デメリット

  • bookmarkデータを編集したり削除すると、tagが親なしになってしまう。(ちゃんと管理する必要がある)

もし、以下の検索条件のようなものを実現するには、クエリーはもっと入り組んだものになるでしょう。

(bookmarks OR bookmark) AND (webservice or WS) AND NOT (semweb or semanticweb)

このページをシェアする

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

2012-02-25