八地方※と都道府県の多対多の関係を表す関連テーブル
郵便番号データベースのテーブル定義
八地方※には都道府県があります。
1:北海道 => 1:北海道
2:東北地方
=> 2:青森県、3:岩手県、4:宮城県、5:秋田県、
6:山形県、7:福島県
3:関東地方
=> 8:茨城県、9:栃木県、10:群馬県、11:埼玉県、
12:千葉県、13:東京都、1:4神奈川県
・
・
ここの八地方と都道府県には親子関係にあるあると呼ぶことができます。
この親子関係にある関係を保持するために中間テーブルとして定義します。
1(北海道） = 1(北海道)
2(東北地方） = 2（青森県)
2(東北地方） = 3(岩手県)
2(東北地方） = 4(宮城県）
2(東北地方） = 5（群馬県)
・
・
この中間テーブルの参照により八地方がどの都道府県を所有しているか、
あるいはある都道府県がどの地域に属しているかが用意に表現できます。
※ 八地方・・・日本の地方を北海道、東北、関東、中部、近畿、中国、四国、九州沖縄と分類します。
ここでは八地方と呼ぶことにします。
八地方テーブルと中間テーブルの作成
今回使用するデータベースは mysql(ver5.7)でPDO経由でアクセスします。
以前の投稿PDO経由でデータベースに接続する。で確認いただけます。
郵便番号データベースで都道府県テーブルは前の投稿郵便番号データベースのテーブル作成で定義してあります。ここで作成するテーブル類はその郵便番号データベース内に追加で定義しました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// 八地域テーブル $create_area = <<< EOM create table area ( area_no int(11) not null PRIMARY KEY, area_name varchar(32) not null, area_kana varchar(32)); EOM // 八地域と都道府県の多対多を表現する中間テーフル $create_areapref = <<< EOM create table areapref ( id int(11) not null PRIMARY KEY AUTO_INCREMENT, area_no int(11) not null, pref_no int(11) not null); EOM; |
作成したテーブルをコマンドラインで確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
// 作成したテーブル構造を確認 // 八地方区分 mysql> show fields from area; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | area_no | int(11) | NO | PRI | NULL | | | area_name | varchar(32) | NO | | NULL | | | area_kana | varchar(32) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.06 sec) // 八地方と都道府県との多対多中間ファイル mysql> show fields from areapref; +---------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | area_no | int(11) | NO | | NULL | | | pref_no | int(11) | NO | | NULL | | +---------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) // 都道府県データベース // このテーブルは前の投稿で作成しました。 mysql> show fields from prefecture; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | pref_no | int(11) | NO | PRI | NULL | | | pref_name | varchar(30) | NO | | NULL | | | pref_kana | varchar(30) | NO | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
八地域テーブルと中間テーブルにデータを挿入
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
// 八地方区分(北海道、東北、関東、中部、近畿、中国、四国、九州沖縄)テーブルデータインサートする。 $stmt = $pdo->prepare("insert into area (area_no,area_name,area_kana) ". "values (?, ?, ?)"); $stmt->execute( [1, "北海道", "ホッカイドウ"]); $stmt->execute( [2, "東北", "トウホク"]); $stmt->execute( [3, "関東", "カントウ"]); $stmt->execute( [4, "中部", "チュウブ"]); $stmt->execute( [5, "近畿", "キンキ"]); $stmt->execute( [6, "中国", "チュウゴク"]); $stmt->execute( [7, "四国", "シコク"]); $stmt->execute( [8, "九州・沖縄", "キュウシュウオキナワ"]); // 八地方区分と都道府県の多対多のテーブルにデータ挿入 $stmt = $pdo->prepare("insert into areapref (area_no,pref_no) values (?, ?)"); $areapref_array = array( [1, 1], [2, 2 ],[2, 3 ],[2, 4 ],[2, 5 ],[2, 6 ],[2, 7 ], [3, 8 ],[3, 9 ],[3, 10],[3, 11],[3, 12],[3, 13],[3, 14], [4, 15],[4, 16],[4, 17],[4, 18],[4, 19],[4, 20],[4, 21],[4, 22],[4, 23], [5, 24],[5, 25],[5, 26],[5, 27],[5, 28],[5, 29],[5, 30], [6, 31],[6, 32],[6, 33],[6, 34],[6, 35], [7, 36],[7, 37],[7, 38],[7, 39], [8, 40],[8, 41],[8, 42],[8, 43],[8, 44],[8, 45],[8, 46],[8, 47]); foreach( $areapref_array as $areapref ){ $stmt->execute( $areapref ); } |
作成された中間テーブルでSQLを発行して確認してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
// pref_no=13 （東京都) を指定してみる。 mysql> select area.area_name,prefecture.pref_no,prefecture.pref_name from areapref -> inner join area on areapref.area_no = area.area_no -> inner join prefecture on areapref.pref_no = prefecture.pref_no -> where prefecture.pref_no=13; +-----------+---------+-----------+ | area_name | pref_no | pref_name | +-----------+---------+-----------+ | 関東 | 13 | 東京都 | +-----------+---------+-----------+ 1 row in set (0.00 sec) // pref_no=３ （岩手県) を指定してみる。 mysql> select area.area_name,prefecture.pref_no,prefecture.pref_name from areapref -> inner join area on areapref.area_no = area.area_no -> inner join prefecture on areapref.pref_no = prefecture.pref_no -> where prefecture.pref_no=3; +-----------+---------+-----------+ | area_name | pref_no | pref_name | +-----------+---------+-----------+ | 東北 | 3 | 岩手県 | +-----------+---------+-----------+ 1 row in set (0.00 sec) // 関東地方を指定してその所属する都道府県を求める mysql> select area.area_name,prefecture.pref_no,prefecture.pref_name from areapref -> inner join area on areapref.area_no = area.area_no -> inner join prefecture on areapref.pref_no = prefecture.pref_no -> where area.area_no=3; +-----------+---------+--------------+ | area_name | pref_no | pref_name | +-----------+---------+--------------+ | 関東 | 8 | 茨城県 | | 関東 | 9 | 栃木県 | | 関東 | 10 | 群馬県 | | 関東 | 11 | 埼玉県 | | 関東 | 12 | 千葉県 | | 関東 | 13 | 東京都 | | 関東 | 14 | 神奈川県 | +-----------+---------+--------------+ 7 rows in set (0.00 sec) |