DB設計の基礎

はじめに

本記事では5年目のアプリケーションエンジニアである私が、ミック著「達人に学ぶDB設計徹底指南書」を読んで、業務に活用できそうだと感じた要点をまとめていきます。（私の業務上、論理設計にフォーカスしています）
普段の業務において、DB設計を行うことが増えてきたアプリケーション開発者やDBエンジニアの方に読んでいただけたらと思います。

どんな本？

リレーショナルデータベース(RDB)における設計についての書籍です。
主に論理設計、物理設計にフォーカスした内容となっており、特定のDBMSには限定せず活用できるようなノウハウがまとめられています。
基本的な用語の意味から解説してくれるので、多少DBに触れたことがある方であれば1年目からおすすめできる1冊です。

• 達人に学ぶDB設計徹底指南書

要点PickUp!

DB設計はシステムの品質を大きく左右する

現代のシステムにおいて、大半のデータはDBに保持されており、ソフトウェアというのはこのデータを活用する機構であるため、プログラムに先立ってDB設計が必要である。
ソフトウェア開発には様々な考え方があるが、上記のアプローチが現在の主流であり、「DOA（データ中心アプローチ）」と呼ばれている。

DOAの反対の考え方である「POA（プロセス中心アプローチ）」というものが、かつてのシステム開発の主流だったようですが、現在では時代遅れであるとされています。

3層スキーマモデル

スキーマ(DBの枠組み)を3つのレベルに区分する重要な概念。
以下の3つに区分されている。

外部スキーマ

• ユーザーから見たデータベースの姿
• 画面のユーザーインターフェースや入力データなど

概念スキーマ

• 開発者から見たデータベースの姿
• 保持するデータの要素および、データ同士の関係を記述する
• 概念スキーマの設計を論理設計と呼ぶ

内部スキーマ

• DBMSから見たデータベースの姿
• 概念スキーマで定義された論理データモデルをどのようにDBMS内部に格納するかを定義する
• 論理設計と対比して物理設計と呼ぶ

概念スキーマは外部スキーマと内部スキーマの独立性を高める役割を持つ。（=柔軟性を高める）

本記事では主に概念スキーマを作るための論理設計について記載します

論理設計における制約

参照整合性制約

• 外部キーを定義した際に発生する制約
• 外部キーを持つテーブルを子、当該カラムを主キーとするテーブルを親とし、親テーブルに存在しないデータが子テーブルに存在することを禁止する制約のこと

外部キーを持つデータを削除する場合は子データから削除しよう

NOT NULL制約

• NULLを禁止する制約

無用なリスクを避けるため、可能な限り列にはNOT NULL制約を付与しよう。

一意制約

• 一意性を求める制約
• 主キーと違い、複数設定が可能

CHECK制約

• 取りうる値の範囲を制限する制約
• 数値に限らず、「いずれかの文字列」のような指定も可能

今のところ、複数列にまたがった制約は設定不可

正規化

• データの冗長性を排除し、一貫性と効率性を保持するためのデータ形式にすること
• 一般的に第１正規形から第５正規形までが存在する
• 基本的に業務で使用されるのは第３正規形まで

第１正規形

• 1つのセルに1つだけの値（スカラ値）が含まれている状態

複数の値が入ったセルを分割する際、「列」ではなく「行」として分割すること

第２正規形

• 完全関数従属のみの状態のこと
• 完全関数従属とは
  • 主キーを構成するすべての列に従属性があること
  • 複数列から主キーがなる場合は、その他の列はそのすべてに対して同時に従属する必要がある

第３正規形

• 推移的関数従属を解消した状態のこと
  • 推移的関数従属とは
    • 主キー以外の列に従属性があること

第4、第5の正規形については高次正規形と呼ばれ、基本的に業務で使用されることは少ないので本記事では割愛します。

正規化における要点

• 正規化は更新時の不整合を排除するために行う
• 正規化は従属性を排除していく作業
  • 業務ロジックの理解が必要！
• 正規化は結合（JOIN）を行うことでいつでも非正規形に戻せる
• 正規化は常にするべきか？
  • 第3正規形までは原則として行う
  • 関連エンティティが存在する場合は関連するエンティティ同士が1対1に対応するように注意する

関連エンティティとは、「学生」,「講義」のような多対多の関係性を持つエンティティが存在する場合に作成される「受講（学生-講義）」のようなエンティティのこと。
これを作成することで、「学生」,「講義」が結合できないという問題を解消することができます。

正規化のメリデメ

メリット

• 冗長性の排除による更新時の不整合防止
• データの持つ意味が明確になることにより理解しやすくなる

デメリット

• テーブルの数が増えていくため、SQLで結合を多用することになりパフォーマンスが悪化する

join句は特別重めなSQLであることに注意が必要。

パフォーマンス

• 上述の通り、正規化には「結合によるパフォーマンスの低下」というデメリットがある。本書で紹介されていた、それに対する打ち手を以下に挙げる

非正規化

• 第3正規形を第2正規形に戻すことで、SQLにおける結合を不要にすることができ、検索処理における性能の改善が見込める
• 一方で冗長性が復活してしまうため、更新処理の際の性能が改悪する
• つまり、正規化の次数（データ整合性）と検索パフォーマンスはトレードオフの関係にある

RDBMSを開発したクリス・デイト氏は「非正規化はあくまで最後の手段である」と述べています。
十分に正規化された設計を諦めるのは、他全ての戦略が要件を満たさない場合のみとしましょう。

インデックス

• DBMSはSQLを受け取ると統計情報をもとに、最適な経路を判断してデータを探す
• このとき、インデックスをたどることでテーブルの特定レコードを狙い撃ちでアクセスできるため、パフォーマンスの向上に繋がる
• インデックスには以下のメリットがある
  • アプリのコード、テーブルのデータに影響を与えない
  • 性能改善の効果が大きい
• インデックスは複数種類あり、代表的なものは「B-treeインデックス」である

B-treeインデックス

• 作成対象
  • 大規模テーブル
  • カーディナリティ（取りうる値の範囲、例えば「性別」であれば2~3といえる）が高い列
  • SQL文にてWHERE句の選択条件、または結合条件となる列

レコード数が１万件以下のテーブルの場合やカーディナリティが20以下の場合、ほぼ効果が無い

• ※注意事項
  • 主キー及び一意制約の列には作成不要
  • B-treeインデックスは更新性能を劣化させるので、無駄なインデックスは張らない
  • 定期的にメンテナンスが必要
    • インデックスの再構築により改善する

まとめ、感想

本書は非常に上手くまとめられてはいるものの、テーマ上どうしてもボリュームのある本だったので、本記事では論理設計にのみに限定して要点を抜粋してみました。
時間があれば、物理設計周りや紹介されていたバッドノウハウなどをまとめた記事を別で作成してみようと思います。

本記事の内容について、ご指摘・ご質問などありましたらコメントをいただけるととても嬉しいです。

以上、ご覧いただきありがとうございましたmm