タグ機能を実現するための便利なデータベース設計を3つ紹介
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)を持っています。
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の関係になります。
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