DB設計
bayamasa達人に学ぶDB設計読む
bayamasa外部キーの設定について
外部キーを設定するときに、参照元のデータが変更されたらどうするかという設定が存在する。
そもそも参照元のデータ変更を許容しない、参照元のデータが更新されたら一緒に更新するなど
cascadeは連鎖的にという意味
bayamasamysql(InnoDB)におけるfkオプションは以下
- CASCADE
子テーブルのデータも一緒に更新/削除 - SET NULL
親の更新/削除により、子テーブルのデータにはNULLが入る - RESTRICT
親テーブルはエラーを出力し、更新/削除できない - NO ACTION
RESTRICTと同じ - SET DEFAULT
現在使えない
bayamasaCASCADEは破壊的なため、設定する場合は要検討が必要
DEFAULTはRESTRICTになる
bayamasa安全のため設定ではRESTRICTとして、もし親テーブルの更新を行いたい場合には子テーブルの方から先に更新/削除を行うのがベスト
bayamasa外部キーに可変長文字列を使用しては行けない。
例えば部署名などを指定すると部署名がcharsetとコンフリクトして正しい検索ができなくなってしまう。
キーにはコードやIDなどの表記体系の定まった値を設定することが大事
bayamasaテーブル定義において可能な限りNOT NULL制約を設ける
NULLがどうしても必要なときのみNOT NULLを外す
bayamasa正規化(normalization)
正規化とはデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式です。
データに冗長性が存在すると、一回で複数のデータを更新しなければならないため、更新処理のタイムラグなどの影響によりデータの不整合が発生するケースが存在する。
正規化は5段階存在するが、3段階目まで考慮すると5段階目まで考慮されているケースが多い
bayamasa第一正規形
- 定義
一つのセルには一つの値しか含まない
例えば以下のような表は正規化されていないと言える
| ID | 社員名 | 子 |
|---|---|---|
| 01 | 田中 | 太郎, 花子 |
| 02 | 山本 | 一郎, 洋子 |
子供のカラムに複数のデータが存在する
これは第一正規化されていないと言える
bayamasaこれを解決するケースは2通り
- 子供のカラムを複数にする
| ID | 社員名 | 子1 | 子2 | 子3 |
|---|---|---|---|---|
| 01 | 田中 | 太郎 | 花子 | |
| 02 | 山本 | 一郎 | 洋子 | |
| 03 | 佐藤 |
- 子供の分行を追加する
| ID | 社員名 | 子 |
|---|---|---|
| 01 | 田中 | 太郎 |
| 01 | 田中 | 花子 |
| 02 | 山本 | 一郎 |
| 02 | 山本 | 洋子 |
| 03 | 佐藤 |
bayamasaこれらはどちらも問題がある。
1はカラムを子供の分だけ追加する必要があるため、汎用的ではない
カラムの追加はRDBにとってとても面倒であるため
2はIDが重複していることから、KEYを一意に特定できない。テーブルの主キーはNULLが入ってはいけないので、このようなテーブルを作成することはできない
bayamasaこれら両方を解決するにはテーブルを分ける必要がある
社員
| ID | 社員名 |
|---|---|
| 01 | 田中 |
| 02 | 山本 |
| 03 | 佐藤 |
扶養者
| ID | 子 |
|---|---|
| 01 | 太郎 |
| 01 | 花子 |
| 02 | 一郎 |
| 02 | 洋子 |
このようにすれば、社員テーブルの主キーは保たれる。
佐藤さんは扶養者をもっていないため、LEFTJOINなどをすることで全体の出力が可能となる
bayamasaなぜ複数データがだめかというと、主キーが各列の値を一意に決めることができないから
正規化とは関数従属性つまり Y = f(X)を成立させるための整理にすぎない
これにより、ある主キーに対する列値が複数になると関数における「入力に対して、求まる値を一意に定める」という定義から外れてしまう。
bayamasa第２正規化
部分関数従属
あるテーブルにおいて、関数従属性が完全ではない状態
ex)
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| c0001 | A商事 | 000A | 田中 | 40 | D01 | 開発 |
| c0001 | A商事 | 000B | 佐藤 | 35 | D02 | 人事 |
| c0001 | A商事 | 000C | 加藤 | 29 | D03 | 経理 |
| c0002 | B化学 | 000A | 田中 | 40 | D01 | 開発 |
| c0002 | B化学 | 000C | 山本 | 25 | D04 | 総務 |
| c0002 | B化学 | 000FI | 阿部 | 50 | D02 | 人事 |
上記のテーブルにおいて、主キーは会社コードと会社名となる。
つまり、会社コード/会社名が一意に定まる事によって他のすべての列の値が一意に定まるべきですが、「会社名」は例外で会社コードが決まることによって一意に決める事ができます。
つまり、主キーの部分的な要素である、会社コードによって会社名カラムは判断することができます。
これを部分関数従属と呼びます。
第２正規化は部分関数従属を解消することによって成立します。
この場合は２つにテーブルを分けることで解消します
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| c0001 | 000A | 田中 | 40 | D01 | 開発 |
| c0001 | 000B | 佐藤 | 35 | D02 | sales |
| c0001 | 000C | 加藤 | 29 | D03 | 経理 |
| c0002 | 000A | 田中 | 40 | D01 | 開発 |
| c0002 | 000C | 山本 | 25 | D04 | 総務 |
| c0002 | 000FI | 阿部 | 50 | D02 | 人事 |
| 会社コード | 会社名 |
|---|---|
| c0001 | A商事 |
| c0002 | B化学 |
これにより、２つのテーブルは部分関数従属ではなく、完全関数従属となりました。
bayamasaこの手法によるメリットは、運用にて現れる
例えば新しい会社C建設を登録しようとするときに、登録社員がまだ不明だとする。
このときに、社員名にNULLなどを入れて対応してもよいが、むやみにデータにNULLを入れるのは危険すぎる。
また、会社コードと社員IDがどちらも同じ属性を示しているので、もしc0001に新しく登録するD製薬などをいれてしまうなどのオペレーションが発生したとき、c0001がA商事を表すのかD製薬を表すのかがわからなくなり、障害に起因してしまう場合がある。
部分関数従属による冗長性を防ぐことでこれらを回避することができる
bayamasa第3正規化
推移的関数従属
以下の第２正規形のテーブルであるが、部署コードに注目する。
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| c0001 | 000A | 田中 | 40 | D01 | 開発 |
| c0001 | 000B | 佐藤 | 35 | D02 | sales |
| c0001 | 000C | 加藤 | 29 | D03 | 経理 |
| c0002 | 000A | 田中 | 40 | D01 | 開発 |
| c0002 | 000C | 山本 | 25 | D04 | 総務 |
| c0002 | 000FI | 阿部 | 50 | D02 | 人事 |
| 会社コード | 会社名 |
|---|---|
| c0001 | A商事 |
| c0002 | B化学 |
部署コードは部署名と関数従属性をもっており、部署コードを指定すると部署名が一意に定まるようになっています。
このようにあるテーブルに対して、内部で別の関数従属性を持っていることを推移的関数従属と呼びます。
つまり、{会社コード, 社員ID} → {部署コード} → {部署名}と２段階の関数従属を持っているということです。
bayamasaこれの何が問題かというと、例えば広報の部署を登録したいときに、まだ広報の部署の部署コードなどが設定されていないという場合があったとします。
その場合、とりあえず社員IDをNULLで登録して先にすすめる、などのオペレーションになると主キーである社員IDにNULLは登録できないのでデータが成立しません。
そのためデータを分割して使用する必要があります
bayamasaシンプルに部署名を切り出して別テーブルとすれば良いだけです。
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード |
|---|---|---|---|---|
| c0001 | 000A | 田中 | 40 | D01 |
| c0001 | 000B | 佐藤 | 35 | D02 |
| c0001 | 000C | 加藤 | 29 | D03 |
| c0002 | 000A | 田中 | 40 | D01 |
| c0002 | 000C | 山本 | 25 | D04 |
| c0002 | 000FI | 阿部 | 50 | D02 |
| 会社コード | 会社名 |
|---|---|
| c0001 | A商事 |
| c0002 | B化学 |
| 部署コード | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
| D03 | 経理 |
| D04 | 総務 |
このようにすることで部署のみ新しく追加したい場合も問題なく追加することが可能です
bayamasaER図を確認するときに、まず着目するのはあるテーブルの主キーが他のテーブルの列に含まれているかどうかです。
もし含まれているのであれば、そこが関連の部分になります。
bayamasa多対多のER図を作る場合は中間テーブルを真ん中に設ける必要がある
bayamasa正規化をするとパフォーマンスに関するトレードオフが生じる
例えば、正規化により分割したテーブルに対してSELECT文を発行する場合、大体の場合JOINが発生する。
JOINはSQLの中でも高度な処理で速度などが低下する主要因となり得る。
そのため、大規模データを正規化することでパフォーマンスが一気に低下するなどということもあり得る。
逆にUPDATEは正規化によってパフォーマンスが上がる。
レコード数が絞られるから
bayamasaパフォーマンスでいうとN+1問題も見ておく
解決方法としてはJOIN or where句で必要な要素を予め配列などでつくっておく(eager load)
bayamasa結合しないでもデータが取れるように予めカラムを用意してデータを入れておく設計もよい
例えば注文-注文明細の関連データがあるとする。注文には日時とIDだけ書いてあり、注文明細の方には各注文の詳細がかいてある。
ある依頼で注文数を算出してほしいというものがあったときに、注文データに紐づく注文明細をカウントするJOIN句を発行しないといけないとする。この場合大規模データになると、パフォーマンスが
落ちる可能性がある。そのため注文テーブルに新たに注文数というカラムを用意することでJOINを設けないSELECT文で依頼内容を達成可能なので、このようなカラムを追加することで、パフォーマンスを落とさずに依頼を達成することが可能である。
bayamasaデータベースの設計においてuserとuser profileを分けるべき場合の参考
bayamasatimestamp型はmax32bitなので、2038年問題に引っかかる
なので時間を表す型はdatetime型を推奨している