質問: 最近MySQLを勉強しだし、データベースをwebに反映させたいのですが、正規化について、どう考えていいかよくわからないことがあったので質問させて頂きます。 現在、MySQLに入れたデータ(商品名、説明、商品の属性1、商品の属性2、商品の属性3)を反映させたいと思っています。以下が現在の仕様と、やりたいこと、わからないことです。 * データ量は現在300件で、今後増える予定ですが、どんなに増えても2000件くらいまでです。 * 今のところ、PHPとMySQLの組合せで作ろうと考えています。都道府県、市町村名はフォームのselectで選ぶため、表記ゆれはありません。 * 現在は1つのテーブルに全てまとめていて、プライマリーキーはidです * 元々はエクセル(csv)でローカルで管理していて、入力者は自分のみでしたが、今回、複数人入力(web上のフォームから)が出来るようにしたいと考えています。 **やりたいこと** 1. 都道府県単位での統計データを反映させたい 2. 次に、各都道府県内の市町村レベルの統計データを反映させたい 3. 商品名は被る場合があるが、属性と、都道府県、市町村の違いで識別したい(場所、名前、属性のどれか1つでも違っていたら、重複していない物として識別) 4. 各属性、都道府県、市町村で検索が出来るようにしたい **わからないこと** 1. 現在はすべて同じテーブルにデータを置いています(Excelなどと変わらない状態)が、そもそも正規化が必要かどうか 2. MySQL上でどのような正規化をしたらよいのかわからない(下記に考えてみた正規化を貼ります) 3. 地図を使うため、PostgreSQLの方がよいと言われたが、まだ使った事がないため、1000件程度のデータベースを作成する場合でも、PostgreSQL方がいいのか、また、 PostgreSQLがいい場合、どの言語(PHP,Javascript,など)と組合せて作るのがいいのかも教えて頂けたら幸いです。 **現在の状態(Excelのまま)** | id | 商品名 | 商品の形 | 商品の色 | 都道府県 | 市町村 | 都道府県の統計データ | 市町村の統計データ | | --- | --- | --- | --- | --- | --- | --- | --- | | 01 | 商品01 | 丸型 | 赤 | 北海道 | 北海道の市町村01 | 統計データ | 統計データ | | -- | 商品最後 | 三角 | 青 | 沖縄 | 沖縄の市町村最後 | 統計データ | 統計データ | **変更後の状態(どう正規化するのか検討中)** | id | 商品名 | | --- | --- | | 01 | 商品01 | | -- | 商品最後 | | id_property01 | 商品の形 | | --- | --- | | 01 | 丸型 | | 02 | 三角 | | 03 | 四角 | | -- | -- | | -- | 最後の形 | | id_property02 | 商品の色 | | --- | --- | | 01 | 赤 | | 02 | 青 | | 03 | 黄色 | | -- | -- | | -- | 最後の色 | | id_prefecture | 都道府県 | 都道府県の統計データ | | --- | --- | --- | | 01 | 北海道 | 統計データ | | 02 | 青森 | 統計データ | | -- | 以下都道府県続く | 統計データ | | 47 | 沖縄 | 統計データ | | id_municipality_town | 市町村 | 市町村の統計データ | | --- | --- | --- | | 01 | 北海道の市町村01 | 統計データ | | 02 | 北海道の市町村01 | 統計データ | | -- | 以下市町村名続く | 統計データ | | -- | 沖縄の市町村最後の番号 | 統計データ |
回答: 専門家ではありませんが、経験で話をします。 正規化の粒度について データベースの正規化はたしかに必要ですが、やりすぎると今度はデータを取り出すときの結合(JOIN)が煩雑になり、動作スピードも遅くなることがあります。ほどほどなのが大事です。 たとえば「商品の形」と「商品の色」の**値が一意に決まる場合は同じテーブルに**入れてしまって問題ないと思います。逆に複数の値を取る(たとえば 黒 かつ 白 とか)場合や、その情報を持ち得ないレコード(たとえば「形」を持たない"旅行パック"とか)がある場合は別テーブルが良いでしょう。 また属性がどんどん増えてカラムが肥大化したり、値が決めにくくなったりする場合は「タグ」としてまとめても良いかも知れません。 ### 主キーについて 各テーブルの主キーとして数値の `id_***` がありますが、これを「サロゲートキー」(代理キー)と呼びます。一方で "東京都" のように情報そのものを使ったものを「ナチュラルキー」と呼びます。 どちらを使うべきかは常に意見が分かれます。個人的には**1つのカラムがユニークになっているならナチュラルキー**、複数カラムの組み合わせがユニークになるなら要審議という感じです。今回なら都道府県は迷わずナチュラルキー、市町村はサロゲートキーを使うか、"都道府県"+"市町村" の複合カラムでナチュラルキーとするか悩ましいところです。もし市町村コードが使えるなら、それ1つでナチュラルキーとして採用できます。 ### 統計データについて 何の情報を統計するかわからないので答えにくいのですが、データベースには**他のテーブル・カラムから間接的に取り出せる情報は記録してはいけない**(重複してはいけない)という原則があります。これは矛盾した情報が記録されてデータベースが破綻するのを防ぐためです。 たとえば次のテーブルは、この人が結局いつ生まれたのかわかりません。システムとしては正しく動いていますが、中身は壊れています。 | 会員名 | 誕生年(西暦) | 誕生年(和暦) | | --- | --- | --- | | 鈴木一郎 | 2016 | 平成25 | というわけで、もし別のテーブルから算出が可能であれば、「都道府県の統計データ」・「市町村の統計データ」カラムは作らず、**必要になった際にその都度計算する**のがよいと思います。 ただし例外として、数千人のユーザーが同時アクセスするだとか、集計対象が億単位で処理が間に合わない・結果がでるまで遅いという場合は、データが壊れるリスクを許容して集計結果をキャッシュとして記録しておく工夫もアリです。 ### MySQL vs PorstgeSQL について 「地図を使うならPostgreSQL」というのは、おそらく空間データ(GIS)の取り扱いのことを指していると思いますが、MySQL 5.7 で GIS 関連が大幅に強化さています。それでもまだ、PostgreSQL + PostGIS のほうが強力で柔軟なようですが、地球が丸いことを考慮するような高精度な距離計算等をする場合の話なので、日本列島内の平面距離を計算する上では **MySQL と PorstgeSQL に大差はない**と思います。 一般的なWebアプリであれば、やはり多く採用されている LAMP(Apache + MySQL + PHP)が作りやすく、設置もしやすいかと思います。 最後に商品の売り上げ集計という想定で、自分ならこうするかなーという例を示します。 商品テーブル | item_id(PK) | 商品名 | 価格 | | --- | --- | --- | | 1 | 野球バット | 5000 | | 2 | えんぴつ | 50 | | 3 | プリン | 100 | | 4 | 洗濯機 | 100000 | 商品属性テーブル | item_id(PK) | タグ(PK) | | --- | --- | | 1 | スポーツ用品 | | 1 | 棒状 | | 1 | 学校用品 | | 2 | 文房具 | | 2 | 消耗品 | | 2 | 学校用品 | | 3 | 食品 | | 3 | おやつ | | 3 | 甘い | | 4 | 家電 | | 4 | 大きい | 都道府県テーブル(ENUMでもよいかもしれません) | pref_id(PK) | 都道府県名 | | --- | --- | | 1 | 北海道 | | 2 | 青森県 | 市区町村テーブル | city_id(PK) | pref_id(FK) | 市区町村名 | 緯度 | 経度 | | --- | --- | --- | --- | --- | | 011002 | 1 | 札幌市 | 43.06 | 141.35 | | 022039 | 2 | 八戸市 | 40.52 | 141.50 | 売上テーブル | order_item_id(PK) | date | item_id(FK) | city_id(FK) | income | | --- | --- | --- | --- | --- | | 1 | 2017-01-01 | 1 | 011002 | 1980 | | 2 | 2017-02-02 | 1 | 022039 | 4800 | | 3 | 2017-03-03 | 2 | 011002 | 2580 | | 1 | 2017-04-04 | 1 | 011002 | 9800 | で、市区町村・商品ごとの売り上げ合計を出すのは次のようなSQLで行います。 ``` SELECT 都道府県名, 市区町村名, 商品名, SUM(income) AS 売上合計 FROM 売上 INNER JOIN 市区町村 USING(city_id) INNER JOIN 都道府県 USING(pref_id) INNER JOIN 商品 USING(item_id) GROUP BY city_id, item_id ``` ※MariaDB 等でエラーになるなら `GROUP BY 都道府県名, 市区町村名, 商品名` で。 このようにしておくことで、地域別のほかに月ごとの売り上げ推移を計算したりと柔軟な集計が可能になります。 こうしたデータベース設計は学問と長年のノウハウの結晶です。おそらく教本もたくさん売られているので体系的に学習されるのもよいでしょう。 データベース設計とは少し違うかもしれませんが、個人的におすすめしたいのが SQLアンチパターン という本です。やりがちなNG集とその対策をまとめており「正規化すべきだ!」と正論を押しつけるのではなく、具体的な事例を例にどういけないのか・どうすべきなのかを教えてくれる名著です。