メインコンテンツまでスキップ

Relational Database

Overview​

リレーショナルデータベースに関する情報をまとめたセクション。

  • データベース
    • データの集積を指す論理的概念
  • DBMS
    • データベースを実装したソフトウェア（MySQL、Postgresなど）

データベース設計を制するものはシステム開発を指す。
それはシステムがデータのフォーマットに合わせて作られるから
※システムに合わせてデータを作るのではない。

データベースから情報を取得する場合は、膨大なデータをただ眺めていても出てこない。
これは「どのような時期によく買っているか」「どのような種類の商品をよく買っているか」といった観点（文脈）による分析が必要になる。
したがって、情報とはデータと文脈を合成して生まれてくるものと言って良い。

ヒント

情報とは、データから、ある文脈なり観点なりに従って集約したり加工したりしたものを指すため。

長い歴史を持つ​

誕生は1969年まで遡る。
すでに登場してからデータベースのメインストリームの座になる。

データベースに求められる基本機能​

  1. データの検索と更新
  2. 同時実行制御
    更新の整合性をどのように保証するか検討し実現する。
  3. 耐障害性
    障害に強いこと。データが消えないこと。
  4. セキュリティ データベースに保存されているデータをいかにして隠すか

データベースの基本的な知識​

テーブル​

エンティティとも呼ぶことがある。

dirty write​

更新処理がぶつかった時は「後出しが勝つじゃんけん」みたいなもの。
データベースにおいてはこのような制御はデータ整合性の観点から忌避される傾向にある。

DBセッションの切り方​

  • mysql
    • DBクライアントの接続を切ってもセッションは切れない（SQLは実行されたまま）
    • そのためオーバーフローしやすい
  • postgres
    • DBクライアントの接続を切った場合セッションは切れる（実行されいるSQLがあれば終了する）

リソースの解放​

クエリが中止されると、そのクエリによって使用されていたリソース（メモリ、一時的なディスクスペースなど）も解放されることが一般的。
これにより、システムのリソースがムダに消費されることを防ぐ。 ロールバック: トランザクション内で実行されていたクエリの場合、未完了のトランザクションは自動的にロールバックされることが多いです。これにより、データベースの整合性が保たれる。

table 命名規則​

参考URL

一時的なレコードと永続化が必要なレコードを同じテーブルには入れない​

設計参考

構造が同じでも意味が違うデータは分けて管理すべきだと、私は思います。 その方がレコードの行数も少なく抑えられて、注文テーブルを引くコストが下がります。

SQL設計​

DB・テーブル設計のプラクティス
SQLアンチパターンを避けるためのチェックリスト①（DB論理設計編）

パスワード保存について​

データベースに生の（平文の）パスワードを保存するのは情報セキュリティの観点から望ましくない。
その代わりにパスワードのハッシュ値を(あるいはダイジェスト)と呼ばれる値をデータベースに保存するのが定石

アプリケーションからのDB基本利用法​

アプリケーションからDBにアクセスするためにはデータベース固有のプロトコルを用いてアクセスする必要があり、各DB向けに実装されているドライバーを使う。 一方アプリケーション側から呼び出す接続コードは、データベースドライバーの実装に依存しない汎用的なAPI

DBバックアップについて​

ダンプのファイル量が多いと時間がかかる。
そのため、その際にデータの書き換えがあるとデータの相互性が失われる。
そのためデータのバックアップを取得するときはDBを停止して行うべき

DB種類​

  • キーバリューストア memcachedやRedis

DB分割構成​

Webアプリケーションの規模が大きくなると応答速度の低下が問題になることがあり、ボトルネックとなるのはDBアクセスに関する部分。
そういった問題を解決し、複数のDBを利用するgemパッケージとしてOctopusなどがあったがRails6.0からはRails標準機能として複数DBへ対応した。

しかし、実際に複数DBとして望まれるケースで多いのは書き込みと読み込みの負荷を軽減させるために書き込む用のデータベースと、そのデータを同期した読み取り用データベースに分割する構成。

書き込み用データベースをプライマリーDB レプリケートした読み取り専用データベースをレプリカDB

上記の構成の場合、レプリカDBへ直接マイグレーションの適用やデータの書き込みは行わない（RailsではレプリカDBであると明示するため database.yml にreplicaを指定する） 実運用ではレプリカDBへの接続は参照権限のみのユーザで接続するのが望ましい。

リードレプリカ(read replica)​

リーどレプリカとは、DBの負荷分散のために作成される参照専用の複製。 データの更新＆追加は行わず検索や読みこみのみを行う。

データベースのスキーマ​

参考URL

  1. MySQLではDBとスキーマは同じと思って良い
  2. PostgreSQLではデータベースがあってその下にスキーマが存在する（デフォルトでpublicスキーマが作成される）

リレーションの読み込み方​

わかりやすい

Eager Loading 訳：熱望的なローディング or 積極的なリレーションシップ​

Eager Loadingでは、2つのテーブルのデータを1度に取得する（関連のも）
Eager Loadingを使うと、クエリの発行回数を減らすことができるので、いわゆる「N＋1問題」の回避策として用いられる。

