読書メモ：達人に学ぶDB設計　徹底指南書

【参考】

達人に学ぶDB設計　徹底指南書 kindleで買って、一旦50％くらいまでさらっと読んだ状態。ざっくりER図がかけて、正規化が何かぼんやりわかるところまで。パフォーマンスチューニングについてはこれから。

【データベース設計】

やること

  • エンティティの抽出（ どういう機能を入れたいか、またそれに必要なデータの抽出）
  • エンティティの定義
  • 正規化
  • ER図の作成

エンティティはデータを属性（attribute）という形で保持する。テーブルにおける「列」と同義。 テーブル名は、すべて複数形または複数名詞でかける。原理的に同じ種類のものが複数入っているため。

【テーブルに対する制約】

  • NOTNULL制約：この行は絶対にNULLにならないというのがわかっていればNULL禁止にする。列単位の設定。可能な限り設定したい。
  • 一意制約：ある列の組について一意性を求める。主キーでは当然この制約があるが、ほかの列にも設定可能
  • CHECK制約：列の取りうる値の範囲の制限。数字や文字列をあらかじめ決めておく。

【正規化】

正規化とそれによってつくられる正規形。 第１～第５正規形がある。数値があがると正規化のレベルが上がる。 正規化をするほどデータ整合性は高まるが、検索性能が劣化する。実用レベルでは第３正規形までを考えれば十分。 それぞれの概要

  • 第１：一つのセルには一つの値だけ
  • 第２：テーブル内に部分関数従属があるものを、完全関数従属のみのテーブルを作る。
    • 関数従属性を満たす。X列の値を決めればY列の値が１つに決まる。「会社IDと会社名」、「社員IDと社員名」が同一テーブルにある場合にはそれぞれ部分関数従属。それぞれテーブルを分ければ完全関数従属。これをやるメリット：社員がいるかどうか変わらない会社を登録できない。MULLかダミーの値を突っ込むことになる。独立していれ会社テーブルにだけ登録できる。
  • 第３：２段階の関数従属（推移的関数従属）をなくす。すべてのテーブルについて非キー列がキー列に従属するようにする。

メリットデメリット

  • メリット: 更新のミスを防ぐ。テーブルの持つ意味が明確になる。
  • デメリット：テーブルの数が増えるとSQLで結合が必要になり、パフォーマンスが低下する。

１テーブルには一つのエンティティ情報を含める。正規化の逆操作は結合。

【ER図】

スタンダードなER記は主に二つ。

  • IE（Information Engineering）表記法（通称鳥の足）　
  • IDEFIX

テーブルの表現方法はどちらも同じ。テーブル名、上に主キー、下に非主キー属性のもの。FKにはその旨をかく。外部キー：Foreign key（FK）。主キー：Primary Key（PK）。 テーブル間の関係は、基本的に１：多。多：多の場合には１：多に分解する。 あるテーブルの主キーが他のテーブルに列として含まれているかどうかというのが重要。

IE表記

  • "ー" は相手のエンティティと対応するレコード数が１（カーディナリティが１）。
  • "○" はゼロ、鳥脚は複数。二つ組み合わせて、ゼロ以上の複数を表す。

IDEFIX表記

略

関連実体

多：多は関連実体で解決する。 学生：講義　とかは多：多になってしまう。二つのテーブルを紐つけようとすると無理やり学生テーブルに「講義コード」列をいれることになる。そうすると講義未登録の学生はNOTNULL制約によりレコード追加できなくなる。 それを防ぐために間に関連実体を挟む。受講エンティティを間に挟んで１：多の関係を二つにする。「学生」「講義」のそれぞれの主キーを組み合わせたキーを主キーとする。

【パフォーマンス劣化を防ぐ】

joinは重い処理。複数（特に３つ以上）を結合すると思い。むしろ非正規化してjoinを使わない方法も。 ただし、非正規化のテーブルに対してUPDATEをかけるとものすごくレコードが多かったりする。正規化されていればテーブル一つにちょっと更新をかければよいことが多い。 データ整合性とパフォーマンスはトレードオフ。原則的には正規化する。非正規化は最後の手段。劇薬。