DB設計基礎まとめ

DB6割・API3割・UI1割 — DB設計が一番の肝

DB Design Academy 総まとめスライド

DB設計の全体マップ — 6つのステップ

① 箱を作る → ② つなぐ → ③ 整える → ④ 描く → ⑤ 速くする → ⑥ 守る
 主キー       外部キー     正規化       ER図        インデックス   トランザクション
 (PK)         (FK)       (1NF〜3NF)   (実体関連図)                  ・制約
  1. 箱を作る: テーブルと主キー（PK: Primary Key）で「1行」を一意に定める
  2. つなぐ: 外部キー（FK: Foreign Key）でテーブル同士を安全にリンクする
  3. 整える: 正規化で重複と更新不整合をなくす
  4. 描く: ER図（Entity-Relationship図: 実体関連図）で設計を可視化する
  5. 速くする: インデックスで検索を高速化する
  6. 守る: トランザクションと制約でデータの整合性を守る

主キー（PK: Primary Key）

  • テーブルの1行を一意に特定する列
  • 満たすべき3条件
    1. 一意性: 同じ値を持つ行が2つ以上存在しない
    2. 不変性: 一度決まった値が後から変わらない
    3. NOT NULL（非NULL）: 値が空（NULL）である行が存在しない
  • 自然キー vs サロゲートキー
    • 自然キー: 業務上意味のある値をそのまま使う（例: CAS番号、ISBN）
    • サロゲートキー: 意味を持たせない連番の代理キー
  • 「氏名」「メールアドレス」は主キーに向かない典型例（重複・変更のリスク）

外部キー（FK: Foreign Key）

  • 他のテーブルの主キーを参照する列
  • 参照整合性: 参照先に存在しない値は登録できない
  • SQLiteは既定でFKチェックが無効。忘れずに実行する:
PRAGMA foreign_keys = ON;
  • 違反すると FOREIGN KEY constraint failed で拒否される
  • 基本形: 「1対多」の多側のテーブルにFK列を置く

正規化 — 第1正規形（1NF: First Normal Form）

  • ルール: 1つの列には1つの値だけを入れる（値の原子性）
  • NG例:
reagent_list TEXT  -- '硫酸,エタノール,アセトン' のようにまとめて格納
  • この状態では特定の試薬を検索・集計するのが困難
  • OK: 行を分割し、1試薬につき1行にする（発注明細のようなテーブルへ）

2NF・3NF 見分けフロー

主キーの一部だけに従属する列がある？
   はい → 2NF違反（部分関数従属）
   いいえ ↓
非キー列が、別の非キー列を経由してしか意味を持たない？
   はい → 3NF違反（推移的関数従属）
   いいえ → 3NFまでクリア
  • 例: 発注テーブルに supplier_address（仕入先住所）を直接持つ
    → 住所は supplier_id を経由してしか意味を持たない ⇒ 3NF違反
  • 仕入先が引っ越すと、全注文行を書き換える更新不整合が発生する

ER図（Entity-Relationship図: 実体関連図）IE記法チートシート

  • 1 : ちょうど1件に対応する
  • 多 : 0件以上・複数件に対応しうる
  • 多対多の関係は中間テーブルで解消する
試薬 ──── 1 : 多 ──── 使用記録
試薬 ──── 多 : 多 ──── 発注     （中間テーブル「発注明細」で解消）
発注明細 ──── 多 : 1 ──── 試薬
発注明細 ──── 多 : 1 ──── 発注

インデックス 効く/効かない 早見表

場面 効果
WHERE句での絞り込み（等価・範囲検索） 効く
絞り込み結果が全体のごく一部 効く
テーブルの行数が非常に少ない 効きにくい
列の値の種類が極端に少ない（例: 性別） 効きにくい
SELECT * で全件取得する処理 効かない
更新（UPDATE/INSERT）が非常に多い列 更新コスト増に注意

EXPLAIN QUERY PLAN で SCAN（全件走査）→SEARCH（索引利用）への変化を確認できる

