テーブルとは？
まず、以下の二次元表は、一見テーブルのように見えますがテーブルではありません。
| 1 | 2 | 3 | 4 |
|---|---|---|---|
| アンパンマン | アンパンチ | 正義の味方 | 子どもに人気 |
| 大谷翔平 | 二刀流 | メジャーリーグ | 日本代表 |
| ドラえもん | のび太くん | ジャイアン | しずかちゃん |
一方で、以下の表はテーブルです。
| 社員ID | 社員名 | 年齢 | 部署 |
|---|---|---|---|
| 1 | 山田 | ２０ | 開発 |
| 2 | 高橋 | 30 | 総務 |
| 3 | 斉藤 | 33 | 人事 |
| 4 | 渋谷 | 44 | 営業 |
| 5 | 山田 | 20 | 開発 |
なぜ、上はテーブルではなくて、下はテーブルと言えるのか。
それは、上のテーブルはとりあえず共通点のない情報が保存されているだけだからです。
一方で、下のテーブルは「社員」という共通点を持ったレコードの集合になっています。
つまり、テーブルとは、共通点を持ったレコードの集合であるといえます。
キー
表になくても良くて、リレーショナルデータベースのテーブルにはなければならないものがいくつかあります。
そのうちの１つに「キー」があります。
別の言い方をすると、「キー」とは、「あるレコードを特定するための列の組み合わせ」です。
キーの中で特に大切なのは、以下の二つです。
- 主キー
- 外部キー
主キー
これはPrimary Keyとも呼びますが、テーブルに必ず１つは存在しなければいけません。かつ、１つしか存在しません。
主キーとは、その値を指定すれば、必ず一行のレコードを特的できるような列の組み合わせのこと。
この「一行に特定する」ことを「一意に識別する」という言い方をする。つまり、ユニークな組み合わせを特定するということ。
例えば、以下のテーブルでは、社員名、年齢、部署では一意の情報を特定できません。
仮に、同姓で年齢も部署も同じ社員がいる場合、それらの情報ではこの人！というのはわかりませんね。
もちろん、同姓ではなくても、例えば部署の開発を主キーとしようとしても、開発には山田さんが２人と高橋さんが１人なので部署も主キーになりません。
また、仮に高橋さんや山田さんが1人存在しないとしても、今後そのような可能性は否定できないので原理的には主キーとは考えられません。
一方で、社員IDは一意です。したがって、社員IDはこのテーブルの主キーとなります。
| 社員ID | 社員名 | 年齢 | 部署 |
|---|---|---|---|
| 1 | 山田 | ２０ | 開発 |
| 2 | 高橋 | 30 | 開発 |
| 3 | 斉藤 | 33 | 人事 |
| 4 | 渋谷 | 44 | 営業 |
| 5 | 山田 | 20 | 開発 |
主キーに関連する概念として「候補キー」と「スーパーキー」と呼ばれるキーもあります。
- 候補キー
主キーになりうるキーが複数存在した場合、それらの「候補」となるキーのことを言います。
しかし、前述した通り、主キーは１つのテーブルに１つなので、どちらを主キーとして選択するかを決める必要があります。
- スーパーキー
これはそれほど重要ではないので、頭の片隅に置いておけば良いかと思います。
外部キー
これはテーブルを理解する上で超超重要です。
二つのテーブル間の列同士で設定するもので、次のような形となります。
この場合、「社員」テーブルの「部署」列が「外部キー」です。
社員テーブル
| 社員ID | 社員名 | 年齢 | 部署 |
|---|---|---|---|
| 1 | 山田 | ２０ | 開発 |
| 2 | 高橋 | 30 | 総務 |
| 3 | 斉藤 | 33 | 人事 |
| 4 | 渋谷 | 44 | 営業 |
| 5 | 山田 | 20 | 開発 |
部署テーブル
| 部署 |
|---|
| 開発 |
| 人事 |
| 営業 |
| 総務 |
外部キーの役割
なぜ、社員テーブルに直接部署を保存しないのかと疑問に思うかもしれません。
それでもやりたいことは実現可能かもしれません。
しかし、テーブルは人間が使用する以上、必ず誤りが生じます。
社員テーブルの部署カラムに「総務」と保存しようとしたのに、「総む」と間違って保存したりなど、、、（まあこんな単純なミスは少ないと思いますが）。
つまり、外部キーの役割は、テーブルに対して、一種の制約を課すことです。
これを「参照整合性制約」と呼びます。
「参照するときにちゃんと整合性が取れているようにしましょうね」ということですね。（そのまんま）
例えば、社員テーブルに次のようなレコードを新たに登録することはできません。
| 社員ID | 社員名 | 年齢 | 部署 |
|---|---|---|---|
| 1 | 山田 | ２０ | 広報 |
なぜならば、部署テーブルに登録されていないからです。
登録しようとしたらSQLでエラーが出ます。
このような意味でも、外部キーを使用することが大切なんですね。
外部キーを使用しないと何でも登録できたり、間違ったデータが保存されたり、検索効率が落ちたりなど様々な問題が生じるので、可能な限り外部キーは使用する方が良いかと。
制約
参照整合性制約を上述しましたが、他にもいくつかの制約をつけることができます。
-
NOT NULL制約
「データが存在しないことを許さない」制約です。
データベースではNULLになることは問題を起こしやすいので、NULLを許さないようにできます。
ちなみに、主キーは存在しないと問題なので、暗黙でNOT NULL制約が付加されます。 -
一意制約
「ある列の組について一意性を求める」制約です。主キーにちょっと似ていますが、これは複数設定できます。 -
CHECK制約
「ある列の取りうる値の範囲を制限するための」制約です。
例えば、「パスワードは8文字〜16文字しか保存できません。」や「年齢は18歳以上しか保存できません。」みたいな感じで指定できます。
では、基本を押さえた上で正規化を確認していきましょう。
正規化とは何か？
正規化はテーブル設計、つまりテーブルを作成するときにどういったテーブルを作成するか
という場面において非常に重要な考え方です。
正規化とは、データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式です。
正規形のレベルは第五までありますが、普通は第３正規形まで理解すれば十分らしいです。
第一正規形
「１つのセルの中には１つの値しか含まない。」
以下は非正規形のテーブルです。
| 社員ID | 社員名 | 子 |
|---|---|---|
| 1 | 山田 | 真司 達夫 |
| 2 | 高橋 | |
| 3 | 斉藤 | 淳 |
この問題点は、主キーを決められないことです。
例えば、社員IDで特定しようとしても、1番の場合、子が二人いるためどちらのレコードが特定できません。
さらに、２番のように子どもを持たない社員がいる場合、NULLになってしまいます。NULLは一部であっても含んではいけません。
したがって、このようなテーブルはよろしくないわけです。
これを解決するためにはテーブルを以下のように分割すれば良いです。
そうすれば先ほどの問題は解決されますね。
社員テーブル
| 社員ID | 社員名 |
|---|---|
| 1 | 山田 |
| 2 | 高橋 |
| 3 | 斉藤 |
子どもテーブル
| 社員ID | 子 |
|---|---|
| 1 | 山田 |
| 1 | 山田 |
| 3 | 斉藤 |
以上のようにセルに複数の値が入ることは、主キーによって一意の情報を特定できないという問題を抱えるわけです。
したがって、セルに複数の値が入っている場合には、それを解決するよう考える必要があります。
これを正規形の概念として
関数従属性と呼びます。
関数従属性は以下のように表されます。
これはXが決まればYも自ずと決まることを意味します。
{ X } → { Y }
正規化とは、テーブルの全ての列が関数従属性を満たすように整理していくこととも言い換えられるかもしれません。
先ほどの例だと、以下のように関数従属性が成立していました。
{ 社員ID } → { 社員名 }
しかし、正規化する前のテーブル（１つのセルに２つ以上の情報が入っているテーブル）では、以下の関数従属性は成立していませんでしたね。
{ 社員ID } → { 子 }
これを正規化することによって、以下のように関数従属性が成立するようになりました。
{ 社員ID } → { 社員名 }
{ 社員ID } → { 子１ }
{ 社員ID } → { 子２ }
{ 社員ID } → { 子３ }
第二正規形
では、次に第二正規形を見ていきましょう。
以下のテーブルは既に第一正規形を満たしています。
しかしながら、第二正規形ではありません。
| 会社ID | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| 1 | A商事 | 000A | 加藤 | 20 | D01 | 開発 |
| 1 | A商事 | 000B | 藤本 | 34 | D02 | 人事 |
| 1 | A商事 | 001F | 三島 | 53 | D03 | 営業 |
| 2 | B商社 | 000A | 斉藤 | 43 | D03 | 営業 |
| 2 | B商社 | 009F | 田島 | 23 | D01 | 開発 |
| 2 | B商社 | 010A | 渋谷 | 54 | D04 | 総務 |
なぜかというと、会社IDと社員IDという２つの主キーが存在しているからです。
他の全ての列はこの2つの主キーに従属しています。
しかし、「会社名」だけが「会社ID」に従属しています。
{ 会社ID } → { 会社名 }
このように主キーの一部の列に対して従属する列がある場合、この関係を部分関数従属と呼びます。
一方で、主キーを構成する全ての列に従属性がある場合を完全関数従属と呼びます。
第二正規形では、この完全関数従属を満たすことを目指します。
その方法は、「テーブルの分割」です。
以下のように分割してみましょう。
これは先ほどの部分関数従属の関係にあるキー列と従属列だけ独立したテーブルにしました。
邪魔な「会社名」という列を追放したということですね。
第二正規形
| 会社ID | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| 1 | 000A | 加藤 | 20 | D01 | 開発 |
| 1 | 000B | 藤本 | 34 | D02 | 人事 |
| 1 | 001F | 三島 | 53 | D03 | 営業 |
| 2 | 000A | 斉藤 | 43 | D03 | 営業 |
| 2 | 009F | 田島 | 23 | D01 | 開発 |
| 2 | 010A | 渋谷 | 54 | D04 | 総務 |
会社テーブル
| 会社ID | 会社名 |
|---|---|
| 1 | A商事 |
| 1 | A商事 |
| 1 | A商事 |
| 2 | B商社 |
| 2 | B商社 |
| 2 | B商社 |
なんか難しそうに聞こえますが、
要は、「なんかあまり関係性がなさそうだな」と思う箇所を別テーブルにするとも言えます。
上の例だと明らかに「会社」と「社員」は別の属性なので、この２つが同じテーブルに入っているということに
違和感を持てると良いのかもしれません。
では、なぜこの第二正規形が重要なのか？そのメリットとは何なのでしょうか。
第二正規形のメリット
これを正規化する前のテーブルから考えてみましょう。
| 会社ID | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| 1 | A商事 | 000A | 加藤 | 20 | D01 | 開発 |
| 1 | A商事 | 000B | 藤本 | 34 | D02 | 人事 |
| 1 | A商事 | 001F | 三島 | 53 | D03 | 営業 |
| 2 | B商社 | 000A | 斉藤 | 43 | D03 | 営業 |
| 2 | B商社 | 009F | 田島 | 23 | D01 | 開発 |
| 2 | B商社 | 010A | 渋谷 | 54 | D04 | 総務 |
例えば、運用を開始して、新たにC建設と契約したとしましょう。
通常であればレコードを作成すればおしまいな訳ですが、
もし、社員の情報が不明な会社があった場合、どうなるでしょうか。
| 会社ID | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| 3 | C建設 | NULL | NULL | NULL | NULL | NULL |
結論、レコードを登録できません。
しかし、第二正規形にすることで、以下のように社員の情報が不明でもレコードを登録できます。
会社テーブル
| 会社ID | 会社名 |
|---|---|
| 1 | A商事 |
| 2 | B商社 |
| 3 | C建設 |
また、以下のように会社IDが1はA商事ですが、A商社のように誤ったレコードが保存される危険性もあります。
| 会社ID | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| 1 | A商社 | 000A | 加藤 | 20 | D01 | 開発 |
もし、第二正規形にしている場合は、これらを避けられる訳です。
そういった意味でも第二正規形にすることは大切です。
第三正規形
第二正規化した社員テーブルはかなり良いテーブルになりました。
しかし、まだ不都合があります。
その不都合を解消するために第三正規形を学んでいきましょう。
第二正規形
社員テーブル
| 会社ID | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| 1 | 000A | 加藤 | 20 | D01 | 開発 |
| 1 | 000B | 藤本 | 34 | D02 | 人事 |
| 1 | 001F | 三島 | 53 | D03 | 営業 |
| 2 | 000A | 斉藤 | 43 | D03 | 営業 |
| 2 | 009F | 田島 | 23 | D01 | 開発 |
| 2 | 010A | 渋谷 | 54 | D04 | 総務 |
会社テーブル
| 会社ID | 会社名 |
|---|---|
| 1 | A商事 |
| 2 | B商社 |
推移的関数従属
社員テーブルを見てみると、一見問題なさそうですが、まだ問題があります。
それは、部署コードと部署名のペアです。
現在のテーブルでは、A商事には部署は開発、人事、営業の三つだとわかります。
しかし、A商事の部署がこの三つの部署だけとは限りません。
もしかしたら、他の部署が存在するかもしれず、現在はたまたまその部署に所属する社員がいないだけかもしれません。
このように社員が一人もいない部署を現在の第二正規化した状態の社員テーブルには登録できません。
これは第二正規化の時と同じで、社員IDが主キーの一部である以上、そこをNULLのままレコードを登録できていないからです。
現在の関数従属性を考えてみると、以下の通りです。
{部署コード}→{部署名}
{会社ID、社員ID}→{部署コード}
つまり、以下のような二段階の関数従属がある訳です。
{会社ID、社員ID}→{部署コード}→{部署名}
このようにテーブル内部に存在する段階的な従属関係のことを
推移的関数従属と呼びます。
第三正規化をしてみる
では、実際に第三正規化をしてみましょう。
社員テーブル
| 会社ID | 社員ID | 社員名 | 年齢 | 部署コード |
|---|---|---|---|---|
| 1 | 000A | 加藤 | 20 | D01 |
| 1 | 000B | 藤本 | 34 | D02 |
| 1 | 001F | 三島 | 53 | D03 |
| 2 | 000A | 斉藤 | 43 | D03 |
| 2 | 009F | 田島 | 23 | D01 |
| 2 | 010A | 渋谷 | 54 | D04 |
会社テーブル
| 会社ID | 会社名 |
|---|---|
| 1 | A商事 |
| 2 | B商社 |
部署テーブル
| 部署コード | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
| D03 | 営業 |
| D04 | 総務 |
このように部署を管理するテーブルに分割することで、全てのテーブルに置いて、他の列は主キー列に従属するようになりました。
これにより、先ほど問題だった一人もいない部署を登録できないという問題は解消されます。
まとめ
ここまで、第一正規形から第三正規形について説明してきました。
ただ、そんな難しく考える必要はないのかなと個人的には思っています。
繰り返しになりますが、カラムのレベルをしっかりと見極めることが大切です。
もしレベルが違う場合は、テーブルの分割を検討しましょう。
また勉強して、間違いに気づいたら即修正します。
もし間違いがあったり、補足があればぜひコメントください。