全国対応のテーブル設計
全国対応のテーブル設計
さて、ではMysqlに、テーブル設計を始めていきましょうか。
ちょっとCakephpの技術的な部分からはそれてしまうかもしれませんが 全国に対応したようなサイトを作る際のテーブル設計の方法を学びたいと思います。
データベースはMysql、文字コードはutf-8generalciをご用意下さい。
なお、この記事の内容のSQLはこの記事の一番下から、Mysqlのdumpファイルがダウンロードできます。地区、都道府県などよろしければ使って下さい。
全国対応のデータベーステーブル設計
全国版に対応したようなサイトを作る際、テーブル設計は大きく３つに分けることが出来ます。
・関東や東海や関西などの地区を大きく分けるテーブル
・都道府県に分けるテーブル
・さらに都道府県から市や区に分けるテーブル
・都道府県に分けるテーブル
・さらに都道府県から市や区に分けるテーブル
さらにここから町や番地まで分けて行くならもう一つテーブルがあるとよいでしょう。
ではMysqlにテーブルを作って行きます。
全国対応のテーブルをMysql内に作成
Cakephpのテーブルの命名規則についてはご存じですか？命名規則の通りに作っていくと後が楽ですよ。逆に命名規則に反して作ると後のModel内でけっこう面倒なことをしなければならないのです。
地区を大きく分けるテーブルについてはそれほど悩まなくて大丈夫だと思います。
地区を大きく分けるテーブル
CREATE TABLE IF NOT EXISTS `districts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’地区名の設定’;
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’地区名の設定’;
都道府県
CREATE TABLE IF NOT EXISTS `prefectures` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) CHARACTER SET utf8 NOT NULL,
`district_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `district_id` (`district_id`)
) ENGINE=InnoDB DEFAULT CHARSET=armscii8 COMMENT=’都道府県’;
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) CHARACTER SET utf8 NOT NULL,
`district_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `district_id` (`district_id`)
) ENGINE=InnoDB DEFAULT CHARSET=armscii8 COMMENT=’都道府県’;
市や区
市や区などは、最初は最低限から始まり、後で増えていく可能性が高いので、件数が増えた時に自由に表示順位を変えられるように『sort』というフィールドも用意しました。(初期なのでデータはNULLですが。)
CREATE TABLE IF NOT EXISTS `areas` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
`prefecture_id` int(11) NOT NULL,
`sort` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `prefecture_id` (`prefecture_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’エリア’ ;
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
`prefecture_id` int(11) NOT NULL,
`sort` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `prefecture_id` (`prefecture_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’エリア’ ;
複数テーブル連携予定の命名規則
ちょっとおさらいなのですが、基本的な命名規則では
・テーブル名は英単語で複数系に
・連番のフィールド名は必ずidとする
・連番のフィールド名は必ずidとする
英単語で複数系ですが、英単語2つを組み合わせてテーブル名としたい場合はhoge_areasとしてアンダーバーデ区切り、2語目を必ず複数系にしてあげます。(テーブルが増えたときとか、よく使います)
さて、複数テーブルを連携する場合はもう一つ命名規則があります。
複数テーブルの命名規則
foreignKeyは『テーブル名+id』にする
では、都道府県を管理するprefecturesテーブルを見てみましょう。
foreignKeyのイメージ
おまけ：ストレージエンジンはMysql5.5からInnoDBがデフォ
ENGINE(ストレージエンジン)は私は何でもInnoDB(イーノデービー)を使います。5.5からだいぶ速度が向上し、もともとInnoDBはトランザクション機能がついているが、MyISAM(マイアイサム)に比べると検索スピードが劣るから、検索を考えた場合はMyISAMを選んだほうがよい、というのがありましたがMysql5.5からはInnoDBがデフォルトのストレージエンジンになり、InnoDBの速度もだいぶ向上したので 特別理由がなければMysqlのテーブルのストレージエンジンはInnoDBをオススメします。ただし、古いMysql5.0とかを利用している場合はMysqlを使ったほうがいいっちゃいいかもしれません。そこは臨機応変にお願いします。
Mysqlのdumpファイル
都道府県を一つ一つデータを入れていくのは大変なので、地区と、都道府県、小エリア3つのテーブルのdumpファイルを用意しました。
以下のファイルをダウンロードして、phpmyadminよりインポートを行えばITかあさんと同じ全国対応のテーブルの構築は完了します。
(小エリアのareasテーブルのデータは各自追加するなり減らすなりしてください。)
Mysqlのdumpファイルインポート方法
念のためにインポート方法を動画にて解説。さくらのレンタルサーバーで行いました。
さくらのレンタルサーバーはMysqlが5.5がデフォルト。
まとめ
複数テーブルの連携において、foreignKeyは『テーブル名+id』にする。
次回からCakephpを使ってがしがし掲示板を作っていきますよ！
Cakephpで会員制サイト サイトの機能設計
会員制掲示板の基本機能を決める
会員制掲示板の基本機能を決めていきましょう。
基本機能をしっかり決めることで効率のよいWEBアプリケーション開発に繋がります。
前回少し触れましたが、ITかあさんは昔けっこうあったバンドメンバー募集サイトを作りたいと思います。
サイトの基本機能
・バンドメンバー 加入希望 募集に大きくスレッドを分ける
・メール認証を行って会員になるシステム
・会員はプロフィールを持てる
・会員は新規スレッドを立てることが出来る
・非会員はスレッドに対してメッセージを送るのみ
・退会時にプロフィール、スレッドは削除される
・都道府県別カテゴリー
・募集パート 加入パート パート別カテゴリー
・プロフィール写真アップロード機能
・メール認証を行って会員になるシステム
・会員はプロフィールを持てる
・会員は新規スレッドを立てることが出来る
・非会員はスレッドに対してメッセージを送るのみ
・退会時にプロフィール、スレッドは削除される
・都道府県別カテゴリー
・募集パート 加入パート パート別カテゴリー
・プロフィール写真アップロード機能
マスター管理
・マスターログイン
・ユーザー管理
・サイト内お知らせ機能(ニュース)
・不正スレッド編集 削除
・ユーザー管理
・サイト内お知らせ機能(ニュース)
・不正スレッド編集 削除
ユーザー
・ログイン
・マイページ
・掲示板閲覧
・掲示板追加
・掲示板へメッセージ送信
・プロフィール機能
・マイページ
・掲示板閲覧
・掲示板追加
・掲示板へメッセージ送信
・プロフィール機能
非ユーザー
非ユーザーについては新規スレッドが立てられずに閲覧と返信のみに留めるようにしましょうか
・掲示板閲覧
・掲示板へメッセージ送信
・掲示板へメッセージ送信
機能を元にデータベース上に必要なテーブルを考える
ではここまでの機能やカテゴリーを踏まえて必要なテーブルを考えます。
日本全国に対応したエリアテーブルがちょっとしたミソになります。
エリアを管理するテーブル設計
私は全国希望のエリアでカテゴリー分けをするようなサイトではエリアの管理だけで3つのテーブルを使うことが多いです。
・東北北海道、関東、関西などざっくりとしたブロック分け(districts)
・関東なら、さらに東京、埼玉など都道府県を分けるテーブル(prefectures)
・都道府県からさらに細かい小エリアに分けるテーブル(areas)
その他必要なテーブル
後のテーブル管理はそれほど悩むこともありません。
エリア情報を管理するテーブルと合わせると10テーブルほどで完了しますね。
・ユーザー管理テーブル(users)
・マスターログインの管理テーブル(masters)
・楽器パート管理テーブル(parts)
・カテゴリー管理テーブル(categories)
・掲示板テーブル(threads)
・掲示板に対するメッセージ返信テーブル(messages)
・サイト内お知らせテーブル(informations)
・東北北海道、関東、関西などざっくりとしたブロック分け(districts)
・関東なら、さらに東京、埼玉など都道府県を分けるテーブル(prefectures)
・都道府県からさらに細かい小エリアに分けるテーブル(areas)
・関東なら、さらに東京、埼玉など都道府県を分けるテーブル(prefectures)
・都道府県からさらに細かい小エリアに分けるテーブル(areas)
その他必要なテーブル
後のテーブル管理はそれほど悩むこともありません。
エリア情報を管理するテーブルと合わせると10テーブルほどで完了しますね。
・ユーザー管理テーブル(users)
・マスターログインの管理テーブル(masters)
・楽器パート管理テーブル(parts)
・カテゴリー管理テーブル(categories)
・掲示板テーブル(threads)
・掲示板に対するメッセージ返信テーブル(messages)
・サイト内お知らせテーブル(informations)
エリア情報を管理するテーブルと合わせると10テーブルほどで完了しますね。
・ユーザー管理テーブル(users)
・マスターログインの管理テーブル(masters)
・楽器パート管理テーブル(parts)
・カテゴリー管理テーブル(categories)
・掲示板テーブル(threads)
・掲示板に対するメッセージ返信テーブル(messages)
・サイト内お知らせテーブル(informations)
・マスターログインの管理テーブル(masters)
・楽器パート管理テーブル(parts)
・カテゴリー管理テーブル(categories)
・掲示板テーブル(threads)
・掲示板に対するメッセージ返信テーブル(messages)
・サイト内お知らせテーブル(informations)
次回は全国対応のエリア分けのテーブル設計についての記事を書きたいと思います
Cakephp入門第2弾始動！！Cakephpで会員制掲示板を作ってみよう！
Cakephpで会員制サイトの基本的を学ぼう
さてCakephp入門のアクセスでITかあさんのブログは成り立っているようなものなんですがCakephp入門終了からしばらく経って、読み返してみると、自分自身も入門している状態で書き始めたために文章もひどいもんだなあ、と。
ただ、ITかあさんは一度したクソは見ない主義(表現汚い上にダメ人間)なので、改めて書き直すくらいなら、もっと質のよいCakephp入門記事を書くべきだと思い、第2弾をスタートさせたいと思います。
今回の目標
・複数テーブルの連携
・会員制サイト構築のセッション管理
・携帯サイトの基本
・会員制サイト構築のセッション管理
・携帯サイトの基本
主にこの3つをやっていきたいと思います。私はCakephpに出会ってから本格的なプログラマー生活が始まったのですが 設計のコツなども踏まえてお伝え出来ればと思います。
全１０回くらいに渡って基本的なCakephpの開発フローをお伝えし、終了未定でプラグインの入れ込みなどお伝え出来ればと考えています。
ITかあさんは今回のネタでこんなの作ります
ITかあさんはかつて『かあさん』になるまでは音楽の専門学校通ってバンド組んだり、ライブハウスで歌ったりと音楽漬けな日々を送っていたのですが、そんなかあさんですので、バンドメンバー募集掲示板みたいなのをずっと作りたいと思っていて完全放置プレイだったので今回のCakephp入門第2弾始動をきっかけにそんなものを作ってみたいなと思っています。
やや大きな規模のサイトになりますので、そういった大規模サイト構築のフローをITかあさんと一緒に学んで頂ければ幸いです。