データベースの質問


写真共有サイトとかソーシャルブックマークとかブログサービスとかでは、1つの写真(ブックマーク、記事)ごとにタグを付けられるようになっていますよね。
「人気のタグ」や「注目のタグ」みたいな感じで検索もできるようになっていますよね。

このタグというのは、データベースではどのような形でデータを持っているのでしょうか?
例えば写真共有サイトを作るとして、1つの写真ごとに以下の内容を登録してもらうことにします。

・写真タイトル
・ファイル名
・撮影者(利用ユーザー)
・タグ(複数タグの登録可)

この場合、どのようにテーブルを作ればいいのでしょう?
(1つの写真や記事に対して複数のタグが入れることができて、それらのタグをキーにして検索やタグの集計をどうやっているのかを知りたいです。)

回答の条件
  • 1人2回まで
  • 登録:2006/07/07 22:49:55
  • 終了:2006/07/08 23:30:26

ベストアンサー

id:bonlife No.3

回答回数421ベストアンサー獲得回数752006/07/08 14:28:05

ポイント40pt

[参考URL]

ここでnaoya氏も言っているように

正規化した tag テーブルにひとつのタグでひとつのレコードという形で保存

が良いと思います。

以下、私が考えたサンプルです。

(id:k6nch6nさんの考え方とほぼ同じだと思います。)

  • users
id (PK) password name
  • photos
id (PK) user_id (FK references users.id) title (INDEX) file_name
  • tags
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"
;
  • 人気のタグの抽出 (最近1週間の登録から5つ抽出する例)
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回処理する、などでも良いと思います。

  • popular_tags
id (PK) tag_id (FK References tags.id) tag_count summary_date (INDEX)

例えば、毎日0時に人気のタグを抽出し、タグ1つごとにpopular_tagsテーブルにセットし、表示する際にはsummary_dateが前日のものをSELECTすれば良いはずです。

このようにしておけば、過去の人気タグを時系列をさかのぼって調べることもできますし、便利だと思います。

また、今回は撮影者を利用ユーザとして想定なさっているようでしたが、一般の登録ユーザが他人の写真にもタグを付けられるようにする場合もほぼ同様の考え方でいけると思います。

(tagsテーブルに登録ユーザの列を追加し、users.idを外部参照させます。)

細かい部分ではもっと上手いやり方も色々とあると思いますが、参考いなれば幸いです。

id:ktoshi

ありがとうございます。

とても分かりやすい回答なので助かります。

はてブはタグ用にテーブルを分けて作ってるんですね。

2006/07/08 23:26:22

その他の回答(2件)

id:k6nch6n No.1

k6nch6n回答回数171ベストアンサー獲得回数112006/07/08 01:23:31

ポイント30pt

ユーザー

・ID(主キー)

・パスワード

・本名

・・・

写真

・ID(主キー)...x

・ユーザー_ID

・タイトル

・ファイル名

・・・

タグ

・写真_ID...y

・タグ

のように写真の情報を格納する表からタグの情報を格納する表を分けるのが一般的ではないかと思います。

xとyの間に参照整合性制約(外部キー)を作成します。

タグ表にgroup byやcountを使用すれば、お望みの結果が得られると思います。

id:ktoshi

ありがとうございます。

タグ用にテーブルを分ける感じになるのですね。なんとなく分かってきました。

2006/07/08 11:46:26
id:nandedarou No.2

nandedarou回答回数230ベストアンサー獲得回数342006/07/08 01:53:48

ポイント10pt

3種類の方法を考えてみました。

3つ目の方法が一番スマートなんですが、理解するのが難しいかも…。

--------

複数フィールドによる方法 (1つのテーブルにタグの種類の数だけフィールドをつくる方法)

 

[写真ID],[ファイル名],[撮影者のユーザID],[人気のタグ],[注目のタグ],…

というフィールドを持つテーブルをつくる。

 

複数テーブルによる方法 (タグの種類の数だけテーブルを作る方法)

 

[写真ID],[ファイル名],[撮影者のユーザID]

[写真ID],[人気のタグ]

[写真ID],[注目のタグ]

