質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Q&A

解決済

1回答

1369閲覧

データベースの正規化やどのデータベースを使用するべきかがわからない>都道府県、市町村、複数人入力

oyatsu8

総合スコア97

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

0グッド

2クリップ

投稿2017/11/12 14:26

編集2017/11/12 17:40

最近MySQLを勉強しだし、データベースをwebに反映させたいのですが、正規化について、どう考えていいかよくわからないことがあったので質問させて頂きます。

現在、MySQLに入れたデータ(商品名、説明、商品の属性1、商品の属性2、商品の属性3)を反映させたいと思っています。以下が現在の仕様と、やりたいこと、わからないことです。

  • データ量は現在300件で、今後増える予定ですが、どんなに増えても2000件くらいまでです。

  • 今のところ、PHPとMySQLの組合せで作ろうと考えています。都道府県、市町村名はフォームのselectで選ぶため、表記ゆれはありません。

  • 現在は1つのテーブルに全てまとめていて、プライマリーキーはidです

  • 元々はエクセル(csv)でローカルで管理していて、入力者は自分のみでしたが、今回、複数人入力(web上のフォームから)が出来るようにしたいと考えています。

やりたいこと
0. 都道府県単位での統計データを反映させたい
0. 次に、各都道府県内の市町村レベルの統計データを反映させたい
0. 商品名は被る場合があるが、属性と、都道府県、市町村の違いで識別したい(場所、名前、属性のどれか1つでも違っていたら、重複していない物として識別)
0. 各属性、都道府県、市町村で検索が出来るようにしたい

わからないこと
0. 現在はすべて同じテーブルにデータを置いています(Excelなどと変わらない状態)が、そもそも正規化が必要かどうか
0. MySQL上でどのような正規化をしたらよいのかわからない(下記に考えてみた正規化を貼ります)
0. 地図を使うため、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統計データ
--以下市町村名続く統計データ
--沖縄の市町村最後の番号統計データ

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

専門家ではありませんが、経験で話をします。

正規化の粒度について

データベースの正規化はたしかに必要ですが、やりすぎると今度はデータを取り出すときの結合(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)市区町村名緯度経度
0110021札幌市43.06141.35
0220392八戸市40.52141.50

売上テーブル

order_item_id(PK)dateitem_id(FK)city_id(FK)income
12017-01-0110110021980
22017-02-0210220394800
32017-03-0320110022580
12017-04-0410110029800

で、市区町村・商品ごとの売り上げ合計を出すのは次のようなSQLで行います。

sql

1SELECT 都道府県名, 市区町村名, 商品名, SUM(income) AS 売上合計 2FROM 売上 3INNER JOIN 市区町村 USING(city_id) 4INNER JOIN 都道府県 USING(pref_id) 5INNER JOIN 商品 USING(item_id) 6GROUP BY city_id, item_id

※MariaDB 等でエラーになるなら GROUP BY 都道府県名, 市区町村名, 商品名 で。

このようにしておくことで、地域別のほかに月ごとの売り上げ推移を計算したりと柔軟な集計が可能になります。


こうしたデータベース設計は学問と長年のノウハウの結晶です。おそらく教本もたくさん売られているので体系的に学習されるのもよいでしょう。

データベース設計とは少し違うかもしれませんが、個人的におすすめしたいのが SQLアンチパターン という本です。やりがちなNG集とその対策をまとめており「正規化すべきだ!」と正論を押しつけるのではなく、具体的な事例を例にどういけないのか・どうすべきなのかを教えてくれる名著です。

投稿2017/11/12 19:07

miyahan

総合スコア3095

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

oyatsu8

2017/11/13 04:18

大変丁寧かつ、分かりやすい回答を頂き、ありがとうございます!! 申し訳ないのですが、良く理解出来ない部分があり、2つ質問させて頂けないでしょうか [1]正規化の粒度についての回答の部分で もし、商品の素材が、丸形と三角の複合型(どちらでもある)場合は別テーブルということでよいのでしょうか また、商品名が同じでも形が違う場合、形や色が同じでも商品名が違う場合がある場合に、どちらにしても別の商品として扱うのですが、 その場合も別テーブルにした方がいいのでしょうか? [2]統計データについての回答の部分で > たとえば次のテーブルは、この人が結局いつ生まれたのかわかりません。システムとしては正しく動いていますが、中身は壊れています。 誕生年(西暦) と誕生年(和暦)は自分から見ると、違う名称なので、同じ誕生年ではありますが、西暦か和暦のどちらかを選んで取り出せばいいのではないかと思うのですが、同じもの(意味)を指すカラムが2つあってはいけないという意味でしょうか? >こうしたデータベース設計は学問と長年のノウハウの結晶です。 遠い道のりになりそうですが、がんばろうと思います。書籍も購入しようと思います。 大変ありがとうございました。
miyahan

2017/11/13 04:29

>丸形と三角の複合型(どちらでもある)場合は別テーブル そのほうが良いと思います。ゼッタイに避けるべきは1つのカラムに "丸,三角" と複数の情報を入れたり、カラムを増やす(形1, 形2, 形3... のように)ことです。 >同じもの(意味)を指すカラムが2つあってはいけない そのとおりです。 なおその例は「2016年」と「平成25年(2013年)」で矛盾が生じています。このように矛盾の混入を許してしまう構造は避けなければならないという趣旨で書きました。
oyatsu8

2017/11/13 04:55

お返事をありがとうございます。書かれたことを参考に正規化してみようと思います。 大変ありがとうございました!!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問