t-suzuki 雑記ノート

主に技術の話

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

【参考】

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

【データベース設計】

やること

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

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

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

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

【正規化】

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

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

メリットデメリット

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

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

【ER図】

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

  • IE(Information Engineering)表記法(通称鳥の足) 
  • IDEFIX

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

IE表記

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

IDEFIX表記

関連実体

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

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

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