Zenn
Open11

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

DB
正規化
データベース設計
おたきおたき

1章：データベースを制するものはシステムを制する

メモ

  • データの定義
    • ある形式（フォーマット）に揃えられた事実のこと
  • 情報の定義
    • データから特定の文脈・観点に従って集約したり加工したりしたもの
    • つまりデータと文脈を合成して生まれる。
  • システムとは、ユーザがデータ登録→データを抽出→情報を作り出す、というサイクルの中にある
  • データベースの代表的なモデル
    • リレーショナルデータベース（RDB）
      • 設計技法に正規化を用いる
    • オブジェクト指向データベース（OODB）
    • XMLデータベース（XMLDB）
      • RDBが苦手な階層構造のデータの扱いが得意
    • キー・バリュー型ストア（KVS）
      • 単純なデータ問い合わせを高速化する目的で使用
      • 対象データを高速処理する必要のあるWebサービスで多用される
    • 階層型データベース
      • RDBの普及により、一般的には使われなくなった
  • データベースのモデルが異なるとデータフォーマットが異なる。モデルが異なると設計技法も異なる
  • DBMSが異なったとしても、設計技法は影響を受けない
  • システム開発の設計工程
    1. 要件定義
    2. 設計 ※さらに細分化される
    3. 開発（実装）
    4. テスト
  • システム開発モデル
    • ウォーターフォールモデル
      • 要件定義〜テストまで段階を踏んでシステムを作る。手戻りが効かない精密さが要求される。
    • プロトタイピングモデル
      • 小さな単位で要件定義〜テストを繰り返す。
      • 早い段階から顧客にシステムイメージを共有できるので、要件定義の取りこぼしや意思疎通の齟齬を防げるメリットがある。
  • ソフトウェア開発においては、データ中心アプローチ（DOA）が主流
    • プログラムよりも先にデータ設計から始める方法論。
  • 3層スキーマモデル
    データベース設計は下記3つのレベルに基づいて行われる
    1. 外部スキーマ　→　ユーザーから見たデータベース（画面やデータ）
    2. 概念スキーマ　→　開発者から見たデータベース（テーブル定義、データ同士の関係）
    3. 内部スキーマ　→　DBMSから見たデータベース（テーブル、インデックス等の物理的定義）
  • 概念スキーマは、内部・外部スキーマ間にあることでデータの独立性を保証させている。
おたきおたき

2章：論理設計と物理設計

メモ

2-1

  • データベース設計は原則として、論理設計が物理設計より先立つ
  • 論理設計の4ステップ
    1. エンティティの抽出
    2. エンティティの定義
    3. 正規化
    4. ER図作成
おたきおたき

3章：論理設計と正規化 - なぜテーブルは分割する必要があるのか？

メモ

  • 外部キーが設定されている場合、データ削除は子から順に操作すべき
  • キーにはコードやIDなど表記体系が決まった固定長文字列を用いる
  • 正規形
    • データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するデータ形式
  • なぜ一つのセルに複数の値を入れることが、RDBでは認められていないのか？
    • 主キーの性質上、各列の値を一意に決定できないといけないから。そのため各列はスカラ値で構成する必要あり。
  • 第2正規形は、テーブル内の部分関数従属を解消して、完全関数従属のみのテーブルを作ること。
    • 部分関数従属
      • 主キーの一部の列に対して従属する列がある関係
    • 完全関数従属
      • 主キーを構成するすべての列に従属性がある関係
おたきおたき

4章：ER図 - 複数のテーブル関係を表現する

メモ

  • ER図の書き方には2種類ある
    • IE表記法　→　「鳥の足」とも呼ばれる。直感的にテーブル関連を理解できる。
    • IDEF1X　→　米国で規格化された表記法。多機能のため難しい。
  • ER図において、相手エンティティと対応するレコード数を「カーディナリティ」と呼ぶ
おたきおたき

5章：論理設計とパフォーマンス - 正規化の欠点と非正規化

メモ

  • 正規化と検索SQLのパフォーマンスは強いトレードオフの関係
    • 正規化の次数が低いほど、パフォーマンスは良いがデータ整合性は低い
    • 正規化すればするほど、パフォーマンスが低下するがデータ整合性は高い
  • 非正規化はあくまでも最後の手段である
    • 十分に正規化された設計を諦めていいのは、パフォーマンス向上のすべての戦略が要件を満たさない場合だけである
  • サマリデータ（結合を伴う集計結果など）を冗長に保持すると、正規形には違反するが検索の高速化できる
おたきおたき

6章：データベースとパフォーマンス

この章むずいいい

メモ

  • インデックス
    • SQLチューニングの手段として非常に多用される
  • 統計情報
    • SQLのデータへのアクセスパスを決める最大の要因となる
    • DBMS側で経路選択を任せて効率的なパスを決める、コストベースというアーキテクチャが主流
おたきおたき

7章：論理設計とバッドノウハウ

