Eloquent 前提の MySQL データベース設計

大前提

• Laravel の Eloquent リレーションを前提 とする。
• 原則として DB ビルダー は使わない。つまり、SQL クエリ（が想像できるコード）は書かない。
• たとえ DB ビルダーを使ったとしても as でテーブル名の省略しない（可読性優先）。
• Eloquent で Join 禁止。子テーブルの値を参照するならリレーション、検索判定するなら whereHas がある。
• 原則として select も書かない。
  • select で絞るほど多数のカラムがあようなら、「横持ち」から「縦持ち」に設計しなおすべき。

ゆえに、生の SQL クエリ（DBビルダー含む）を書く前提である巷のベストプラクティスとは、真っ向から反する命名規則となる。

Eloquent 前提の命名規則

テーブル、カラム共通

• テーブル名、カラム名ともに小文字でスネークケース（foo_bar）
• ローマ字禁止。英語でがんばる。
• 省略語は禁止。
  • flag を flg、code をcd などすると、混在してバグを生みます。

テーブル名

• テーブルは原則複数形。
• 複数単語の合成語の複数形に自信がないなら単数形（影で笑われる よりもマシ）
  • 複数でも単数でも Eloquent モデルの $table は必ず書く。
• 接頭辞は原則つけない。
• 設計規模が大きすぎるときは接頭辞を検討する。例として：
  • m_ ：変更がすくないマスター系テーブル
  • t_ ：ユーザーによる変更される（トランザクション系）
  • l_ ：ログテーブル

カラム名

• カラム名にはテーブル名を含めない。
  • product_name や user_name ではなく、name とする。
  • リレーション前提なので $product->name が美しい。
  • join は使わないし、原則 select も書かないので name を区別する必要はない。
• プライマリーは必ず id。
• _id は他テーブルへのリレーションキー。
  • 論理名が「なんとかID」であっても、物理名は _key や _code、_no 等とする。
  • DB レベルの外部キー制約は無理にふる必要はないが、インデックスはふる。
• Boolean 型は is_、has_、use_等（_flag 禁止）
• Data 型は _on。
• Datetime 型は _at（Timestamp型は 2038 年問題があるので避ける）

原則は NOT NULL

• すべてのカラムは 原則 NOT NULL とする。
• nullable として良いのは……
  • 必須ではないユニークカラム。
  • text カラム（DEFAULT を持てないので）
  • 初期値を持たない日付カラム。
• nullable でないなら、必ず DEFAULT を定義 する。
  • 未定義の文字列なら ""、数値なら 0 を指定。
  • カラムにより適切な初期値があるならそれ。

未入力という概念がある数値についても、0 を表示しないという処理を laravel でできるので、DB 的には NOT NULL default 0 のほうが扱いやすい。0 を含めた入力値の平均を求めるなら nullable を検討する（想像できないけど）。

NULL を許可しない理由を一言でいうと「MySQL だから」。MySQL ドキュメント「8.4.1 データサイズの最適化」から……

可能な場合は、カラムを NOT NULL として宣言します。 それにより、インデックスを適切に使用し、各値が NULL であるかどうかをテストするためのオーバーヘッドがなくなることで、SQL の操作が速くなります。 カラムあたり 1 ビットでいくらかのストレージ領域も節約します。 テーブルで実際に NULL 値が必要な場合、それらを使用します。 単にすべてのカラムで NULL 値を許可するデフォルトの設定を避けます。

Oracle では空文字列を NULL として扱う ので、Oracle 起源のシステムは NULL が原則となっている。MySQL 開発者は Oracle マスターに引け目があるのか、右にならって NULL を並べてしまうのかもしれないが、デメリットしかない。「NULL撲滅委員会」で検索すると、その主張や論争を知ることができる。なお、PostgreSQL でも同様 のはず。

Laravel には、空文字列を NULL に置換するミドルウェア ConvertEmptyStringsToNull がデフォルトで組み込まれているので忘れずに削除する。……Laravel のマイグレーションのデフォルトは NOT NULL なのに、一貫性がないとないと感じるのは自分だけ？

なお、Quasar という Vue フレームワークを使ったとき、Quasar が空文字を NULL に置き換えていた。メンバーが API を半分使ってしまった後で気づき、大いに困った。

また、必ず default を定義 するのは、まずオブジェクトとしての空レコードを作成する……というコードが必要になる可能性もあるので。カラム指定がなくてもレコード作成が成功する 前提を保つ。

論理削除は使わない

• 論理削除を導入するとユニークを担保できない。
• 削除に見えるもものの多くはステータス変更である。
• 他テーブルとリレーションが発生する前のゴミレコードは物理削除してよい/したい。
• 証跡が必要なら audits ログで対応する。

