【MySQL】データ・データベース・テーブルの正規化について

このページについて

このページではMySQLでデータベースを作成・構築する上で必須の考え方である「正規化」についての解説をしています。

正規化って何?

正規化は簡単に言えば「データベース本来の使い方に沿ってデータを格納する」感じのことです。これはエクセルにも言えることですが、1セル1データが基本ですよね。1つのセルに改行などを入れて無理やり複数の要素やデータを入れ込んだ場合、別のソフトなどで読み込んだ時に正確に読み込めない可能性があります。

それはデータベースでも同じで、基本的に1つのデータのみを格納します。つまりどういうことかというと、下記のテーブルがあるとします。太字になっているのが主キーとします。

これは非正規化テーブルとか非正規状態とか言います。この場合取引番号がセルを跨いで表示されています。そしてカードパックAが2度登場しています。取引番号から探すなら取引番号のみ検索すればそれぞれの取引商品や金額が全て取得できます。しかしデータベースではセルの統合みたいな機能や使い方はしません。

これをわざわざ分離するメリットを挙げるとレコード(行)の編集をする時に一ヶ所だけで済むことが一番大きな要因かなと思います。他にも処理速度を上げれるとか取得する時に不必要なデータを除外できるとか、そういうのもあります。ただシンプルかつ一番便利なのはやはり更新のお手軽さですね。

最終的にこのテーブルは以下のテーブルにそれぞれ分離されます。

• 商品名・商品番号
• 取引番号

なぜこのように分離されるのか、その分離の過程も解説していきます。

正規化の前に知っておくべきワード

正規化以前に、まずはテーブルで使われるワードについて知っておく必要があります。今回のページで出てくるワードは以下の通りです。

主キー

PRIMARYキーのこと。NULLが扱えないのと、そのテーブル内で一意であるという2つの制約があります。詳しくはキーの種類のところで。

複合キー

PRIMARYキーを複数カラムに設定することを言います。例えば「商品番号」と「取引番号」の2つのカラムをまとめてPRIMARYキーに設定すれば、それは複合キーと言います。この2つの組み合わせがそのテーブルの中で一意である必要があります。

候補キー

主キーや複合キーではなく、キーが与えられていないカラムの中から主キーや複合キーと似たような扱いができるものの事を、候補キーと言います。

第1正規化

まずは一番最初の正規化から紹介します。これは紹介サイトや書籍等で異なる説明がされているのですが、簡単に言えば「セルの統合をしない」です。今回の例だと取引番号がセルを跨いでいるので、それを解除します。すると以下のように変化します。

単一のデータを格納するという前提に従っただけですね。人にとっては見づらいかもしれませんが、データベースにとってはこれが最低限の形式となります。

そして今回の場合、不要なカラムが存在します。それは合計金額です。これは単価*数量で求めることができるので、わざわざ入力する必要はありません。そして現実的なことを言うと消費税が変わるとその度に合計金額も計算して入力しなければならなくなります。計算式を1つ作成しておき、単価と数量を入れたら自動で消費税も考慮した合計金額を出してくれるような仕組みにするのがベストです。なので合計金額を削除して、第1正規化終了です。

第2正規化

次にこれを分解していくんですが、ここで重要なワードが出てきます。

関数従属

特定の項目が決まれば、別の項目も一意に決まること。今回の例だとカードパックという商品名が分かれば、単価がわかります。

完全関数従属

複数の候補キーが非キーに対して関数従属になっていること。例えば「商品番号」と「取引番号」の組み合わせが候補キーの場合、数量が出てくることになります。この数量は非キーなので完全関数従属と言えます。

部分関数従属

「複合キーの一部の項目で例の一部の値が一意に決まる関係」を部分関数従属と言います。今回の例だと「商品番号」と「取引番号」が複合キーだとした場合に、商品名から単価が判明します。数量も判明します。これらは部分関数従属と言えます。

正規化の2ステップでは、これらを分離します。具体的に分離すると以下のテーブルができます。

↑は取引番号というキーに対して部分関数従属となっている商品番号と数量を分けたテーブルです。

