はてなブクマやblog等で最近よく見かける「タグ」システムについて。

例えばこういうテーブルがあるとします．

テーブル構造

CREATE TABLE items (
    id integer NOT NULL PRIMARY KEY,
    name varchar(200) NOT NULL,
    price integer NOT NULL
);

CREATE TABLE users (
    id integer NOT NULL PRIMARY KEY,
    name varchar(200) NOT NULL,
    email varchar(200) NOT NULL,
    login_name varchar(100) NOT NULL,
    login_password varchar(40) NOT NULL
);

CREATE TABLE item_tags (
    id integer NOT NULL PRIMARY KEY,
    user_id integer NOT NULL,
    item_id integer NOT NULL,
    name varchar(200)
);

• 実際にはプライマリキーのidは自動採番(MySQLならautoincrement，PostgreSQLならSERIAL)させるほうが好ましい
• パフォーマンスを維持するためには適切なインデックスを付加する必要があります

レコードの追加

商品の追加は下記のようにやります．

INSERT INTO items (id, name, price) VALUES( 1, '商品1', 100);
INSERT INTO items (id, name, price) VALUES( 2, '商品2', 200);

ユーザーの追加は下記のようにやります．

INSERT INTO users (id, name, email, login_name, login_password) VALUES( 1, 'ユーザー1', 'user1', 'user1@example.com', '暗号化，ハッシュ化したパスワード');
INSERT INTO users (id, name, email, login_name, login_password) VALUES( 2, 'ユーザー2', 'user2', 'user2@example.com', '暗号化，ハッシュ化したパスワード');

この上で，「user1」さんが商品番号1にタグ「書籍」「参考書」を付加するには下記のようにします．

INSERT INTO item_tags (id, user_id, item_id, name) VALUES( 1, 1, 1, '書籍');
INSERT INTO item_tags (id, user_id, item_id, name) VALUES( 2, 1, 1, '参考書');
INSERT INTO item_tags (id, user_id, item_id, name) VALUES( 3, 1, 2, 'MUSIC');
INSERT INTO item_tags (id, user_id, item_id, name) VALUES( 4, 1, 2, 'CD');

この上で，「user2」さんが商品番号1にタグ「本」「後で読む」を付加するには下記のようにします．

INSERT INTO item_tags (id, user_id, item_id, name) VALUES( 5, 2, 1, '本');
INSERT INTO item_tags (id, user_id, item_id, name) VALUES( 6, 2, 1, '後で読む');
INSERT INTO item_tags (id, user_id, item_id, name) VALUES( 7, 2, 1, '参考書');
INSERT INTO item_tags (id, user_id, item_id, name) VALUES( 8, 2, 2, 'CD');
INSERT INTO item_tags (id, user_id, item_id, name) VALUES( 9, 2, 2, '音楽');

こうすることにより，商品に付加されたタグの数などをSELECG時にSQLだけで算出することができるようになります．

テストSQL

ユーザー1が商品1に付加している商品にタグ

SELECT * FROM item_tags WHERE user_id = 1 AND item_id = 1;

商品にタグを貼っているユーザー数

SELECT COUNT(*) FROM (SELECT user_id FROM item_tags WHERE item_id = 1 GROUP BY user_id) AS rows;

商品に貼られている全タグ

SELECT * FROM item_tags WHERE item_id = 1 GROUP BY name;

ユーザー1が商品に付加している全タグ

SELECT name FROM item_tags WHERE user_id = 1 GROUP BY name;

軽くテストしただけなのでSQLミスなどあるかもしれません．