【達人に学ぶDB設計 徹底指南書】第3章 論理設計と正規化をざっくりまとめてみた
目次
- 目次
- テーブルとは何か
- 2種類のキーについて
- どのような列をキーとすべきか
- テーブルの制約
- テーブルと列の命名規則
- 正規化とは何か
- 正規形とは
- 正規化の3つのポイント
- 正規化のメリット・デメリット
- 参考記事
テーブルとは何か
テーブルとは、共通点を持ったレコードの集合です。テーブルは行(レコード)と列(カラム)で構成されており、必ず1つの主キーを持ちます。1つのテーブルに1つだけ主キーが存在します。
2種類のキーについて
キーは、2種類存在します。それぞれの特徴を以下にまとめます。
主キー
主キーとは、その値を指定すれば、必ず1行のレコードを特定できるような列の組み合わせのことです。複数列を組み合わせて作る主キーを複合キーと呼びます。主キーはテーブルに必ず1つだけ存在します。主キーがテーブルに必ず存在するということは、テーブルには重複行が存在してはならないことが分かります。主キーを設定した列には、暗黙的に一意制約とNOT NULL制約が付加されます。
(※)
多くのDBMSでは、主キーを持たないテーブルを作ることができます。ただし、設計上、そのようなテーブルは許されません。
外部キー
外部キーとは、別のテーブルの主キーを参照するための列のことです。外部キーを設定することで、2つのテーブルに関連性を持たせることができます。
外部キーのメリット
外部キーを設定することで、外部キーが参照している主キーの列に存在しない値が入ったレコードを、外部キーを設定したテーブルに登録するのを防ぐことができます。外部キーを設定することで、外部キーを設定したテーブルに対して、一種の制約を課すことができます。この制約を参照整合性制約と呼びます。
外部キーを設定する場合の注意点
外部キーを設定するテーブルを子テーブル、外部キーから参照される主キーを持つテーブルを親テーブルとします。 親テーブルのレコードが削除する場合、子テーブルのレコードも一緒に削除するか、エラーを出す必要があります。ただし、このような厄介な問題を起こさないようにするために、基本的には子テーブルのレコードから先に更新してから、親テーブルのレコードを更新します。
(※)
親を削除する時に子も一緒に削除することを「カスケード」と呼びます。
どのような列をキーとすべきか
可変長文字列の列をキーとする場合、同じ文字列であっても、入力したユーザーによって微妙に表記が違うことがあります。そのため、データを正しく検索できない可能性があります。 そのような問題を防ぐため、キーとなる列には、コードやIDなど表記体系が決まっている固定長文字列を用いるべきです。
テーブルの制約
テーブルには「参照整合性制約」以外に、いくつかの種類の制約をつけることができます。代表的なものを以下にまとめます。
NOT NULL制約
NOT NULL制約とは、ある列にNULLを使用できないようにする制約のことです。「この列は絶対にNULLにならない」という列に対して、NOT NULL制約を付加します。NULLはデータの値がわからないときに使うもので、便利ですが、SQL上で扱うときにいろいろな問題を引き起こします。そのため、可能な限りデータはNULLにしないというのがDB設計における大方針です。テーブル定義において、列には可能な限りNOT NULL制約を付加しましょう。
(※)
主キーとなる列には、DBMS側で暗黙にNOT NULL制約が付加されています。主キーは重複が許されないため、当然NULLも許されません。
一意制約
一意制約とは、ある列の値が一意であるようにする制約のことです。代表的な例は、emailカラムに対して一意制約をつけることです。主キーがテーブルにつき一つしか設定できないのに対し、一意制約は何個でも設定できます。一意制約と主キーを比較してみて分かったことは、主キーを設定した列には、暗黙的に一意制約とNOT NULL制約が付加されているということです。
CHECK制約
CHECK制約とは、ある列の取りうる値の範囲を制限するための制約です。 ageカラムに対して、「20 ~ 60までの整数」という制約を付加したり、languageカラムに対して、「'Ruby', 'Python', 'TypeScript', 'Go'」という制約を付加したりできます。
CHECK制約も一つのテーブルに対して、何個でも設定できます。しかし、複数列にまたがった制約は、現在のところ設定できません （たとえばA列B列について「A > B」のような関係は設定できません）。
(※)
Railsのenumをモデルに定義して、enum用のカラムを作成すれば、同じようなことができます。しかし、DB側に保存される値はinteger型 or boolean型なので、モデルを見ないとどんなデータがマッピングされているのか分かりません。CHECK制約の場合、データそのものが列に入ります。どんな制約が付加されているのかはスキーマを見れば分かります。Railsではenumを使うのがメジャーだと思うので、今度Railsを書くときはCHECK制約を使ってみます。
テーブルと列の命名規則
テーブルと列の名前には、基本的には以下のルールで命名します。
- 半角のアルファベット
- 半角の数字
- アンダーバー
- 日本語はダメ
- 1文字目はアルファベット
- 同じ名前を持つテーブル、同じ名前を持つ列は存在してはならない
正規化とは何か
正規化とは、テーブル(エンティティ)を細かく分割して、テーブルのフォーマットを整理する作業のことです。テーブルを分割することで、データの冗長的な保持を解消したり、データの整合性(ズレや矛盾がない、前後が揃っている)を向上させることができます。
行き当たりばったりでテーブルを作ると、一つの情報が複数のテーブルに存在してしまったり(冗長性)、冗長なデータを持つことによって、更新処理にラグが生じて、矛盾が生じてしまうことがあります(整合性の欠如)。そのような事態を防ぐために、正規化をする必要があります。
正規形とは
正規化によってできたテーブルを正規形と呼びます。正規形にはどれくらい正規化されているかを表すレベルがあります。業務では第三正規形まで作れていれば大丈夫です。
第一正規形
第一正規形の定義は、「一つのセルには一つの値しか入らない」です。
(※)
この一つの値のことを、スカラ値と言います。
なぜ一つのセルに複数の値を入れてはダメなのか
セルに複数の値が入ることを許すと、主キーを使って各列の値を一意に決定することができません。そして、主キーの定義に反しています。故に、一つのセルに複数の値を入れることはダメです。
関数従属性とは
関数従属性とは、y = f(x)のように、xの値を決めればyの値も一つに決定する関係性のことです。 正規化を言い換えるならば、テーブルのすべての列が主キーとの関数従属性を満たすように整理することと言えます。主キーの値が決まればカラムの値が一つに決まるということです。
第二正規形
第2正規形の定義は、「テーブル内の部分関数従属を解消し、完全関数従属のみのテーブルを作る」です。 ざっくりまとめると、部分関数従属を解消すれば、第二正規形になります。具体的な解消方法は、新しいテーブルを作って分割することです。
部分関数従属とは
部分関数従属とは、複数列からなる主キーの一部の列に対して、従属する列があることです。
完全関数従属とは
完全関数従属とは、主キーを構成するすべての列に対して、従属性があることです。
第二正規形のメリット
第二正規形のメリットを以下にまとめます。
- 誤情報を含むレコードを登録できないようになります。
- 更新する場合、更新対象のレコード数が減少します。
- テーブルを分割したので、データを冗長的に保持せずに済みます。
第二正規形は、「異なるレベルの実体（エンティティ）を、きちんとテーブルとして分離する作業」という見方もできます。
無損失分解
正規化は可逆的な操作(元に戻せる)なので、正規化によって失われる情報はありません。このように情報を完全に保存したままテーブルを分割する操作のことを、無損失分解と言います。正規化して分割されたテーブルをもとに戻すためには、SQLの結合を使います。
第三正規形
第三正規形の定義は、「推移的関数従属を解消して、完全関数従属のみのテーブルを作る」です。推移的関数従属を解消するためには、第二正規形と同じで、テーブルを分割すれば良いです。
第三正規形も第二正規形と同じで、「異なるレベルの実体（エンティティ）を、きちんとテーブルとして分離する作業」という見方ができます。
推移的関数従属とは
推移的関数従属とは、テーブル内部に存在する段階的な従属関係のことです。具体的に言うと、「主キーの値が決まることである列の値が決まり、ある列の値が決まることで、別の列の値が決まる状態」のことです。
正規化の3つのポイント
1. 正規化とは更新時・登録時の不都合/不整合を排除するために行う
正規化を行うことで、ある値を持つデータを登録できない等の不都合を解消します。また、データの冗長的な保持を解消することで、データの整合性(ズレや矛盾がない、前後が揃っている)を向上させています。
2. 正規化は従属性を見抜くことで可能になる
部分関数従属、推移的関数従属を見抜いて解消することで、正規形を作ることができます。
3. 正規形はいつでも非正規形に戻せる
正規化は可逆的な操作なので、SQLの結合を使えばいつでも元に戻せます。
正規化のメリット・デメリット
正規化は原則第三正規化まで行うべきです。その上で正規化のメリット・デメリットを以下にまとめます。
メリット
- データの冗長性が排除され、更新時の不整合を防止できる。
- テーブルの持つ意味が明確になり、開発者が理解しやすい。
デメリット
- テーブルの数が増えるため、SQL文で結合を多用することになり、パフォーマンスが悪化する。
SQLの結合は非常にコストの高い操作なので、結合するテーブルのレコード数が増えれば増えるほど、処理時間がかかります。
メリットとデメリットをまとめましたが、データベースの第一目的はデータを整合的な状態で保持することであるため、第三正規化までの正規化は必ず行うべきです。
参考記事
【Ruby On Rails】DBに一意性を与えるユニーク制約（unique）の正しい記述場所 - Qiita
Rails 6.1: CHECK制約のサポートをマイグレーションに追加（翻訳）｜TechRacho by BPS株式会社
【Rails】 enumチュートリアル | Pikawaka
第136回 CHECK制約を利用してみよう | gihyo.jp
PostgreSQL - 制約 - PRIMARY KEY (主キー) と UNIQUE の違い - From the Southern Hemisphere
