オブジェクト指向設計ならわかる人に贈るRDBテーブル設計

テーブル定義

オブジェクト指向設計におけるクラス定義と同様に考える

• メンバフィールドのみを持つクラスとして考える
• Class, Enum, Dict の概念は全てテーブル化する
• List 型の包含関係が発生する場合は独特の思考が必要になる (後述)

正規化

オブジェクト指向設計として妥当な定義をしていれば勝手に正規化されている

包含関係の表現

1対1

オブジェクト指向設計と同様

多対1

包含関係の表現がオブジェクト指向設計とは異なる

• どのインスタンス（xxxx_id）に包含されるか の情報を持たせる

多対多… に進む前に

• 多対1で例示したテーブル設計では Userが複数のGroupに属することができない

オブジェクト指向設計的には例と同じクラス設計であっても、
あるUserインスタンスを複数のGroupインスタンスに包含させることができる
そのためオブジェクト指向設計を起点にテーブル設計をしていると失念しやすい

多対多

テーブル設計においてはマッピングテーブルを設けることで多対多の包含を実現する

• 多対1の包含関係でも将来的な拡張性のために上記設計の採用が検討候補に挙がる
• User, Group 双方のテーブルから包含関係に関するカラムを排除できるのも利点
• 1対1の包含関係の場合は冗長になりパフォーマンス低下まであるので基本不採用

カラム設計

設けるべきカラムのテンプレート

エンティティテーブル

• ID
  • プライマリキー用途（後述）
  • id ではなく foobar_id というネーミングにすること
    • 外部キーとして foreign_id カラムなどを追加する場合にネーミングの一貫を保つ
• Name
  • システムのユーザ視点における一意識別子
    • 意味論的に UNIQUE 属性を付与しておくことを推奨
  • 例えばUserテーブルにおける user_name はログインネームが該当する
• Label
  • UIなどに表示する文字列
  • 例えばUserテーブルにおける user_label はニックネームが該当する
• 日付系
  • 特定の期間で抽出したくなる場面は頻出であるため

マッピングテーブル

• ID
• マッピング対象の外部キー ×2
  • 重複するマッピングを防ぐために複合ユニークキーとする
• 日付系

トランザクションテーブル

処理結果などを蓄積するためのテーブル

例えば操作履歴にユーザ情報を併せて記録する場合、ユーザの削除後も有効なレコードでなければならない
このように外部キーによる参照をするべきではないケースが存在するので注意

• ID
• 諸々の情報
  • 例えばユーザ情報を記録する場合、ユーザ削除後のトレースを考慮して以下を残す
    • user_id (※外部キー属性を付与しない)
    • user_name
• 日付系

プライマリキー

各テーブルに設けるプライマリキー（主キー）の設定については以下の方針を推奨
どちらもパフォーマンスへの影響の観点から

• 数値型または固定長文字列型とする
• 複合プライマリキーは採用しない
  • 上でマッピングテーブルにIDカラムを設けたのはこのため

数値型

AUTO_INCREMENT 属性の付与により一意な連番IDを自動生成する

ただしID情報をシステム外部とやり取りする場合はスクレイピング耐性が悪化する
具体的には user_id = 123 であるUser情報に以下のようなURLでアクセスできる場合

• https://www.my-website.com/user/123

これが AUTO_INCREMENT によって生成されたIDである場合、URLの 123 の部分を変更することで色々とできてしまう

なので外部からのアクセスを想定しないテーブル（マッピングなど）での採用に留めるべき

固定長文字列型

UUIDからハイフンを削除した32byteで一意なIDを生成する
上記のようにシステム外部にID情報が露出するケースでも耐性に影響が無い

ちなみにPostgreSQLにはUUID型が存在するが、MySQLには存在しない
移植性を考えるならPostgreSQLであっても型定義は CHAR(32) を推奨