【参考】
達人に学ぶ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をかけるとものすごくレコードが多かったりする。正規化されていればテーブル一つにちょっと更新をかければよいことが多い。 データ整合性とパフォーマンスはトレードオフ。原則的には正規化する。非正規化は最後の手段。劇薬。