Lazy Loading 訳：怠惰なローディング​

リレーションの際、親テーブルからデータA取得する。次に子テーブルから（便宜上、親テーブルのidを持っているもの）データAのidに関連するデータを取得する。
というように2回に分けてデータを取得すること。

N＋１問題とは？​

クエリの発行回数が過剰に増えて、サーバーサイドでタイムアウトエラーなどの不具合を起こしてしまう現象のこと

データベーススキーマ設計の完全ガイド​

参考URL

  • ブルのROW_FORMATがDynamic(Barracuda)であることを確認する
  • MySQL5.6まではデフォルトのフォーマットはCompact(Antelope)でしたが、これは1レコードあたり8KBまでしかデータを入れることができません。 テキスト型を使うと8KB制限を突破してしまうこともあるため、テーブルのフォーマットがDynamic(Barracuda)であることを確認します。
  • 整数値を入れる場合はint型かbigint型を使う
  • float型は使わない
  • 精度のトラブルに巻き込まれたくないためfloatは使いません。多くの場合、doubleかdecimalで問題ありません。 金額情報など、精度を求められる小数値にはdecimalを使う doubleも小数点以下の精度に悩まされることがあります。金額を扱う、精度が必要な計算は必ずDecimalを利用します。
  • 日付を入れる場合はDATE型を使う
  • 商品のお届け日など、日付を入れる場合はDatetimeやTimestamp型ではなくDATE型を使うようにします。DatetimeやTimestampはタイムゾーンの影響を受けるためです。
  • JSON型を使ったら負け

郵便番号や電話番号のnumberかstringかの悩み

  • 算術計算の対象ではないのでstringにする。

命名規則​

参考URL

  1. 大文字を利用しない。 テーブル名、カラム名ともに大文字を利用しない。 （DBにより大文字小文字を区別するもの、しないものなどがあるため小文字で統一を図る）

  2. 複数単語の連携はスネークケース テーブル名、カラム名ともにスネークケースを利用する。 キャメルケース、キャメルバックはNG。

DB・テーブル設計のベストプラクティス​

参考URL

テーブル 複数形 vs 単数形​

参考URL 参考URL

実際のところWEB系のフレームワークなどは複数形を好むため、複数形のテーブル名に慣れ親しんでいる人は多い。

空文字 or NULL​

参考URL

空文字だと、電話番号を持っているが電話番号がない。というようなよくわからない事情がでる。 そのため持っていないのであれば NULL にした方がいい。

データが存在しないのならば、素直にNULLを入れるべきです。

エンティティ保存方法を考える​

このエンティティの識別子の生成方法には様々な種類がありますが，大きく分けて永続化前に生成する早期生成と永続化後に生成する遅延生成の2種類に分けられます

外部キー制約​

参照されるのが 親テーブル

参照するのが 子テーブル

リレーションシップの種類​

  • 依存リレーションシップ 子テーブルの存在が親に依存している場合

  • 非依存リレーションシップ 子テーブルの存在が親に依存していない場合

覚え方​

以下のように覚えると混乱しにくい

「自分」目線で ManyToOne か OneToMany を決める

  • 自分が「多」側 → ManyToOne
  • 自分が「1」側 → OneToMany

この例だと、UserToken は多く存在できて、その所有者は User 1人なので ManyToOne(User)。

多対多​

多対多（M:N）は RDB では 中間テーブルで表現します。テーブル同士を直接相互参照させる設計は避ける。

  • 目的：関係を正規化し、削除・更新・集計を安全にする
  • 表現：A (1) --< A_B >-- (1) B（A_B が中間テーブル）

2つの実装アプローチ​

  1. ORM の ManyToMany を使う（最短）

    • ORM が 中間テーブルを自動作成 する
    • 関係に 属性が不要 なときに向く（例：単純なタグ付け）

  2. 中間エンティティ方式（推奨）

    • 中間テーブルを 明示的なエンティティ として定義する（例：fanclub_products）
    • 両端は OneToMany / ManyToOne で接続
    • 関係に 並び順・可視性・期間・メモ などの属性を持たせたいときに向く

例：投稿とタグ​

  • posts(id), tags(id)
  • post_tags(post_id, tag_id, sort_order, is_primary) ← 属性を持たせたいので 中間エンティティ として管理

選び分けの目安​

  • 属性なし／単純な関連 … ManyToMany（自動生成の中間テーブル）
  • 属性あり／将来拡張・監査が必要 … 中間エンティティ方式
ヒント

ManyToMany は手早い一方で、中間テーブルに列を追加したくなった時に移行が大変です。
最初から中間エンティティを作っておくと、後から 販売期間 や 並び順 などを安全に追加できます。

論理削除の可否​

twadaさんに語ってもらった 論理削除はアンチパターンのひとつだが割とよくある設計。

レコードを消したい。でも消したくないみたいな時に削除フラグ項目を設け、レコードをDELETEするのではなく削除フラグをUPDATEして、SELECTの条件で削除フラグがTRUEなら取得しないようにするやつです。