トランザクションとACID特性

  • BEGIN 〜 COMMIT で複数のSQLをひとまとまりの処理にする
  • 失敗したら ROLLBACK でまとめて取り消す
  • ACID特性（原子性・一貫性・独立性・永続性）
    • 原子性: 全部成功する か 全部失敗する のどちらかになる
    • 一貫性: 制約を満たした状態だけが許される
    • 独立性: 複数の処理が同時に走っても互いに干渉しない
    • 永続性: COMMIT した内容は後から消えない

制約まとめ — 「怒られるが正義」

制約 違反時のエラー
PRIMARY KEY（主キー） UNIQUE constraint failed
NOT NULL NOT NULL constraint failed
FOREIGN KEY（外部キー） FOREIGN KEY constraint failed
CHECK CHECK constraint failed

制約に「怒られる」のは、データベースが正しく仕事をしている証拠。
エラーを消すのではなく、エラーの理由を直すのが正しい対応。

実事例① ECサイトのER図

顧客 ──1:多── 注文 ──1:多── 注文明細 ──多:1── 商品
  • 「注文」と「商品」は多対多 → 注文明細が中間テーブルとして解消する
  • 価格スナップショット: 注文明細に注文時点の単価を保存し、
    あとで商品の値上げ・値下げがあっても過去の注文金額が変わらないようにする

実事例② 図書館システムのER図

書誌 ──1:多── 蔵書（実物の1冊） ──1:多── 貸出
利用者 ──1:多── 貸出
  • 書誌（タイトル・著者などの情報）と蔵書（実物の1冊1冊）を区別する
  • 部分UNIQUEインデックス（条件付き一意制約）で
    「貸出中の本の二重貸出」をデータベース側で防止できる

実事例③ 研究データ管理のER図

サンプル ──1:多── 測定 ──1:多── 結果（縦持ち）
  • Excel1枚での管理は、前処理条件のコピペ事故や装置名の表記ゆれが起きやすい
  • 結果を「縦持ち」（1行1測定値）にすると、後からの集計・解析がしやすい
  • TG-MS（熱重量-質量分析）やHPAEC（高性能アニオン交換クロマトグラフィー）のように
    測定条件・データ点数が多い研究データでも同じ設計思想が有効

アンチパターン集

  • Excel1枚DB: 型・制約・同時アクセスの安全性のいずれも保証されない
  • カンマ区切り列: 1NF違反。特定の値の検索・集計が困難になる
  • 表記ゆれ: 「エタノール」「Ethanol」「EtOH」が別データとして扱われてしまう
  • 変更されうる値を主キーにする: メールアドレスなど、不変性を満たさない値
  • 子テーブルに親の情報を埋め込む: 3NF違反。更新のたびに整合性が崩れるリスク

設計チェックリスト（10項目）

  1. 各テーブルに一意な主キー（PK）があるか
  2. 主キーは不変な値になっているか
  3. 外部キー（FK）で参照整合性を保っているか
  4. 1つの列に1つの値だけが入っているか（1NF）
  5. 部分関数従属がないか（2NF）
  6. 推移的関数従属がないか（3NF）
  7. 多対多の関係を中間テーブルで解消したか
  8. 検索条件になる列にインデックスを張ったか
  9. 複数の更新をトランザクションでひとまとめにしたか
  10. PK・FK・NOT NULL・CHECK・UNIQUEなどの制約を活用しているか

次に学ぶこと

  • インデックスの内部構造（B木: バランスの取れた木構造のデータ構造）
  • 実行計画の詳しい読み方（実測を伴う実行計画分析など）
  • 同時実行制御の分離レベル（トランザクション分離レベル）
  • 実務のDBMS（PostgreSQL・MySQLなど）でのDDL・DML実践
  • 大規模データにおけるパーティショニング・シャーディングの考え方

お疲れさまでした

DB Design Academyに戻って、各章の復習や総合演習にもう一度挑戦できます。

サイトのトップページへ戻る