PHP5+Mysql5.5にて検索順位をチェックする会員制サイトを作成しようと思います。

DB設計をお願い致します。

内容
・会員が複数のURLを登録でき、そのURLごとに複数のキーワード登録が可能
・会員がログインし更新ボタンを押すと、その日の順位を記録
・URL+キーワードの組み合わせ毎に、月間、週間、等でグラフ化予定
・一ヶ月ログインしていない会員データは自動削除

考慮頂きたいこと
・カラム数が多くなると検索に時間がかかる
・PhpMyAdminにて管理する際にユーザIDやURL等目視でもわかりやすいように

その他、テーブルを結合する場合はそれも教えてください。


回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2012/10/27 21:11:33
  • 終了:2012/11/03 15:00:11

ベストアンサー

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492012/10/28 11:18:35

ポイント250pt

短縮系は好みがありますし、
説明しやすくもあるのでとりあえず日本語で命名しました。

(1)マスター

■会員マスター
・ユーザーID(主キー)
・パスワード(暗号化して登録しておくことが望ましい)
・ユーザーネーム
・メールアドレス
・削除フラグ
その他に登録しておきたいユーザーの情報があれば、このテーブルに入れておく。

削除フラグは一ヶ月以上ログインしていない会員を示し、
いきなり削除してしまうのではなく、退避させるなどの手段を講じやすくするために用意。

(2)データ

■URLリスト
・URLリスト通し番号(主キー)
・ユーザーID(外部キー:会員マスターとのJOIN時に利用)
・URL
入力フォームなどで既存のURLのリストを作成したりすることを考慮して、
ユーザーIDで別途インデックスを作成しておく。

■キーワードリスト
・キーワードリスト通し番号(主キー)
・キーワード
・URLリスト通し番号(URLリストからの転載:URLリストとのJOIN時に利用)
・URL(URLリストから転載:冗長だけど読み出し時の速度アップに寄与)

入力フォームなどで既存のリストを作成したりすることを考慮して、
キーワードインデックス、URLリスト通し番号インデックスをそれぞれ作成しておく。

キーワードリストにURLを含むことは冗長ですが、
新規でキーワードを登録する時以外は、
URLリストへのアクセスを必要としなくなり負荷軽減となります。
キーワードリストにユーザーIDを含まないのは、会員マスターを都度再検索せずとも、
ログイン中はphp側でユーザーネームを保持し続けるようにすれば済む話なので…。

(3)ログ

■ログイン情報
・ユーザーID(主キー)
・最終ログイン日時
cronなどで毎日監視して一ヶ月以上ログインしていなければ…削除フラグを立てる…。

■検索順位
・日付(主キー)
・キーワードリスト通し番号(外部キー)
・順位

キーワードリスト通し番号がURL+キーワードの組み合わせになっているので集計が楽。

id:windofjuly

なお、広告関係の部分を別のログテーブルに取るかログイン情報テーブルに入れ込むかは判断できず、割愛しています。

2012/10/28 11:21:05

その他の回答(2件)

id:oil999 No.1

oil999回答回数1728ベストアンサー獲得回数3202012/10/27 22:14:19

ポイント250pt

会員情報 tbl_users

カラム名(例)内容キー
uid会員IDkey
userログイン名
pswパスワード
date0登録日

登録URL tbl_urls

カラム名(例)内容キー
URLURLkey
uid会員IDkey
date0登録日

キーワード tbl_keywords

カラム名(例)内容キー
URLURLkey
uid会員IDkey
keywordキーワード
counterカウンター
date0登録日

tbl_urlsとtbl_keywordsはuidとURLで連結します。

id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492012/10/28 11:18:35ここでベストアンサー

ポイント250pt

短縮系は好みがありますし、
説明しやすくもあるのでとりあえず日本語で命名しました。

(1)マスター

■会員マスター
・ユーザーID(主キー)
・パスワード(暗号化して登録しておくことが望ましい)
・ユーザーネーム
・メールアドレス
・削除フラグ
その他に登録しておきたいユーザーの情報があれば、このテーブルに入れておく。

削除フラグは一ヶ月以上ログインしていない会員を示し、
いきなり削除してしまうのではなく、退避させるなどの手段を講じやすくするために用意。

(2)データ

■URLリスト
・URLリスト通し番号(主キー)
・ユーザーID(外部キー:会員マスターとのJOIN時に利用)
・URL
入力フォームなどで既存のURLのリストを作成したりすることを考慮して、
ユーザーIDで別途インデックスを作成しておく。

■キーワードリスト
・キーワードリスト通し番号(主キー)
・キーワード
・URLリスト通し番号(URLリストからの転載:URLリストとのJOIN時に利用)
・URL(URLリストから転載:冗長だけど読み出し時の速度アップに寄与)

入力フォームなどで既存のリストを作成したりすることを考慮して、
キーワードインデックス、URLリスト通し番号インデックスをそれぞれ作成しておく。

キーワードリストにURLを含むことは冗長ですが、
新規でキーワードを登録する時以外は、
URLリストへのアクセスを必要としなくなり負荷軽減となります。
キーワードリストにユーザーIDを含まないのは、会員マスターを都度再検索せずとも、
ログイン中はphp側でユーザーネームを保持し続けるようにすれば済む話なので…。

(3)ログ

■ログイン情報
・ユーザーID(主キー)
・最終ログイン日時
cronなどで毎日監視して一ヶ月以上ログインしていなければ…削除フラグを立てる…。

■検索順位
・日付(主キー)
・キーワードリスト通し番号(外部キー)
・順位

キーワードリスト通し番号がURL+キーワードの組み合わせになっているので集計が楽。

id:windofjuly

なお、広告関係の部分を別のログテーブルに取るかログイン情報テーブルに入れ込むかは判断できず、割愛しています。

2012/10/28 11:21:05
id:GRY No.3

Bright回答回数58ベストアンサー獲得回数22012/11/03 13:52:23スマートフォンから投稿

URLにタイトルを付けたり出来る。
ユーザーネームが登録出来るなどどうでしょう。

  • id:windofjuly
    うぃんど 2012/10/27 22:26:54
    正規化されたテーブル群を使うほうが効率よいもの
    >会員が複数のURLを登録でき、そのURLごとに複数のキーワード登録が可能

    検索時間、目視のいずれにおいても非正規化のほうが効率よいもの
    >一ヶ月ログインしていない会員データは自動削除
    >URL+キーワードの組み合わせ毎に、月間、週間、等でグラフ化予定

    意味不明なもの
    >会員がログインし更新ボタンを押すと、その日の順位を記録
    毎日定時に自動的に行うような処理だと思われるが、
    なぜ、ログインして更新ボタンをクリックしなければならないのか?
    その理由によってはテーブル構成が変わってくる。

    URLの長さやユーザーID、ユーザーニックネームの長さなど、
    それぞれの情報のデータサイズ、MySQLの容量制限などの情報も欲しいところですね。
  • id:iori753
    お忙しい中ご回答ありがとうございます。

    ログインしてクリックが必要な理由は、検索エンジンからのスクレイピング回数をログインユーザのみとすることで削減するためと、広告収入ベースにするためログインによるアクセスを求めるためです。

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

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

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

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