マイグレーション

開発初期

• 開発初期はテーブルごとに新規作成マイグレーションを作成する。
• 開発中の構造修正もこの新規作成マイグレーションを書き直す。
• データシーダーを作成し、構造変更してもテストデータをリフレッシュできるようにする。

クライアント環境へのデプロイ後

• php artisan schema:dump でデータダンプを取得し、既存マイグレーションは database/migrations_backup/ へ移動する（この作業はプロジェクトマネージャが実施）
• 以後の構造修正は、修正単位ごとにテーブル更新のマイグレーションを作成し、１テーブル１マイグレーションにはこだわらない。
• マスターデータ内容に追加や修正が必要な場合は、データシーダーを更新するだけでなく、マイグレーション内に insert や update を記述する。
• ただし、migrate:fresh --seed したときはシーダーよりマイグレーションが先に走るので、insert が二重にならないような配慮が必要。

// migrate:refresh --seed ではないときに実行
if (!in_array('--seed', $_SERVER['argv'], true)) {

マイグレーションのルール

• default をつけ忘れない。
• すべてのカラムに comment を定義する。
• 同様にテーブルそのものにも comment を定義する。
• リレーションキーは id の型に合わせて unsignedBigInteger とし、インデックスをつける。
• timestamps() は使用せず、datetime で定義する。
• コンポジット（多対多対応の中間）テーブルは複合プライマリーを使う。
  • ロードバランサーでクラスターを組んでいるとき、プライマリーではない複合ユニークキーでは更新エラーとなる。
• 複合プライマリーは逆引きには対応できないので、副キー側に独立したインデックスをふる。

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name')->commen('ユーザー名');
    $table->string('email')->unique()->comment('メールアドレス');
    $table->integer('company_id')->default(0)->index()->comment('会社');
    $table->datetime('email_verified_at')->nullable()->comment('メール確認日時');
    $table->string('password')->comment('パスワード');
    $table->rememberToken()->comment('リメンバートークン');
    $table->datetime('created_at')->nullable();
    $table->datetime('updated_at')->nullable();
});
DB::statement("ALTER TABLE users COMMENT 'ユーザー';");

// コンポジットテーブル
Schema::create('project_user', function (Blueprint $table) {
    $table->integer('project_id')->comment('プロジェクト');
    $table->integer('user_id')->comment('ユーザー');
    $table->primary(['project_id', 'user_id']);     // 複合プライマリ
    $table->index('user_id');                       // 副キー側のインデックス
});
DB::statement("ALTER TABLE project_user COMMENT 'プロジェクト・ユーザー';");

ファクトリー

• モデルごとに必ずファクトリーを書く。
• ファクトリーは、テーブル（モデル）ごとに１レコードのダミーを定義するもので、そう難しく考えることはない。
• テーブル定義に DEFAULT が指定されているなら、ファクトリーは空でもレコード作成は成功する。
• それっぽい値がほしいカラムにだけ、faker や now()->subDays(rand(7, 30)) 等で値を定義すれば十分。
• ダミーを生成する faker の用法については、「faker チートシート」等で検索 する。

データシーダー

• シーダーは、関連（リレーション）する複数のテーブル（モデル）をまとめて、１つのシーダーとする。
  例えば、5個の会社とそれに紐づくユーザーを5～20作成するシーダーは次のように書く。
  create に与えた引数は、ファクトリーで定義したランダム値より優先する。

Company::factory()->count(5)->create()->each(function ($company) {
   User::factory()->count(rand(5, 20))->create([
      'company_id' => $company->id
   ]);
});

• または次のように書くこともできる。

$rows = [
    ['company_code' => 'test1'],
    ['company_code' => 'test2'],
    ['company_code' => 'test3'],
    ['company_code' => 'test4'],
    ['company_code' => 'test5'],
];
foreach ($rows as $row) {
   $company = Company::factory()->create($row);
   $max = rand(5, 20);
   foreach ($i = 0; $i < $max; $i++);
       User::factory()->create([
          'company_id' => $company->id
       ]);
    });
  }
}

• 上記いずれの場合も、User モデルに ユニークカラム（例えば email） があった場合は注意が必要。ファクトリーの unique 指定は一度の ceate の中だけの制限なので、ループさせるとたちまち重複エラーとなる。ファクトリーに任せず、シーダー側でユニーク性を担保する工夫が必要となる。

Company::factory()->count(5)->create()->each(function ($company) {
   User::factory()->count(rand(5, 20))->create([
      'company_id' => $company->id,
      'email' => uniqid() . '@example.com',
   ]);
});

[参照] マイグレーションで積み上げたテーブル構造を可視化するのは次の記事を参照。

• MySQL テーブル構造の md を作成