このテーブルは商品番号に部分関数従属となっている商品名、単価を分けたテーブルです。 このように分けたのは「部分関数従属している部分をそれぞれ分ける」というルールに従っただけです。つまりこのテーブルは「商品番号」+「取引番号」の複合キーで注文内容を特定しています。なので「商品番号」が分かれば商品名と単価がわかります。「取引番号」が分かれば商品番号と購入量がわかります。なのでそれぞれのテーブルを作成したというお話です。

第3正規化

通常はこの第3正規化まで行ったら終了です。それ以上は本当によくわからないというかやればやるほど面倒になるのです。なのでまずはここまでを目指しましょう。この正規化では「主キーに間接的に従属している部分を別テーブルにする」というものです。そして更新時などにエラーが出ないようにする必要もあります。

例えば以下のテーブルがあったとします。

この第3正規化では{x}→{y}→{z}である時に{y}→{x}が成り立たない場所を考えます。つまり「取引番号{x}」が分かれば「取引相手{y}」・「顧客コード{z}」の2つがまとめてわかります。しかし、「取引相手{y}」が分かったところで「取引番号{x}」を特定することはできません。なのでここを分離します。

まずは「取引番号」と、「顧客コード」のテーブルです。ここで顧客コードを選んだ理由ですが、これが上で軽く触れた「更新時にエラーが出ないようにする」対策です。取引相手の名前が変わることはあっても、顧客コードは変わらないケースがほとんどです。なので取引相手で参照している場合、相手の名前が変わったら該当箇所を全て変更する必要が出てきます。その反面、顧客コードを主キーとした別テーブルを作成しておけばそのテーブルの一ヶ所のみを変更すれば、該当する顧客コードを参照しているテーブル全てに変更が適用されます。

次に「顧客コード」を主キーとしたテーブルです。

正規化したテーブルの活用方法

正規化したテーブルはそれぞれで単一の情報を持つまでに分離されます。ここまで分離して元の表に戻るのか？って話ですが、戻ります。第3正規化までは「データの無損失分解」が目的です。無損失なので元の形に戻すことも容易です。

これらのテーブルは全て主キーによって結合できます。例えば取引相手が1の主キーの注文内容を見るってなったらそのようなリクエストをデータベースに送れば1の注文内容のみが返ってきます。たとえその注文内容のテーブル内に2や3などのデータがあろうと、1だけを探して組み上げて結果として返してくれます。

このような関係性のことをリレーショナルと言います。是非とも使いこなしてみてください。

MySQLの用語集

MySQLでよく使う・よく見る用語集です。知っておくと検索とかも便利になります。

ローカル環境の整え方

MAMPを使ってローカル環境を整える方法を紹介。

MySQLデータベースに接続する方法

MAMPのデータベースと、レンタルサーバーのデータベースに接続する方法

MySQLの基本コマンド

MySQLへ接続してまず使う基本コマンドの一覧です。

データベースの作成

ターミナル・phpMyAdminでのデータベースの作成方法をそれぞれ解説。

テーブルの作成

ターミナル・phpMyAdminのそれぞれでテーブルを作成する方法と、作成と同時にPRIMARYやUNIQUEの付与などの方法を紹介。

キーの種類と機能

カラムに設定出来るキーの種類と機能を解説。

正規化

リレーショナルデータベースを使う上で必須となる正規化について。データの追加や更新などで手間を省く方法としても使えます。

ALTER TABLE

ALTER TABLE構文を使って作成済みのテーブルに対して操作を行う。PRIMARYの付与やカラムの移動・名前変更・追加・削除なども行えます。

INSERT構文

INSERT構文を使って指定テーブルにデータ(行)を挿入する方法の解説です。

UPDATE構文

UPDATE構文を使って指定テーブルのカラムを一括更新、もしくは指定レコードの指定カラムのみを更新する方法の解説です。

SELECT構文

SELECT構文を使って指定したテーブルからデータを取得する方法の解説です。

JOIN

JOINを使ってテーブル同士を結合する方法の解説です。