メモ

  • 非スカラ値の配列型は極力使わない
    • 配列型の値を含むテーブルを作成できる機能がサポートされている
    • もし採用する場合はDBに接続するアプリケーションやミドルウェアとの整合性も考慮必要ある
  • テーブルの列は可能な限り分割して保存するほうがよい
    • 名前を名字と氏名にわける、メールアドレス(hoge@test.com)をhoge、test.comにわけるなど。
      ただし意味は崩してはいけない
    • あとから列の値を結合するのは楽だが、分割するのは面倒
  • 列に複数の意味を持たせない
    • 別の情報を格納したいならば、新規で列を作成せよ
  • テーブルの多さから単一参照テーブルを作成してはいけない
    • あらゆるタイプのマスタテーブルを一つのテーブルにまとめるのは悪手である
    • テーブルにポリモーフィズムは必要なし
  • テーブル分割（水平分割・垂直分割）は原則禁止
    • 分割する意味的な理由がないため
    • テーブルを将来的に増やしていく必要があり、拡張性に乏しい
  • 集約によるテーブル分割
    • 垂直分割の代替案に相当する
    • オリジナルテーブルとのデータ同期問題があるため、データ更新頻度には注意する
      • 1日数回程度のバッチ更新で機能、要件的に問題ないか検討する
  • キーには固定長文字列の「コード」列が理想
    • 同じデータを意味するキーは同じデータ型にすべし
  • バッドノウハウを採用してはいけない理由3点
    • 可読性が悪いと、エンジニアにとって分かりづらい
    • 開発の後工程での設計変更が難しい
    • システム開発において最も重要なのは、コーディングではなくデータ設計である
おたきおたき

8章：論理設計とグレーノウハウ

メモ

論理設計においてバッドノウハウまでいかないが、際どいグレーゾーンがある。
利点と欠点があるので使用時には慎重に判断する

  • 代理キーの使用について

    • 意図的につくった一意なキーみたいな感じか。
    • あると便利だが、極力使用は避けて自然キーによる解決を図るべき。
      論理的に不要なキーのため、論理モデルをわかりにくくしている。
    • タイムスタンプやインターバルを使えば、自然キーで主キーを解決できる場合がある
    • 代理キーをオートナンバリング機能で以下のやり方で実現できる
      • データベース機能での実装（シーケンスオブジェクト、ID列）
      • アプリケーション側での実装（採番テーブル）→ おすすめしない（車輪の再発明である）

  • テーブル設計において、テーブルに主キーを付けることはRDBにおいて重要な役割をもつ。

  • 列持ちテーブルの使用

    • 列持ちテーブルの特徴
      • 利点
        • シンプルな設計になる（正規化、スカラ値などを知らなくてもいい）
        • 入出力のフォーマットを合わせやすい
      • 欠点
        • あとから列の増減するのが難しい。拡張性が乏しい
        • 値がない列の場合、NULLを使う必要がある
    • 行持ちと列持ちの相互変換はSQL文で可能なので、最初は行持ちで保持しておき必要があれば列持ちテーブルを作るのが良い。

  • アドホック（場当たり的）な集計キー

    • アドホックな集計キーは、コード体系が短いスパンで変更したり、肥大化したりでパフォーマンスが悪くなる可能性あり。解決策は以下。
      1. キーを別テーブルで管理する（例：都道府県コードと地方コードの変換テーブル）
      2. キーを追加した専用のビューを作成する（オリジナルテーブルには手を加えない）
      3. GROUP BY句の中で、CASE式を使って意図的にアドホックキーを作る → ハッキーだな。

  • 多段ビュー

    • ビューのSQL文によるアクセスでは、常に裏側に基底テーブルの存在があることを意識する必要あり。
    • ビューの使用は原則として、1段にとどめて置くようにする
    • 「KISS」の原則は、過度に複雑なつくりはシステムをだめにする、という思想。
      • Keep It Simple, Stupid.
    • 一方、マテリアライズド・ビューは実データを保持するため、多段ビューの弊害は受けない

  • データクレンジングの重要性

    • データクレンジング
      • これまで業務で利用されていたデータをデータベースに登録できる状態にすること
      • データベースの論理設計に着手する前に、入力データのフォーマットが適切か調査して、必要に応じてデータ変換やフォーマット変換が必要がある。
    • 具体的なデータクレンジング項目
      • 一意となるキーの特定（）
      • 名寄せ（=人名や企業名の表記揺れを解消して名称を統一すること）
        • 名寄せをサボるとダブルマスタの状態が生まれるので、ちゃんと行って一元化すべき

  • 一時テーブルについて

    • 一時的なデータを保存するためのテーブル
    • 決められたスパン（トランザクションやセッション）でデータ保持して、終わると削除される
    • SQL Serverの場合、「#」「##」をテーブル名の先頭に付与する
      https://learn.microsoft.com/ja-jp/azure/synapse-analytics/sql/develop-tables-temporary
    • 欠点
      • 統計情報の収集タイミングが難しい
      • 物理的なI/O分散が難しい

  • バックアップとレプリケーション

    • レプリケーションは、全く同じデータを持つデータベースを2個用意して、常に一方の更新差分をもう一方に反映させることで同期を取る仕組み
    • レプリケーションのステップ
      1. ユーザーがマスタDBに更新SQLを実行
      2. マスタDBでトランザクションログが生成される
      3. トランザクションログがマスタDB→スレーブDBに転送される
      4. スレーブDBでトランザクションログが適用される
    • メリット
      • 障害時に復旧に要する時間が短いこと（マスタDBがダウンしたらスレーブに繋ぎ変える）
    • デメリット
      • コストがかかる（DBが最低2個必要）
おたきおたき

9章：一歩進んだ論理設計 - SQLで木構造を扱う

内容が難しすぎて、まだ読むのは早い印象あり.

メモ