この記事の内容
- 社員管理システムを題材として、正規化の流れを解説
- 第1~第3正規形までの正規化を実践
正規化とは？
データベース設計において、冗長性や非一貫性を極力ださないようにする方法論が正規化です。
正規化とはなんぞやを話すと長くなるので、詳しくは別の記事で解説しています。
早速やってみよう！
実際にやって覚えた方が早いので、社員管理システムを例に正規化してみます。
正規化では、要件定義がとても重要なので、簡単な条件を以下に書きます。
【社員管理システムの要件】
- 本システムは、グループ会社所属の社員情報を管理することが目的
- 部署は一般的に使われている部署に当てはめること（例：人事部、営業部）
- 昇給に２つ以上の資格が昇給に必要なため、資格情報を管理したい
非正規形はこんな状態
すべての要素をとりあえず1つにまとめただけの状態になります。
ここから正規化していきます。
非正規形
| 会社コード | 会社名 | 社員ID | 社員名 | 資格1 | 資格2 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|---|
| C100 | 鎌倉インク | S001 | かまくら太郎 | イラスト検定 | 釣り１級 | D01 | 開発 |
| C100 | 鎌倉インク | S002 | かまくらよし子 | 草むしり３級 | - | D02 | 人事 |
| C200 | 大崎大工 | S001 | 大崎よう太 | - | - | D01 | 開発 |
第1正規形をつくろう
第1正規形とは「１つのセル（フィールド）に1つの値しか含まない状態」です。
下のテーブルは社員情報を第1正規形にした状態です。主キーは会社コードと社員IDです。
非正規形から社員と資格の情報を別テーブルに分離させ、テーブルに「繰り返しグループ」を削除しました。
社員
| 会社コード | 会社名 | 社員ID | 社員名 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C100 | 鎌倉インク | S001 | かまくら太郎 | D01 | 開発 |
| C100 | 鎌倉インク | S002 | かまくらよし子 | D02 | 人事 |
| C200 | 大崎大工 | S001 | 大崎よう太 | D01 | 開発 |
社員_資格
| 社員ID | 資格 |
|---|---|
| S001 | イラスト検定 |
| S001 | 釣り１級 |
| S002 | 草むしり３級 |
１つのフィールドに1つの値を入れるなんて当たり前のことじゃん！と思うかもしれませんが、世の中には１つのフィールドに複数の値を詰め込んでいるテーブルが存在します。実際に私もやらかしたことがあります。
アンチパターン１(一つのテーブルに複数の値)
| 社員名 | 資格 |
|---|---|
| かまくら太郎 | イラスト検定、釣り１級 |
| かまくらよし子 | 草むしり３級 |
| 大崎よう太 | - |
上のテーブルはNG例です。資格に対して、かまくら太郎さんが「イラスト検定、釣り１級」の２つの値を含んでいます。これは第１正規形ではありません。アンチパターンの一種で、ジェイウォークとも呼びます。
ただし、資格情報がシステム・要件上あまり重要ではなく、自由欄として記述するのが望ましい場合は、「イラスト検定、釣り１級」のように1つのフィールドに複数の資格情報を含める方が管理しやすいといえます。
この場合、１つのフィールドに資格郡という大きい括りで１つの値が入っている状態となります。
大事なポイントは、正規化は業務ロジックによって分解するカラムが変わるということです。
アンチパターン２(同じドメインが複数存在)
| 社員名 | 資格1 | 資格2 |
|---|---|---|
| かまくら太郎 | イラスト検定 | 釣り１級 |
| かまくらよし子 | 草むしり３級 | - |
| 大崎よう太 | - | - |
これもNG例です。同じ役割を持つカラムが複数存在しています。この状態だと検索時に「資格１」「資格２」を両方問い合わせないといけない他、もし今後「資格３」が必要になった時カラムを増やしていかないといけなくなります。
第2正規形をつくろう
第2正規形は、部分関数従属を解消した状態です。
部分関数従属って？
複合主キーを持つカラム（会社コード）に対し、従属するカラム（会社名）が存在する場合、部分関数従属という関係になります。
部分関数従属が存在すると、冗長性を引き起こします。今回の場合、会社名を変更する際、社員テーブル内のすべての会社名を変更しなければなりません。
第2正規形にしてみる
第2正規形にするには、部分関数従属にあたるカラムを丸ごと別テーブルに切り出します。
会社コードは複合主キーのため、社員テーブルに残します。
社員
| 会社コード | 社員ID | 社員名 | 部署コード | 部署名 |
|---|---|---|---|---|
| C100 | S001 | かまくら太郎 | D01 | 開発 |
| C100 | S002 | かまくらよし子 | D02 | 人事 |
| C200 | S001 | 大崎よう太 | D01 | 開発 |
社員_資格
| 社員ID | 資格 |
|---|---|
| S001 | イラスト検定 |
| S001 | 釣り１級 |
| S002 | 草むしり３級 |
会社
| 会社コード | 会社名 |
|---|---|
| C100 | 鎌倉インク |
| C200 | 大崎大工 |
第3正規形をつくろう
第3正規形は、推移的関数従属を解消した状態です。
推移的関数従属って？
推移的関数従属は２段階の関数従属がある関係のことです。
社員テーブルを見ると、「会社名」と「部署名」が２段階の関数従属であることがわかります。
部署コードを例に見てみましょう。
{ 会社コード, 社員ID（複合主キー）} → { 部署コード }
部署コードは複合主キーが決まれば決定できる
{ 会社コード, 社員ID（複合主キー）} → { 部署コード } → { 部署名 }
部署名は部署コードが決めれば決定できる
第３正規形にしてみる
第3正規化にするには、推移的関数従属にあたるカラムを丸ごと別テーブルに切り出します。
社員
| 会社コード | 社員ID | 社員名 | 部署コード |
|---|---|---|---|
| C100 | S001 | かまくら太郎 | D01 |
| C100 | S002 | かまくらよし子 | D02 |
| C200 | S001 | 大崎よう太 | D01 |
社員_資格
| 社員ID | 資格 |
|---|---|
| S001 | イラスト検定 |
| S001 | 釣り１級 |
| S002 | 草むしり３級 |
会社
| 会社コード | 会社名 |
|---|---|
| C100 | 鎌倉インク |
| C200 | 大崎大工 |
部署
| 部署コード | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
まとめ
正規化の方法を社員管理システムを例に解説しました。
とにかく繰り返し正規化をすることで、だんだん身についていくのでたくさん手を動かしていきましょう！
参考文献