Help us understand the problem. What is going on with this article?

【初心者向け】リレーショナルデータベースの正規化

More than 1 year has passed since last update.

webシステムを開発していく上で、フロントエンドやバックエンドなどのアプリケーションの実装に対する知見が必要なことは当然ですが、それと同様に知っておくべきことの中にデータベースの設計があります。
データベースはビジネスにおいて重要なデータを保存(永続化)する役割をもつので、webシステムの根幹を司る要素であり非常に重要です。

今回はそんなデータベースを設計する際のテクニックの一つである正規化についてまとめました。
あまり厳密な説明ではないかもしれませんが、雰囲気が伝われば幸いです。

正規化とは

wiki大先生によると

関係データベース (リレーショナル・データベース) において、正規形と呼ばれる形式に関係(リレーション)を準拠させることにより、データの一貫性の維持と効率的なデータアクセスを可能にする関係設計を導くための方法である。

また、

正規形には様々なものが存在するが、いずれにせよ、正規化を行うことにより、データの冗長性と不整合が起きる機会を減らすことができる。

とのことです。
つまり正規化とは、データの異常や不整合、冗長性の排除を実現するための手法です。

正規化の目的

素早く正規形を見抜く実践テクニックによると、

1事実1カ所(1 fact in 1 place)を目指して、テーブルの整合性を保ったまま、テーブルの冗長性を排除して、データを効率的に管理できるようにすること

だそうです。

つまり、管理しやすいテーブル設計にすることを目的としています。

余談ですが、正規化に失敗したDB(およびそれを支えるwebシステム)は、冗長なカラムやテーブルが散財しており、データの整合性が取れていないことが多々あります。

どのテーブルのどの値が現実の事項を正しく反映しているか分からないのに、それを土台にしてアプリケーションを構築するのは非常に苦痛なものです(実際はアプリケーション側も信頼できない可能性が高いですが...)。

また、その上で機能拡張や保存するデータの種類が増えると悪夢は絶えません。。

というように、webシステムに蓄積していく大切なデータを、効率的に管理するために正規化を行います。

正規化のステップ

正規化をしていくには主に6つのステップを経ていきます。
ただ、実際の業務では主に第1正規形~第3正規形までが行われますので、今回は第3正規形までについてご紹介します。

- 第1正規形(1NF)
- 第2正規形(2NF)
- 第3正規形(3NF)
- ボイスコッド正規形(BCNF)
- 第4正規形(4NF)
- 第5正規形(5NF/PJNF)

今回は下記のような八百屋さんの商品管理台帳(といっても厳密な台帳ではありませんが..)を例に、正規化を行なっていきます。
Screen Shot 2018-11-04 at 15.55.58.png

第1正規形

第1正規形を満たすためには、「リレーションであること」が求められます。
リレーションとは主に下記を満たすことです。

1. 行が上下で順序づけされていない
  - 行の位置に依存したテーブルであってはならない
2. 列が左右で順序づけされていない
  - カラムの位置に依存したテーブルであってはならない
3. 重複する行は存在しない
4. カラムの値は、そのカラムを構成するドメインに属するデータを一つ持っている
  - 繰り返しグループがあってはならない
5. 全ての列の値は定義されたものだけであり、かつそれぞれの業において常に存在する

この中でもよく例に挙げられる事項としては、繰り返しグループがないことです。

上に挙げた商品台帳の場合、一つの行の中に(といってもセルを結合させていますが..)複数の商品情報が詰め込まれています。
第1正規形を満たすためには下記のように、複数レコードにデータを分けます。
また、在庫数は入出荷の数から計算できるので削除してしまいます(今回は簡単に計算できると仮定)。
既存のデータを元に導出(計算)できるものも削除するのがミソです。

これによりひとまず、同じ行の中での繰り返しグループは無くなりました。

商品台帳