というフィールドを持つテーブルを作る

※一つの行が一つのテーブルを表しています。

※後々、タグの種類が増えたら、 その分、テーブルを増やせばよい。

  

単一フィールドによる方法 (タグのフィールドを1つで済ます方法 ※テーブルは1つ)

 

[写真ID],[ファイル名],[撮影者のユーザID],[タグ]

というフィールドを持つテーブルを作る。

タグには以下の規則の数値を書き込む。

 

人気のタグは、2進数の01 → 10進数で1

注目のタグは、2進数の10 → 10進数で2

と考える。

 

よって、

人気のタグのみ→1

注目のタグのみ→2

人気のタグかつ注目のタグ→3(1+2)

を[タグ]フィールドの値として書き込む

 

※さらに別のタグが増えたら、2進数で100→10進数で4

 さらに次は、2進数で1000→10進数で8

 の要領で、数値を決める。

 

※このように考えることで、後々、タグが増えてもテーブル構成を変える必要がない。

--------

 

3つ目の方法(単一フィールドによる方法)の検索について説明します。

ビット演算子のANDを使います。

 

人気のタグが含まれるデータを検索したいなら、

[タグ]フィールドの値 AND 1 = 1

が成立するデータを抽出する。

 

注目のタグが含まれるデータを検索したいなら、

[タグ]フィールドの値 AND 2 = 2

が成立するデータを抽出する。

 

人気のタグと注目のタグが含まれるデータを検索したいなら、

[タグ]フィールドの値 AND 3 = 3

が成立するデータを抽出する。

 

なお、

ANDの実際の書き方は、

データベースや、プログラミング言語により異なります。

PostgreSQLでは、&

Oracleでは、BITAND()関数

使いたい言語のビット演算子を調べてください。

id:ktoshi

ありがとうございます。

私の説明の仕方が悪かったですね。

タグ=キーワード(またはカテゴリー)と思ってください。

例えば、

写真タイトル:横浜旅行

写真名:yokohama-01.jpg

ユーザー:ktoshi

タグ:横浜,観覧車,2006-07,旅行

のような感じに登録します。

2006/07/08 11:54:35
id:bonlife No.3

回答回数421ベストアンサー獲得回数752006/07/08 14:28:05ここでベストアンサー

ポイント40pt

[参考URL]

ここでnaoya氏も言っているように

正規化した tag テーブルにひとつのタグでひとつのレコードという形で保存

が良いと思います。

以下、私が考えたサンプルです。

(id:k6nch6nさんの考え方とほぼ同じだと思います。)

  • users
id (PK) password name
  • photos
id (PK) user_id (FK references users.id) title (INDEX) file_name
  • tags
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"
;
  • 人気のタグの抽出 (最近1週間の登録から5つ抽出する例)
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回処理する、などでも良いと思います。

  • popular_tags
id (PK) tag_id (FK References tags.id) tag_count summary_date (INDEX)

例えば、毎日0時に人気のタグを抽出し、タグ1つごとにpopular_tagsテーブルにセットし、表示する際にはsummary_dateが前日のものをSELECTすれば良いはずです。

このようにしておけば、過去の人気タグを時系列をさかのぼって調べることもできますし、便利だと思います。

また、今回は撮影者を利用ユーザとして想定なさっているようでしたが、一般の登録ユーザが他人の写真にもタグを付けられるようにする場合もほぼ同様の考え方でいけると思います。

(tagsテーブルに登録ユーザの列を追加し、users.idを外部参照させます。)

細かい部分ではもっと上手いやり方も色々とあると思いますが、参考いなれば幸いです。

id:ktoshi

ありがとうございます。

とても分かりやすい回答なので助かります。

はてブはタグ用にテーブルを分けて作ってるんですね。

2006/07/08 23:26:22
  • id:nandedarou
    タグを勘違いしておりました。
    タグは自由に投稿者が設定できるのですね。
    私の提示した方法では、
    あらかじめ決められた種類のタグしか付けることができません。
    実際の使用例を見てから考えるべきでした。
    申し訳ありません。m(__)m

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません