こんにちわ、みけです。
しょぼちむにデータモデル設計について教えてくださいの会 #syoboben - connpass
上記の勉強会にでられないので適当に言いたいことを書くシリーズの第……何段だ？これ？
本日のテーマはT字型ER図とは離れて、よく話題になる(ならない)N次正規化の話
と言いつつ、T字型ER図の話も後半に出てくる…
N次正規形
「このテーブルがN次正規形である」という分類は第三次正規化されたテーブルが正義であって、そうでないテーブルが悪であるとかそういう話ではない。
そのように指標となる数字を出しておくことによって、設計の健康状態を把握しやすくすることが目的です。
例えば高速に追加するだけが目的のテーブルにおいては、単純な文字列だけを格納できるテーブルにjsonの状態でレコードが追加されていくだけの正規化されていないテーブルであっても問題無いです。
以下ではデータベースの正規化のサンプルを表示するにあたって次のようなデータを用いていきます。
| 名前 | 性別 | 所属部署 | フロア | 保有資格 |
|---|---|---|---|---|
| いかりや 長介 | 男 | 人事部 | 3階 | - |
| 高木 ブー | 男 | サービス開発部 | 2階 | データベーススペシャリスト |
| 仲本 工事 | 男 | 受託開発部 | 1階 | 基本情報技術者 |
| 加藤 茶 | 男 | 総務部 | 3階 | エンベデッドシステムスペシャリスト |
| 志村 けん | 男 | 人事部 | 3階 | - |
| 研 ナオコ | 女 | サービス開発部 | 2階 | 基本情報技術者, ITパスポート, 応用情報技術者 |
| いかりや 太郎 | 男 | 受託開発部 | 1階 | データベーススペシャリスト |
| 高木 花子 | 女 | 総務部 | 3階 | - |
| 仲本 長介 | 男 | 人事部 | 3階 | データベーススペシャリスト |
| 加藤 ブー | 男 | サービス開発部 | 2階 | 基本情報技術者, ITパスポート, 応用情報技術者 |
1次正規化
上記のテーブルは保有資格の部分が配列状であって、複数件を含むものも、1件のみのものも、0件のものもあるので、一度1件もしくは0件になるようにデータを重複させます。これを1次正規化と言います。
データは次のような重複のあるデータになります。
| 名前 | 性別 | 所属部署 | フロア | 保有資格 |
|---|---|---|---|---|
| いかりや 長介 | 男 | 人事部 | 3階 | - |
| 高木 ブー | 男 | サービス開発部 | 2階 | データベーススペシャリスト |
| 仲本 工事 | 男 | 受託開発部 | 1階 | 基本情報技術者 |
| 加藤 茶 | 男 | 総務部 | 3階 | エンベデッドシステムスペシャリスト |
| 志村 けん | 男 | 人事部 | 3階 | - |
| 研 ナオコ | 女 | サービス開発部 | 2階 | 基本情報技術者 |
| 研 ナオコ | 女 | サービス開発部 | 2階 | ITパスポート |
| 研 ナオコ | 女 | サービス開発部 | 2階 | 応用情報技術者 |
| いかりや 太郎 | 男 | 受託開発部 | 1階 | データベーススペシャリスト |
| 高木 花子 | 女 | 総務部 | 3階 | - |
| 仲本 長介 | 男 | 人事部 | 3階 | データベーススペシャリスト |
| 加藤 ブー | 男 | サービス開発部 | 2階 | 基本情報技術者 |
| 加藤 ブー | 男 | サービス開発部 | 2階 | ITパスポート |
| 加藤 ブー | 男 | サービス開発部 | 2階 | 応用情報技術者 |
1次正規化は一つのテーブルの中に意味の同じ項目が複数ある状態を避けるために行われます。
例えば、先ほどのテーブルを次のように変更するのは1次正規化とは言いません
- 名前
- 性別
- 所属部署
- フロア
- 保有資格1
- 保有資格2
- 保有資格3
- 保有資格4
- 保有資…(以下大量に続く)
このテーブルの何が不都合かというと、保有資格をキーにして検索をおこなう場合のクエリが複雑になるためです。
WHERE some = condition AND ( 保有資格1 = ... OR 保有資格2 = ... OR ... )
2次正規化
でも、このテーブルは元々社員を扱いたいのに、保有資格のお陰で重複するレコードがあって、不便です。そのような社員を扱うのに足を引っ張るようなレコードは外のテーブルに切り分けてしまいます。
また、その際に保有資格表から資格を持っていない人の情報は捨て去ります。
社員表
| 名前 | 性別 | 所属部署 | フロア |
|---|---|---|---|
| いかりや 長介 | 男 | 人事部 | 3階 |
| 高木 ブー | 男 | サービス開発部 | 2階 |
| 仲本 工事 | 男 | 受託開発部 | 1階 |
| 加藤 茶 | 男 | 総務部 | 3階 |
| 志村 けん | 男 | 人事部 | 3階 |
| 研 ナオコ | 女 | サービス開発部 | 2階 |
| いかりや 太郎 | 男 | 受託開発部 | 1階 |
| 高木 花子 | 女 | 総務部 | 3階 |
| 仲本 長介 | 男 | 人事部 | 3階 |
| 加藤 ブー | 男 | サービス開発部 | 2階 |
社員保有資格表
| 名前 | 保有資格 |
|---|---|
| 高木 ブー | データベーススペシャリスト |
| 仲本 工事 | 基本情報技術者 |
| 加藤 茶 | エンベデッドシステムスペシャリスト |
| 研 ナオコ | 基本情報技術者 |
| 研 ナオコ | ITパスポート |
| 研 ナオコ | 応用情報技術者 |
| いかりや 太郎 | データベーススペシャリスト |
| 仲本 長介 | データベーススペシャリスト |
| 加藤 ブー | 基本情報技術者 |
| 加藤 ブー | ITパスポート |
| 加藤 ブー | 応用情報技術者 |
3次正規化
データの重複もなくなったのでめでたしめでたしと言いたいところですが、社員表の中に気持ちの悪い項目があります。
それは「フロア」という項目です。
どうやらこのドリフソリューションという会社、3階建ての社屋で、部署によってフロアが異なるようです。
| 部署 | フロア |
|---|---|
| 人事部 | 3階 |
| 総務部 | 3階 |
| サービス開発部 | 2階 |
| 受託開発部 | 1階 |
この社員表のように社員(主キー)とは異なる項目によって決定されるデータがあるようなテーブルは3次正規化がなされていません。
そこで、部署-フロアの部分を分離します。
社員表
| 名前 | 性別 | 所属部署 |
|---|---|---|
| いかりや 長介 | 男 | 人事部 |
| 高木 ブー | 男 | サービス開発部 |
| 仲本 工事 | 男 | 受託開発部 |
| 加藤 茶 | 男 | 総務部 |
| 志村 けん | 男 | 人事部 |
| 研 ナオコ | 女 | サービス開発部 |
| いかりや 太郎 | 男 | 受託開発部 |
| 高木 花子 | 女 | 総務部 |
| 仲本 長介 | 男 | 人事部 |
| 加藤 ブー | 男 | サービス開発部 |
部署表
| 部署 | フロア |
|---|---|
| 人事部 | 3階 |
| 総務部 | 3階 |
| サービス開発部 | 2階 |
| 受託開発部 | 1階 |
社員保有資格表
| 名前 | 保有資格 |
|---|---|
| 高木 ブー | データベーススペシャリスト |
| 仲本 工事 | 基本情報技術者 |
| 加藤 茶 | エンベデッドシステムスペシャリスト |
| 研 ナオコ | 基本情報技術者 |
| 研 ナオコ | ITパスポート |
| 研 ナオコ | 応用情報技術者 |
| いかりや 太郎 | データベーススペシャリスト |
| 仲本 長介 | データベーススペシャリスト |
| 加藤 ブー | 基本情報技術者 |
| 加藤 ブー | ITパスポート |
| 加藤 ブー | 応用情報技術者 |
キーの導入
これは本来もっと早い段階でやっておくべき事柄です。
特に同じ名前複数の人がいた場合、保有資格表などが混乱してしまいます。
各表に他のレコードと識別できる一意なキーを与えます。
また、他の表を参照している箇所は、キーに変更します。
社員表
| 社員ID | 名前 | 性別 | 所属部署 |
|---|---|---|---|
| 1 | いかりや 長介 | 男 | 1 |
| 2 | 高木 ブー | 男 | 3 |
| 3 | 仲本 工事 | 男 | 4 |
| 4 | 加藤 茶 | 男 | 2 |
| 5 | 志村 けん | 男 | 1 |
| 6 | 研 ナオコ | 女 | 3 |
| 7 | いかりや 太郎 | 男 | 4 |
| 8 | 高木 花子 | 女 | 2 |
| 9 | 仲本 長介 | 男 | 1 |
| 10 | 加藤 ブー | 男 | 3 |
部署表
| 部署ID | 部署 | フロア |
|---|---|---|
| 1 | 人事部 | 3階 |
| 2 | 総務部 | 3階 |
| 3 | サービス開発部 | 2階 |
| 4 | 受託開発部 | 1階 |
社員保有資格表
| 社員ID | 保有資格 |
|---|---|
| 2 | データベーススペシャリスト |
| 3 | 基本情報技術者 |
| 4 | エンベデッドシステムスペシャリスト |
| 5 | 基本情報技術者 |
| 5 | ITパスポート |
| 5 | 応用情報技術者 |
| 7 | データベーススペシャリスト |
| 9 | データベーススペシャリスト |
| 10 | 基本情報技術者 |
| 10 | ITパスポート |
| 10 | 応用情報技術者 |
こっからは何正規化かよくわからない
社員保有資格表を見ると、社員が所有している資格が書かれていますが、「データベーススペシャリスト」というのはもっと別のキーによって決定されていることがわかります。
すると、こんな表ができます。
資格表
| 資格ID | 資格名 |
|---|---|
| 1 | ITパスポート |
| 2 | 基本情報技術者 |
| 3 | 応用情報技術者 |
| 4 | データベーススペシャリスト |
| 5 | エンベデッドシステムスペシャリスト |
また、このおかげで社員保有資格表も修正できます。
社員保有資格表
| 社員ID | 資格ID |
|---|---|
| 2 | 4 |
| 3 | 2 |
| 4 | 5 |
| 5 | 2 |
| 5 | 1 |
| 5 | 3 |
| 7 | 4 |
| 9 | 4 |
| 10 | 2 |
| 10 | 1 |
| 10 | 3 |
また、以前、僕のエントリーで関連そのものもエンティティ化することを話しました。今の社員表の中にも関連が含まれているので、それも表にします。
社員表
| 社員ID | 名前 | 性別 |
|---|---|---|
| 1 | いかりや 長介 | 男 |
| 2 | 高木 ブー | 男 |
| 3 | 仲本 工事 | 男 |
| 4 | 加藤 茶 | 男 |
| 5 | 志村 けん | 男 |
| 6 | 研 ナオコ | 女 |
| 7 | いかりや 太郎 | 男 |
| 8 | 高木 花子 | 女 |
| 9 | 仲本 長介 | 男 |
| 10 | 加藤 ブー | 男 |
社員所属部署表
| 社員ID | 部署ID |
|---|---|
| 1 | 1 |
| 2 | 3 |
| 3 | 4 |
| 4 | 2 |
| 5 | 1 |
| 6 | 3 |
| 7 | 4 |
| 8 | 2 |
| 9 | 1 |
| 10 | 3 |
社員の身体的な性別も社員の情報としては有益ですが、文字列で持っておく必要性は特に感じられません。したがって、身体的な性別にもキーを与えます。
社員表
| 社員ID | 名前 |
|---|---|
| 1 | いかりや 長介 |
| 2 | 高木 ブー |
| 3 | 仲本 工事 |
| 4 | 加藤 茶 |
| 5 | 志村 けん |
| 6 | 研 ナオコ |
| 7 | いかりや 太郎 |
| 8 | 高木 花子 |
| 9 | 仲本 長介 |
| 10 | 加藤 ブー |
性別表
| 性別ID | 性別 |
|---|---|
| 0 | 女 |
| 1 | 男 |
社員性別表
| 社員ID | 性別ID |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 0 |
| 7 | 1 |
| 8 | 0 |
| 9 | 1 |
| 10 | 1 |
ただし、この性別表はあまりにも小さいテーブルですし、実装しなくてもよいかもしれません。そのような場合、ここで「性別ID」と言っているようなものは「性別コード」と呼ばれたりします。
今のところこのドリフソリューションは一つの部署は一つの階にいますが、今後一つの部署が複数の階で仕事をするようになるかもしれません。そうした場合、今の部署表のデータの持ち方ですと、複数階にいる部署のレコードが重複する可能性もあります。部署表は部署を保存しておくのが望ましいので、そのような重複は避けるべきです。したがって、重複を発生させる要因となるようなデータは別の表に分けるのが望ましいと言えます。(これはどちらかと言うと、第三正規化レベルの話……)
部署表
| 部署ID | 部署 |
|---|---|
| 1 | 人事部 |
| 2 | 総務部 |
| 3 | サービス開発部 |
| 4 | 受託開発部 |
フロア表
| フロアID | フロア |
|---|---|
| 1 | 3階 |
| 2 | 2階 |
| 3 | 1階 |
部署フロア表
| 部署ID | フロアID |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
かくしてデータはキーとキー、もしくはキーと情報の集まりになった
以上の操作を経て、ドリフソリューションの混沌としたスプレッドシートはキーとキー、もしくはキーと情報の集まりに生まれ変わりました。
最初のスプレッドシート
- 名前
- 性別
- 所属部署
- フロア
- 保有資格
現在の表
- 社員表
- 社員ID
- 名前
- 社員性別表
- 社員ID
- 性別ID
- 性別表
- 性別ID
- 性別
- 社員所属部署表
- 社員ID
- 部署ID
- 部署表
- 部署ID
- 部署名
- 部署フロア表
- 部署ID
- フロアID
- フロア表
- フロアID
- フロア
- 社員保有資格表
- 社員ID
- 資格ID
- 資格表
- 資格ID
- 資格名
システムの変更
このドリフソリューション社の社員管理システムで社員の異動経歴(いつからいつまでどの部署に所属したという情報)を参照したいという要望が発生したとします。
その場合には、社員所属部署表に変更が必要になってくるでしょう。
社員所属部署表は現在のような「社員ID」と「部署ID」で一意になる複合キーで管理することができなくなります。なぜならば、何度も同じ部署に所属する可能性があって、同じレコードが作成される可能性があるからです。
したがって、社員の所属する部署という関連そのものに一意なキーを付与する必要がありそうです。
また、経歴を参照するので配属された日付、および配属終了(まだしていないかもしれない)した日付を保有する必要が出てきます。
配属終了の日付は存在しない可能性のある項目ですので、これは別の表に分けておきたい項目です。
以上を考慮して変更を施すと次のような感じになります。
- 社員所属部署表
- 社員所属ID
- 社員ID
- 部署ID
- 所属開始年月日
- 社員部署所属終了表
- 社員所属ID
- 所属終了年月日
ここでキーだけの表、キーと情報だけの表という先ほどの理想を徹底すれば、次のような設計でも構わないでしょう。ただし、社員所属というのは日付を持つイベントであるという性格を考えるとあまりオススメは出来ません。(イベント=日付を持つデータ)
- 社員所属部署表
- 社員所属ID
- 社員ID
- 部署ID
- 社員所属部署詳細表
- 社員所属ID
- 所属開始年月日
- 社員部署所属終了表
- 社員所属ID
- 所属終了年月日
テーブル数が爆発する
さて、今までのエントリーで僕がテーブルの設計をするとテーブルの数が爆発すると言ってきました。
たかだか5つしか項目がなかったスプレッドシートが、先ほどのシステム変更を施す前の段階で9個のテーブルになりました。
また、社員の部署への所属経歴を参照したいという変更を受け入れただけで、さらにテーブルが1個増えました。
テーブル数が膨大に膨れ上がっていく様子がお分かり頂けたと思います。テーブルが多くなるのが嫌という人(今まで何人も見てきた)や、もう少し手軽にテーブルを作りたいという考えの人もいるでしょう。これまでのエントリーで僕のやり方はオススメはしませんと言ってきたのは、そのような理由があったからです。
ただ、一つ一つのテーブル自体は小さいし、テーブルを分割するようなデータベース変更はほぼ発生しないので、影響箇所は小さくなるかもしれない(影響箇所は必ず小さくなるとは言ってない)。
以上、第３正規化以上の正規化について名前とか考え方を教えてくれる人いたら教えて下さい。(ただし覚える気があるとは言ってない)