仕入先 住所 代表者 代表者連絡先 商品名 単価 入荷数 入荷日
山田農園 鹿児島県 山田太郎 080-1111-2222 さつまいも 150 20 2018/10/15
山田農園 鹿児島県 山田太郎 080-1111-2222 ごぼう 200 20 2018/10/15
磯部農園 北海道 磯部五郎 090-1111-2222 玉ねぎ 100 50 2018/10/01
磯部農園 北海道 磯部五郎 090-1111-2222 かぼちゃ 130 10 2018/10/01

第2正規形

第1正規形を経て行内の重複は取り除けましたが、まだデータを管理するには不便な事項があります。
例えば、代表者の連絡先が変更になった場合には、テーブル内で該当する全ての連絡先を変更する必要があります。
また、新しい契約先農家が増えたとしても、全ての情報が揃うまではテーブルに記載できません。

これらの原因は、一つのテーブルに色々な要素を詰め込みすぎていることによります。
プログラムで言うところの、単一責任ではない、といったニュアンスです。

商品台帳としてレコードを一意に決めるためには、[仕入先、商品名、入荷日]あたりがあれば十分です。
そのためそれ以外の要素については、それぞれの要素ごとに別のテーブルに分けます。

入荷情報

仕入先 商品名 入荷数 入荷日
山田農園 さつまいも 20 2018/10/15
山田農園 ごぼう 20 2018/10/15
磯部農園 玉ねぎ 50 2018/10/01
磯部農園 かぼちゃ 10 2018/10/01

仕入先情報

仕入先 住所 代表者 代表者連絡先
山田農園 鹿児島県 山田太郎 080-1111-2222
磯部農園 北海道 磯部五郎 090-1111-2222

商品情報

商品名 単価
さつまいも 150
ごぼう 200
玉ねぎ 100
かぼちゃ 130

このように、分解したリレーションの情報を使って元のリレーションを再構築できることを無損失分解と言います。

第3正規形

以上により、テーブルの冗長性は随分と減りましたが、まだ改善の余地はあります。
例えば、山田農園を営んでいる山田太郎さんが他にも農園を持っていた場合です。

仕入先情報

仕入先 住所 代表者 代表者連絡先
山田農園 鹿児島県 山田太郎 080-1111-2222
山田ほのぼのファーム 鹿児島県 山田太郎 080-1111-2222
磯部農園 北海道 磯部五郎 090-1111-2222

この場合、山田さんの連絡先が変わってしまうと仕入先情報を管理しているテーブルで複数のカラムに対して更新をする必要が出てきます。
この場合、代表者をキーとして、代表者の情報を管理するためのテーブルを別途作ることにします。

代表者情報

代表者 連絡先
山田太郎 080-1111-2222
磯部五郎 090-1111-2222

入荷情報

仕入先 商品名 入荷数 入荷日
山田農園 さつまいも 20 2018/10/15
山田農園 ごぼう 20 2018/10/15
磯部農園 玉ねぎ 50 2018/10/01
磯部農園 かぼちゃ 10 2018/10/01

仕入先情報

仕入先 住所 代表者
山田農園 鹿児島県 山田太郎
磯部農園 北海道 磯部五郎

商品情報

商品名 単価
さつまいも 150
ごぼう 200
玉ねぎ 100
かぼちゃ 130

まとめ

  • リレーショナルデータベースは今やどんなwebシステムにも利用されており、かつサービスを展開する上で重要な情報を管理する役割を持っているので、それに対する知見というのもアプリケーション側の知見と同様に重要である。
  • リレーショナルデータベースを設計する上で鍵となる手法の一つに正規化があり、だいたい第1~第3正規形までをやるのが一般的。
  • 正規化の目的は、データから冗長性を減らして管理しやすいようにすることを目的としている。

参考

yu-croco
backendエンジニアです。仕事ではAWS, Scala on DDDが中心で、たまにRaspberry Piも触ります。発信内容は個人の見解です。
koska
原価計算をテクノロジーで刷新する
https://www.koska.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away