頻繁に復活させたり、レコード数が少ないテーブルに設けるのであれば検討の余地はありますが、基本的には論理削除を用いないほうが良いでしょう。

内部結合と外部結合の違い​

参考URL

カーディナリティ（Cardinality）​

カーディナリティとは、データベースにおける「列（カラム）に含まれる異なる値（ユニーク値）の数」のことを指します。

タイプ説明例
高カーディナリティユニークな値が多いユーザーのメールアドレス、UUIDなど
中カーディナリティ適度なユニーク値商品カテゴリ（10〜100個程度）
低カーディナリティ値の種類が少ない性別（male/female）、ステータス（on/off）など

カーディナリティが重要な理由​

  1. インデックス効率に影響する インデックスは 高カーディナリティの列で効果を発揮します（例：UUID、emailなど）。 逆に、低カーディナリティ（例：true/false）にインデックスを貼ってもあまり効果がないことが多い。
  2. ENUMやルックアップテーブルの判断材料になる 低カーディナリティなら ENUM や小さな lookup table 向き。 高カーディナリティで ENUM を使うと 変更・保守コストが跳ね上がるため不向き。

🧱 ENUMとカーディナリティの関係

あなたの引用にあったように：

「ENUMの使用には、おとり商法のような保守手法が伴う」

というのは、ENUMにたくさんの種類（≒高カーディナリティ）を持たせてしまうと： • マイグレーション時の管理が地獄になる（すべてDDL変更が必要） • 表示名、順序、説明などの制御もDB外でやらなければならない

→ 結果的に “うまく使えそうに見えて、実運用で後悔する”罠になる という比喩です（= bait-and-switch trick）

append-onlyテーブル​

「append-onlyテーブル」は、INSERT（追記）しかせず、UPDATEやDELETEを基本的に行わないテーブル のこと。

  • 新しい行を追加するだけ
  • データは時系列で積み上がる
  • 既存の行を更新しない（例外は誤登録や法的削除対応など特殊ケース）
  • 改変不可（監査証跡向け）
  • ログ・監査証跡・履歴テーブルに向いている
  • 「過去の状態を消さない・上書きしない」ことで事後検証や再処理が可能
  • 削除はアーカイブ運用で
  • 容量削減は、古いパーティションを丸ごとアーカイブ→DROPする形で行う

なぜWebhook保存でappend-onlyが推奨されるか

Webhookは外部から来る“事実”なので、後から上書きすると • 「いつ」「どんなデータが届いたか」がわからなくなる • 冪等判定や再処理の検証ができなくなる

StripeやAdyenなどの決済システムも、受信イベントは基本的にappend-onlyテーブルに積み上げて、 別の正規化テーブルに投影して業務に使います。

-- append-only: UPDATE/DELETEは原則禁止
INSERT INTO webhook_events (id, event_type, occurred_at, raw_payload)
VALUES ('uuid-1', 'INITIAL_PURCHASE', '2025-08-11 12:00:00', '{...}');

後から「間違ってたから上書き」ではなく、

-- 状態変化があれば新しい行として追加
INSERT INTO webhook_events (id, event_type, occurred_at, raw_payload)
VALUES ('uuid-2', 'CANCELLATION', '2025-08-12 09:00:00', '{...}');

のように履歴を積み上げる。

スロットリング（throttling）​

スロットリング（throttling）は、RDBのCPU/IO/同時実行/接続などの資源が上限に近づいたときに、リクエストを遅延・抑制・拒否してDBを守る制御。
狭義のレート制限（rate limiting）は入口（アプリ/ゲートウェイ）でQPSを制御すること、広義のスロットリングはDB・接続層・アプリ全体の抑制を含む。

レイヤ代表例典型症状/エラー
DBサーバ同時実行枠不足、ロック待ち、IO/CPU飽和レイテンシ急増、statement_timeout/lock_timeout、MySQL Lock wait timeout exceeded、デッドロック増
接続層max_connections超過、PgBouncer/ProxySQLのプール枠Postgres: too many connections / MySQL: ERROR 1040 (HY000)
アプリ/ゲートウェイレート制限、ワーカ並列過多429/503、ジョブ滞留、リトライ嵩み
  • 接続ストーム：プールなしor過大プールでスパイク時に新規接続乱発
  • 重いクエリの並列実行：フルスキャン・巨大ソート・膨らんだ work_mem → メモリ/IO枯渇
  • ロック競合/長トランザクション：ホット行更新、不要な SERIALIZABLE
  • バッチの時間帯衝突：本番トラフィックと重なる一括処理

基本方針（抑制の順序）

  1. 入口で間引く：レート制限・キューでDBにサージを到達させない
  2. 接続は細く長く：コネクションプールで「接続数 ≪ リクエスト数」を維持
  3. 同時実行を絞る：重い処理の並列数に上限（ワーカプール/キューの並列幅）
  4. 早めに諦める：タイムアウト・キャンセルで占有を解放
  5. 設計で避ける：適切なインデックス/パーティショニング/キャッシュ/バッチ分割

Resource​