全国対応のテーブル設計

2012年03月28日| Cakephpで会員制サイトを作ろう！

全国対応のテーブル設計

さて、では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=’地区名の設定’;

都道府県

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=’都道府県’;

市や区

市や区などは、最初は最低限から始まり、後で増えていく可能性が高いので、件数が増えた時に自由に表示順位を変えられるように『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とする

英単語で複数系ですが、英単語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テーブルのデータは各自追加するなり減らすなりしてください。)

全国対応のテーブル dumpファイル