写真共有サイトとかソーシャルブックマークとかブログサービスとかでは、１つの写真（ブックマーク、記事）ごとにタグを付けられるようになっていますよね。
「人気のタグ」や「注目のタグ」みたいな感じで検索もできるようになっていますよね。
このタグというのは、データベースではどのような形でデータを持っているのでしょうか？
例えば写真共有サイトを作るとして、１つの写真ごとに以下の内容を登録してもらうことにします。
・写真タイトル
・ファイル名
・撮影者（利用ユーザー）
・タグ（複数タグの登録可）
この場合、どのようにテーブルを作ればいいのでしょう？
（１つの写真や記事に対して複数のタグが入れることができて、それらのタグをキーにして検索やタグの集計をどうやっているのかを知りたいです。）
No.3
421
75
40pt
[参考URL]
ここでnaoya氏も言っているように
正規化した tag テーブルにひとつのタグでひとつのレコードという形で保存
が良いと思います。
以下、私が考えたサンプルです。
(id:k6nch6nさんの考え方とほぼ同じだと思います。)
| id (PK) | password | name |
|---|
| id (PK) | user_id (FK references users.id) | title (INDEX) | file_name |
|---|
| id (PK) | name (INDEX) | photo_id | registered_date (INDEX) |
|---|
以下のようなイメージで検索できると思います。
29
28
SELECT p.title, p.file_name, u.name
FROM users u, photos p, tags t
WHERE u.id = p.user_id AND
p.id = t.photo_id AND
t.name = "タグ名"
SELECT p.title, p.file_name, u.name
FROM users u, photos p, tags t
WHERE u.id = p.user_id AND
p.id = t.photo_id AND
p.id = "写真のID"
;
SELECT t.name
FROM photos p, tags t
WHERE p.id = t.photo_id AND
p.id = "写真のID"
;
SELECT t.name
FROM tags t
WHERE t.registered_date > '2006-06-30' AND
t.registered_date < '2006-07-08'
GROUP BY t.name
ORDER BY count(*) DESC, t.name
LIMIT 5
;
人気のタグの場合、求められる情報の鮮度によりますが、毎回検索されるのを避けるために、人気タグ用のテーブルを作っておいて毎日1回処理する、などでも良いと思います。
| id (PK) | tag_id (FK References tags.id) | tag_count | summary_date (INDEX) |
|---|
例えば、毎日0時に人気のタグを抽出し、タグ1つごとにpopular_tagsテーブルにセットし、表示する際にはsummary_dateが前日のものをSELECTすれば良いはずです。
このようにしておけば、過去の人気タグを時系列をさかのぼって調べることもできますし、便利だと思います。
また、今回は撮影者を利用ユーザとして想定なさっているようでしたが、一般の登録ユーザが他人の写真にもタグを付けられるようにする場合もほぼ同様の考え方でいけると思います。
(tagsテーブルに登録ユーザの列を追加し、users.idを外部参照させます。)
細かい部分ではもっと上手いやり方も色々とあると思いますが、参考いなれば幸いです。
No.1
17111
30pt
ユーザー
・ID（主キー）
・パスワード
・本名
・・・
写真
・ID（主キー）...x
・ユーザー_ID
・タイトル
・ファイル名
・・・
タグ
・写真_ID...y
・タグ
のように写真の情報を格納する表からタグの情報を格納する表を分けるのが一般的ではないかと思います。
xとyの間に参照整合性制約（外部キー）を作成します。
タグ表にgroup byやcountを使用すれば、お望みの結果が得られると思います。
ありがとうございます。
タグ用にテーブルを分ける感じになるのですね。なんとなく分かってきました。
No.2
23034
10pt
3種類の方法を考えてみました。
３つ目の方法が一番スマートなんですが、理解するのが難しいかも…。
--------
●複数フィールドによる方法 （１つのテーブルにタグの種類の数だけフィールドをつくる方法）
[写真ID],[ファイル名],[撮影者のユーザID],[人気のタグ],[注目のタグ],…
というフィールドを持つテーブルをつくる。
●複数テーブルによる方法 （タグの種類の数だけテーブルを作る方法）
[写真ID],[ファイル名],[撮影者のユーザID]
[写真ID],[人気のタグ]
[写真ID],[注目のタグ]
というフィールドを持つテーブルを作る
※一つの行が一つのテーブルを表しています。
※後々、タグの種類が増えたら、 その分、テーブルを増やせばよい。
●単一フィールドによる方法 （タグのフィールドを１つで済ます方法 ※テーブルは１つ）
[写真ID],[ファイル名],[撮影者のユーザID],[タグ]
というフィールドを持つテーブルを作る。
タグには以下の規則の数値を書き込む。
人気のタグは、２進数の０１ → １０進数で１
注目のタグは、２進数の１０ → １０進数で２
と考える。
よって、
人気のタグのみ→１
注目のタグのみ→２
人気のタグかつ注目のタグ→３（１＋２）
を[タグ]フィールドの値として書き込む
※さらに別のタグが増えたら、２進数で１００→１０進数で４
さらに次は、２進数で１０００→１０進数で８
の要領で、数値を決める。
※このように考えることで、後々、タグが増えてもテーブル構成を変える必要がない。
--------
３つ目の方法（単一フィールドによる方法）の検索について説明します。
ビット演算子のANDを使います。
例
人気のタグが含まれるデータを検索したいなら、
[タグ]フィールドの値 AND １ ＝ １
が成立するデータを抽出する。
例
注目のタグが含まれるデータを検索したいなら、
[タグ]フィールドの値 AND ２ ＝ ２
が成立するデータを抽出する。
例
人気のタグと注目のタグが含まれるデータを検索したいなら、
[タグ]フィールドの値 AND ３ ＝ ３
が成立するデータを抽出する。
なお、
ANDの実際の書き方は、
データベースや、プログラミング言語により異なります。
PostgreSQLでは、&
Oracleでは、BITAND()関数
使いたい言語のビット演算子を調べてください。
ありがとうございます。
私の説明の仕方が悪かったですね。
タグ＝キーワード（またはカテゴリー）と思ってください。
例えば、
写真タイトル：横浜旅行
写真名：yokohama-01.jpg
ユーザー：ktoshi
タグ：横浜,観覧車,2006-07,旅行
のような感じに登録します。
No.3
42175
ここでベストアンサー
40pt
[参考URL]
ここでnaoya氏も言っているように
正規化した tag テーブルにひとつのタグでひとつのレコードという形で保存
が良いと思います。
以下、私が考えたサンプルです。
(id:k6nch6nさんの考え方とほぼ同じだと思います。)
| id (PK) | password | name |
|---|
| id (PK) | user_id (FK references users.id) | title (INDEX) | file_name |
|---|
| id (PK) | name (INDEX) | photo_id | registered_date (INDEX) |
|---|
以下のようなイメージで検索できると思います。
SELECT p.title, p.file_name, u.name
FROM users u, photos p, tags t
WHERE u.id = p.user_id AND
p.id = t.photo_id AND
t.name = "タグ名"
SELECT p.title, p.file_name, u.name
FROM users u, photos p, tags t
WHERE u.id = p.user_id AND
p.id = t.photo_id AND
p.id = "写真のID"
;
SELECT t.name
FROM photos p, tags t
WHERE p.id = t.photo_id AND
p.id = "写真のID"
;
SELECT t.name
FROM tags t
WHERE t.registered_date > '2006-06-30' AND
t.registered_date < '2006-07-08'
GROUP BY t.name
ORDER BY count(*) DESC, t.name
LIMIT 5
;
人気のタグの場合、求められる情報の鮮度によりますが、毎回検索されるのを避けるために、人気タグ用のテーブルを作っておいて毎日1回処理する、などでも良いと思います。
| id (PK) | tag_id (FK References tags.id) | tag_count | summary_date (INDEX) |
|---|
例えば、毎日0時に人気のタグを抽出し、タグ1つごとにpopular_tagsテーブルにセットし、表示する際にはsummary_dateが前日のものをSELECTすれば良いはずです。
このようにしておけば、過去の人気タグを時系列をさかのぼって調べることもできますし、便利だと思います。
また、今回は撮影者を利用ユーザとして想定なさっているようでしたが、一般の登録ユーザが他人の写真にもタグを付けられるようにする場合もほぼ同様の考え方でいけると思います。
(tagsテーブルに登録ユーザの列を追加し、users.idを外部参照させます。)
細かい部分ではもっと上手いやり方も色々とあると思いますが、参考いなれば幸いです。
ありがとうございます。
とても分かりやすい回答なので助かります。
はてブはタグ用にテーブルを分けて作ってるんですね。
nandedarou
2006/07/09 12:28:17
3
2
1
ありがとうございます。
とても分かりやすい回答なので助かります。
はてブはタグ用にテーブルを分けて作ってるんですね。