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

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

ただいまの
回答率

90.76%

  • MySQL

    5531questions

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

  • PostgreSQL

    992questions

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

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 471

oyatsu8

score 65

最近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 統計データ
-- 以下市町村名続く 統計データ
-- 沖縄の市町村最後の番号 統計データ
  • 気になる質問をクリップする

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+4

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

 正規化の粒度について

データベースの正規化はたしかに必要ですが、やりすぎると今度はデータを取り出すときの結合(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集とその対策をまとめており「正規化すべきだ!」と正論を押しつけるのではなく、具体的な事例を例にどういけないのか・どうすべきなのかを教えてくれる名著です。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/13 13:18

    大変丁寧かつ、分かりやすい回答を頂き、ありがとうございます!!

    申し訳ないのですが、良く理解出来ない部分があり、2つ質問させて頂けないでしょうか

    [1]正規化の粒度についての回答の部分で
    もし、商品の素材が、丸形と三角の複合型(どちらでもある)場合は別テーブルということでよいのでしょうか
    また、商品名が同じでも形が違う場合、形や色が同じでも商品名が違う場合がある場合に、どちらにしても別の商品として扱うのですが、
    その場合も別テーブルにした方がいいのでしょうか?

    [2]統計データについての回答の部分で
    > たとえば次のテーブルは、この人が結局いつ生まれたのかわかりません。システムとしては正しく動いていますが、中身は壊れています。

    誕生年(西暦) と誕生年(和暦)は自分から見ると、違う名称なので、同じ誕生年ではありますが、西暦か和暦のどちらかを選んで取り出せばいいのではないかと思うのですが、同じもの(意味)を指すカラムが2つあってはいけないという意味でしょうか?


    >こうしたデータベース設計は学問と長年のノウハウの結晶です。

    遠い道のりになりそうですが、がんばろうと思います。書籍も購入しようと思います。
    大変ありがとうございました。

    キャンセル

  • 2017/11/13 13:29

    >丸形と三角の複合型(どちらでもある)場合は別テーブル
    そのほうが良いと思います。ゼッタイに避けるべきは1つのカラムに "丸,三角" と複数の情報を入れたり、カラムを増やす(形1, 形2, 形3... のように)ことです。

    >同じもの(意味)を指すカラムが2つあってはいけない
    そのとおりです。
    なおその例は「2016年」と「平成25年(2013年)」で矛盾が生じています。このように矛盾の混入を許してしまう構造は避けなければならないという趣旨で書きました。

    キャンセル

  • 2017/11/13 13:55

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

    キャンセル

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

  • ただいまの回答率 90.76%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

  • 解決済

    エクセルでURLの一覧を並べ替え時、階層構造を保つには?

    エクセルでURLの一覧を並べ替え時、階層構造を保つには? /a.html /b.html /aaa/ /bbb/ という風に並べ替えしたい 通常は昇順でいく

  • 解決済

    mysqlで数100列のテーブルを作りたい

    今、mysqlにてテーブルを185列のテーブルを作成しようとしています。 (以下のコード) mysql> create table kifus( -> id int n

  • 解決済

    insert into でインサートできない

    df内のweekの後ろの1文字を除去して、dfのmonthにinsertしたいのですが、 エラーは出ないのにデータを確認するとインサートされていません。 --行追加 alter

  • 受付中

    Excelで分単位でバラバラに記録されたデータから将来予測

    前提・実現したいこと 艦これの支援ソフト「航海日誌拡張版」から出力される資源ログのデータをもとに、指定日を初日として、このまま遠征等を続けたらいかほど資源が獲得できるかを将来予測す

  • 解決済

    Mysql,MariaDBで画像を保存する

    動作環境はクライアントで画像を選んでJavascriptもしくはhtml埋め込みphpでサーバーphpで処理 PDOでDBに画像パス保存 といった感じです。 この際、ク

  • 解決済

    count関数について (PostgreSQL)

    現在SQLを学んでいます。 count関数について画像右の【販売履歴テーブル】でなぜエラーになるのか理解ができません。教本には 「【商品区分】もgroup byによってグループ

  • 解決済

    SQL 複数テーブルの結合 JOINについて

    SQLを勉強しています。 以下のテーブルを結合するSQL文を見て結果を予想しろ。という内容の問題がありました。 テーブルA                   A1

  • 解決済

    MySQLのカラムに収めるデータ形式について

    お世話になります。 勉強しながらPHPとMySQLの連携で小規模なウェブサイトを作ったりしている身ですが、新しくアプリを設計するとき、表題にある「カラムに収めるデータの形式」につい

同じタグがついた質問を見る

  • MySQL

    5531questions

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

  • PostgreSQL

    992questions

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

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